September 17, 2012 at 7:43 am
I am using SQL server 2008 R2 with no SSIS capabilities. I have set up a job in SQL Server Agent to run a query every morning at 9am. I have set the Query Results to save as Results to Text. When I run the job I've set up the results do not save to text (which I assumed they wouldn't) but I am wondering if there is a way of the job being able to save the results of the query when it executes?
I hope I've explained this clearly.
Any help greatly appreciated.
Thanks.
September 17, 2012 at 10:16 am
sqlrd22 (9/17/2012)
I am using SQL server 2008 R2 with no SSIS capabilities. I have set up a job in SQL Server Agent to run a query every morning at 9am. I have set the Query Results to save as Results to Text. When I run the job I've set up the results do not save to text (which I assumed they wouldn't) but I am wondering if there is a way of the job being able to save the results of the query when it executes?I hope I've explained this clearly.
Any help greatly appreciated.
Thanks.
Can you just save the results in an audit table? If you MUST save it to a file as you eluded to in the title of your post it is quite a bit uglier. This is one of the things that SSIS excels at.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 2:15 am
Sean Lange (9/17/2012)
sqlrd22 (9/17/2012)
I am using SQL server 2008 R2 with no SSIS capabilities. I have set up a job in SQL Server Agent to run a query every morning at 9am. I have set the Query Results to save as Results to Text. When I run the job I've set up the results do not save to text (which I assumed they wouldn't) but I am wondering if there is a way of the job being able to save the results of the query when it executes?I hope I've explained this clearly.
Any help greatly appreciated.
Thanks.
Can you just save the results in an audit table? If you MUST save it to a file as you eluded to in the title of your post it is quite a bit uglier. This is one of the things that SSIS excels at.
What kind of audit table? Could you explain that a bit further.. It doesn't necessarily have to be saved as a file, just stored somewhere to be accessed every day.
September 18, 2012 at 10:49 am
Hi,
Without knowing what your sql job is doing I think you can execute a batch file from the sql job that could execute a BCP (bulk copy program) command with the output saved to a file. I do not have the syntax handy but a quick search on BCP should return something pretty quick.
September 18, 2012 at 10:55 am
It's not something as simple as...
SELECT *
INTO dbo.new_table_name
FROM dbo.existing_table
... is it?
September 18, 2012 at 10:57 am
laurie-789651 (9/18/2012)
It's not something as simple as...SELECT *
INTO dbo.new_table_name
FROM dbo.existing_table
... is it?
That's pretty much what I am thinking.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 18, 2012 at 12:46 pm
If you just want to write job output to some kind of file, this is what you can do:
- Double click the job you created
- Click Steps and list of steps shows up
- Double Click on the step that you want output to be saved on the file
- Go to "Advanced" and give you full filename path. Remember the file will be created on the server where sql server instance reside.
Audit table would be another good idea if you will need to do some querying on that data.
September 18, 2012 at 12:57 pm
adding another option to the mix:
i threw a CLR export example out on codeplex;
if you have the ability to add CLR into the mix,then you could use one of the export methods found here:
http://sqlclrexport.codeplex.com/
EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'SELECT * FROM Products',
@FilePath = 'C:\Data',
@FileName = '\Products_export.csv',
@IncludeHeaders = 1
Lowell
September 19, 2012 at 2:03 am
Thanks for the suggestions everyone, I'll be trying out the methods you've mentioned today and I'll let you know how I get on.
🙂
September 19, 2012 at 3:25 am
mbhandari (9/18/2012)
If you just want to write job output to some kind of file, this is what you can do:- Double click the job you created
- Click Steps and list of steps shows up
- Double Click on the step that you want output to be saved on the file
- Go to "Advanced" and give you full filename path. Remember the file will be created on the server where sql server instance reside.
Audit table would be another good idea if you will need to do some querying on that data.
The first method you mentioned worked great for me, thank you mbhandari.
Thanks everyone else too for providing me with great info 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply