Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output a sql resultset to csv automatically with sql job


Output a sql resultset to csv automatically with sql job

Author
Message
Jason DBA
Jason DBA
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 297
I have found on the forums this code to output a .sql query (I have modified for my needs):
sqlcmd -S ECOM-VS-01 -i "d:\sqlcode\backorder.sql" -o "\\temp-shed-40\public\backorder.csv" -s"," -h-1 -W

This command is called from a batch file. I want to have this automatically run daily.
I tried running from a SQL job and received the error "the system cannot find the file specified."
I tried running this from a Windows Scheduled task and nothing happened.

Any ideas would be appreciated.

ECOM-VS-01 is the SQL Server running SQL Server 2005.
D: is a local drive to the SQL server.
temp-shed-40 is a network server.

The user that the sql agent runs as has write permissions to the network location.

Jason Johnson
Akeel.Mughal
Akeel.Mughal
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 634
You could try using xp_cmdshell.
Jason DBA
Jason DBA
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 297
That is the code I'm using. In the SQL job's step, I have the following code:
exec xp_cmdshell "d:\sqlcode\runsql.bat"

The .bat file code is the following:
sqlcmd -S ECOM-VS-01 -i "d:\sqlcode\backorder.sql" -o "\\temp-shed-40\public\backorder.csv" -s"," -h-1 -W

This still gave me the same error.
Executed as user: PROFUNI\ECOMSA01. The process could not be created for step 1 of job 0x9278D1451C7AE845801DDA940E5F4EA0 (reason: The system cannot find the file specified). The step failed.

PROFUNI/ECOMSA01 has write access to the temp-shed-40 location.

Any ideas would be appreciated?

Jason
Christo Wolmarans
Christo Wolmarans
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 349
Hi Jason

Do both of the files exist at the locations that you are referencing them from? The file I am talking about are "d:\sqlcode\backorder.sql" and "d:\sqlcode\backorder.sql"

If they do exist have you tried putting the server name/ip address infront of the file being called in the bach file?
Jason DBA
Jason DBA
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 297
Ok...
If I run the batch when I'm logged into the server, it works fine. I'm assuming it is using my credentials to access the network location.

As you can see from my code example previously, I do reference the network location by server name ("\\temp-shed-40"). The D drive is a local drive for the SQL server.

I tried xp_cmdshell, with the previously provided error.

Any ideas on how to run this command:
sqlcmd -S ECOM-VS-01 -i "d:\sqlcode\backorder.sql" -o "\\temp-shed-40\public\backorder.csv" -s"," -h-1 -W

as a SQL agent job?

I tried putting this into a step for a job and received the same error.

Thank you for your help.

Jason
Jason DBA
Jason DBA
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 297
Using sqlcmd, do I need to pass username and password for the SQL server?
Christo Wolmarans
Christo Wolmarans
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 349
Hi

Is there a specific reason why you want to use a batch file to export the results produced to a csv? Why don't you use SSIS to generate the results for a csv?

If you use SSIS to do this you create a package to export the information and then you schedule the package in a job to run when ever you want it to.
Hyabusact
Hyabusact
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 121
Yes, you need to supply your login information when using SQLCMD. Here is a sample from something I have done recently.
sqlcmd -S\Dbserver -U sa -P password1 -s, -W -i c:\apinvoice.sql

This would run the contents of the apinvoice.sql file.
Here are the contents of that file:

use CheckProcess
SET NOCOUNT ON
GO
select * from ap_invoice
:out E:\Imaging\Admin\SQLFiles\Invoices.csv
GO

This created a file with the contents
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search