write query results automatically to file

[DanHD]

Hello

I’m looking at the following issue
We want to run at night a query in the mssql server (for example as a job), and let the results written to a csv file on the server.
What is the best way to do this?


Dan

 

[SQLBill]

When you create the job and fill out the job step info, go to Advanced. In that area, enter a path and file name for 'Output file'.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[DanHD]

hi Bill

this works fine, but one problem: There will be also print some unwantend text and lines into the file, like

Job 'test2' : Step 1, '1' : Began Executing 2012-01-12 22:11:13
----------------
----------------

We need the file as import in another programm.
How can I skip those text?

Dan

 

[SQLBill]

I'm not sure you can. You can cut out the (## rows affected) by using SET NOCOUNT ON. But for the rest, I'm not sure of a way in the job to change that.

I've never done this, buy you might be able to save the results to a table and then have Excel query the table for the results.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[DanHD]

hi Bill

thanks for your input.
I found a solution for me on the net by using the Xp_cmdshell in combination with BCP.

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

Dan