Sending Query Results via SQL Job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Sending Query Results via SQL Job

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-01 : 07:52:09

I know it is possible to run SQL via a SQL Job.

What I do not know.... Is it possible to export the result set in Pipe Delimited format to a specified FILE location on another server?
If so, how?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:57:34

its possible with bcp. see below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53428

you may need to use format file to specify pipe delimiter.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 08:57:47

Yes.
You can use BCP.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 08:58:04

more details on format file here

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-01 : 09:52:17

Great, thanks to all. I just got BCP to work successfully.
Just need to incorporate it into my JOB.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 10:04:53

see this for instructions to create job

http://msdn.microsoft.com/en-us/library/ms135739(SQL.90).aspx

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-01 : 12:10:29

I have a problem configuring my BCP command to write to a .CSV file.
I am able to write to a .CSV file using the following code as a cmdexec via my SQL Job:

bcp "exec usp_create_company_list_for_export" queryout c:\Monthly_Export_4_Brian_M\monthly_export.csv -U abc -P def -c

My problem is that any Null value being exported is represented by a weid square in Excel. Is this because I am not declaring a separator in the BCP command? Ideally, I would like to use a Pipe Character "|" as my separator.

Hope this makes sense, any help would be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 12:32:13

bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export.csv" -Uabc -Pdef -c -t"0x7C"

You can alter your procedure to include COALESCE function to replace NULL values with a default value.

E 12°55'05.63"
N 56°04'39.26"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-01 : 13:28:45

Peso, your the man!
It worked like a charm.

Thanks

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-01 : 14:35:55

One more question....

Is it possible using the below BCP syntax to create a .CSV file without overwriting the existing one? Basically, keeping a history?

bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export.csv" -U abc -P def -c -t "0x7C"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 02:38:33

You can dynamically add a timestamp to the filename so that you get a unique filenname for each export.

The use of binary value for pipe character (ASCII 124) is because of a bug in BCP and BULK INSERT when using a single character delimiter.

E 12°55'05.63"
N 56°04'39.26"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-02 : 07:12:17

Hi Peso,

I have tried applying the time stamp to the filename as shown below:

bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export_" + CONVERT(char(8),GETDATE(),1) + ".csv" -U aaa -P bbb -c -t "0x7C"

What I have fails when the job is run, do you see any syntax errors?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 07:44:53

Yes. You can't have "/" in a filename.

declare @cmd NVARCHAR(4000)

set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export_' + CONVERT(char(8), GETDATE(), 112) + '.csv" -Uaaa -Pbbb -c -t"0x7C"'

exec master..xp_cmdshell @cmd

E 12°55'05.63"
N 56°04'39.26"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-02 : 08:45:01

Peso, sorry if I am being a pain.....

I replaced my code with yours as shown below.

declare @cmd NVARCHAR(4000)

set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export_' + CONVERT(char(8), GETDATE(), 112) + '.csv" - Uaaa - Pbbb -c -t"0x7C"'

exec master..xp_cmdshell @cmd

The job fails when I attempt to run. I notice that the xp_cmdshell stored procedure is in the extended stored procedure folder prefixed with a sys. Do I need to changed master..xp_cmdshell to reference the sys or will it find it as is?
When viewing the SQL Log, I see the following:

2008-12-02 08:29:43 - ! [136] Job Monthly_Export_4_Brian_M reported: The process could not be created for step 1 of job 0x0B4E2DE009479744897A76152EC1E03C (reason: The system cannot find the file specified).

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-02 : 13:21:57

I still am having problems trying to get the following BCP to run.

declare @cmd NVARCHAR(4000)
declare @dte NVARCHAR(200)
set @dte = CONVERT(char(8), GETDATE(), 112)
set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "c:\Monthly_Export_4_Brian_M\monthly_export_' + @dte + '.csv" -U abc -P def -c -t"0x7C"'
exec master..xp_cmdshell @cmd

SQL Error is: 2008-12-02 08:29:43 - ! [136] Job Monthly_Export_4_Brian_M reported: The process could not be created for step 1 of job 0x0B4E2DE009479744897A76152EC1E03C (reason: The system cannot find the file specified).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 13:25:26

is the path given local path or server path? it must be server path. If the file happens to be in your local machine, then use UNC path instead.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-12-02 : 13:37:05

It is a server path being run manually from the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources