Setting up a report within SQL

Question

Setting up a report within SQL

jennigirl

Old Hand

Points: 372
More actions
May 23, 2013 at 10:34 am

#278108

I have a SQL statement ready go but I am looking for a way to get SSMS to run the report weekly at midnight and provide an output of that report into a specific file. Basically, instead of me running reports and distributing them, I want to create the report and have it run automatically on a set schedule and users who need it can access the output file. I am not sure what this process is called so I am not sure what to search for at this point. Any help would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

jennigirl (5/23/2013)
I have a SQL statement ready go but I am looking for a way to get SSMS to run the report weekly at midnight and provide an output of that report into a specific file. Basically, instead of me running reports and distributing them, I want to create the report and have it run automatically on a set schedule and users who need it can access the output file. I am not sure what this process is called so I am not sure what to search for at this point. Any help would be greatly appreciated.

There are several ways to do this depending on the version of SQL Server and the features that you have available.

SQL Server Reporting Services would be perfect for this.

SQL Server Integration Services can also be used.

but if you don't have access to these two components then a simple SQL Agent job that runs your TSQL code and output the results as a .CSV file would suffice. Sorry I can't provide a step-by-step guide but if you google it you will find hundreds of examples on how to do this kind of thing. Good luck!

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

jennigirl (5/23/2013)
I have a SQL statement ready go but I am looking for a way to get SSMS to run the report weekly at midnight and provide an output of that report into a specific file. Basically, instead of me running reports and distributing them, I want to create the report and have it run automatically on a set schedule and users who need it can access the output file. I am not sure what this process is called so I am not sure what to search for at this point. Any help would be greatly appreciated.

Do you have an example of what you'd like the report to look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jennigirl Old Hand Points: 372 More actions · Answer 3

I have the code and I don't need anything special other than an export of the data. I was actually blocked from being able to see the Jobs option within SQL but we got that resolved. Thank you for the help!