October 1, 2008 at 8:51 am
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
October 1, 2008 at 9:01 am
You could try using xp_cmdshell.
October 1, 2008 at 12:17 pm
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
October 2, 2008 at 1:00 am
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?
October 2, 2008 at 9:10 am
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
October 2, 2008 at 1:57 pm
Using sqlcmd, do I need to pass username and password for the SQL server?
October 3, 2008 at 1:09 am
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.
April 6, 2009 at 12:39 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply