Saving query results to CSV file with query in job

  • 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.

  • 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/

  • 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.

  • 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.

  • It's not something as simple as...

    SELECT *

    INTO dbo.new_table_name

    FROM dbo.existing_table

    ... is it?

  • 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/

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

    🙂

  • 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