Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Output a sql resultset to csv automatically with sql job Expand / Collapse
Author
Message
Posted Wednesday, October 1, 2008 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 8:32 AM
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
Post #579122
Posted Wednesday, October 1, 2008 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:45 AM
Points: 105, Visits: 633
You could try using xp_cmdshell.
Post #579137
Posted Wednesday, October 1, 2008 12:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 8:32 AM
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
Post #579300
Posted Thursday, October 2, 2008 1:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:30 AM
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?
Post #579502
Posted Thursday, October 2, 2008 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 8:32 AM
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
Post #579722
Posted Thursday, October 2, 2008 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2011 8:32 AM
Points: 170, Visits: 297
Using sqlcmd, do I need to pass username and password for the SQL server?
Post #579930
Posted Friday, October 3, 2008 1:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:30 AM
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.
Post #580091
Posted Monday, April 6, 2009 12:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:18 AM
Points: 23, Visits: 106
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
Post #691352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse