Schedule SQL job to run and export to CSV file

5 pts.
Tags:
CSV
SQL Jobs
SSMS 2008
Hi. I am trying to create a SQL job to run every hour and export results to a CSV file. I have created the job and scheduled it but can not figure out how to have it create the CSV file. Thanks!

Software/Hardware used:
SSMS 2008
ASKED: February 23, 2012  11:45 PM
UPDATED: May 8, 2013  2:45 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

We use BCP:

set @sql = 'select * from [server].dbname.dbo.tablename'
set @bcp = 'bcp "' + @sql + '" queryout server\path\FileName.csv -T -t, -c'
exec master..xp_cmdshell @bcp

-t, references comma separated values
Depending on the version of the BCP utility you may or may not be able to use temp tables.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Colin Smith
    I would use powershell to run your query. $results = Invoke-sqlcmd -serverinstance "instancename" -database "dbname" -query "query" $results | export-csv filename -force That should get you want you want. Just be sure that you have SQL Server 2008 client tools installed and that you are adding the ps snappin in order to use the invoke-sqlcmd cmdlet.
    885 pointsBadges:
    report
  • JSH

    Use the sp_CreateDelimitedTextFile command.  Was very easy:

    EXEC sp_CreateDelimitedTextFile 'name of view I created', 'Full path to .csv file'

    And that was it.

     

    40 pointsBadges:
    report
  • JSH
    Actually, let me elaborate that I put the command inside a procedure that is called when I want to build the file.
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following