Help Writing Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help Writing Stored Procedure

Author

Topic

swool
Starting Member

3 Posts

Posted - 2014-09-30 : 11:32:18

I have SQL 2008 and am using SQL management studio. I have written several queries but never a stored procedure. I have a particular query I need to run once a week and have the results saved in a text file. I can do this manually but need it to be automated. Here is the query:
SELECT Inv_Num,Inv_Date,Cust_Name,CustPONum,Inv_Total,Balance,JobNumber,Salesman1,InsertedBy,LastChangedBy
,Datediff(day, Inv_date, getdate()) AS Days_Past_InvDate
FROM [RFMS Archive1].[dbo].[Header]
Where Balance > 0 and Inv_Num like 'cg%'

Please help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 11:47:20

To automate it, you need to set up a Sql Server Agent job. The job can use the query directly or you can wrap it in a stored procedure if you like. from SSMS, find the Sql Server Agent tab and open it. right-click on the Jobs folder and select New Job. Fill out the tabs and create a step that runs your query. Set up a schedule as desired. To output the result to a text file, consider using SQLCMD instead. Here's an article on that:

http://blog.sqlauthority.com/2013/09/12/sql-server-automatically-store-results-of-query-to-file-with-sqlcmd/

The other way, of course, is with SSIS. Write a package that uses your query and directs the output to a text file. Schedule the package using Agent.