Problems using BCP to save CSV to another physical

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

SSIS and Import/Export (2005)

Problems using BCP to save CSV to another physical

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-14 : 09:42:16

I have a SQL job which writes a CSV file to a location on the Server running SQL Server. This works with no problems. I want to modify the job to write the CSV to another physical server. Once changed, I am no longer able to run the job.

First thought would be security, but I am able to read, write to the networked drive using proper login, etc.

Below is my syntax of my job.

NEW Syntax (Not Working):

bcp "exec usp_create_company_list_for_export" queryout "\\Fnpxsvr\abc\Dev Runs\test.csv" -U aaa -P bbb -c -t "0x7C"

OLD Syntax (Working):

bcp "exec usp_create_company_list_for_export" queryout "C:\abc\Dev Runs\test.csv" -U aaa -P bbb -c -t "0x7C"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 10:29:33

whats the error thrown?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-14 : 11:05:19

Unable to open BCP host data-file. Process Exit Code 1. The step failed.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-14 : 11:47:37

Fixed the issue, it was related to the SQL Agent user id not being set up for the share.

One more question.... I am trying to append a date to my file. Using the below, syntax creates a file with the convert, etc as part of the name. Do you see anything wrong with the syntax?

bcp "exec usp_create_company_list_for_export" queryout "\\Fnpxsvr\Novantasdata\Dev Runs\orbit_company_report_' + CONVERT(char(8), GETDATE(), 112) + '.csv" -U pfradet -P pfradet -c -t"0x7C"'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 12:04:36

i think you need to use dynamic sql for this
EXEC ('bcp "exec usp_create_company_list_for_export" queryout "\\Fnpxsvr\Novantasdata\Dev Runs\orbit_company_report_' + CONVERT(char(8), GETDATE(), 112) + '.csv" -U pfradet -P pfradet -c -t"0x7C"')

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-14 : 14:27:11

Step 1 of the job now writes out to a separate physical server without any problems. This was corrected by adding the SQL Server Agent username to the shared directory on the separate physical server.

Step 2 of the job fails. This step calls a stored procedure which utilizes dynamic SQL to write to the same separate physical server only this time, appending a date stamp to the file.

Do stored procedures use a different user name which need to be set up on my share?

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-15 : 08:42:12

I am not sure why this Stored Procedure is failing?
The file is being written to a separate server. Security/Sharing rights have been granted on the share for the SQL Agent login, etc.

DECLARE @cmd as varchar(300)
DECLARE @today as varchar(20)
set @today = CONVERT(char(8), GETDATE(), 112)
set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "\\Fnpxsvr\data\Dev_Runs\company_report_' + @today + '.csv" -U aaa -P bbb -c -t"0x7C"'
EXEC master..xp_cmdshell @cmd
-- print @cmd

ERROR SHOWN BELOW:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 08:50:55

quote:
Originally posted by qman

I am not sure why this Stored Procedure is failing?
The file is being written to a separate server. Security/Sharing rights have been granted on the share for the SQL Agent login, etc.

DECLARE @cmd as varchar(300)
DECLARE @today as varchar(20)
set @today = CONVERT(char(8), GETDATE(), 112)
set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "\\Fnpxsvr\data\Dev_Runs\company_report_' + @today + '.csv" -U aaa -P bbb -c -t"0x7C"'
EXEC master..xp_cmdshell @cmd
-- print @cmd

ERROR SHOWN BELOW:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

the error seems as if file path is unaccessible

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-15 : 09:40:20

Since this is being run and saved from a Stored Procedure, would my share need to grant access for this Stored Procedure? If so, I am not aware of what the login should be. I thought the SQL Agent user name would handle all aspects when applied to the share....

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-01-15 : 09:41:42

I am able to write to this share from a SQL job without a datestamp in the file. When I attempt to use the SP which writes to the same location but with a datestamp appended to the file, it fails. Very odd....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources