BCP utility in procedure gives strange erros

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

a.borgeld Ten Centuries Points: 1395 More actions · Answer 1

It looks like the space in the DB causes the problem, this works only now i have to play with the right user name:

DECLARE @cmd VARCHAR(2048);

SET @cmd = 'bcp [myTestNativeData] out "C:\myTestUniNativeData-N.csv" -N -T -t","';

EXEC master..xp_cmdshell @cmd

a.borgeld Ten Centuries Points: 1395 More actions · Answer 2

this is the result:

DECLARE @cmd VARCHAR(2048);

SET @cmd = 'bcp [myTestNativeData] out "C:\myTestUniNativeData-N.csv" -U<admabo> -N -T -t","';

EXEC xp_cmdshell @cmd

But now i get the following error:

The system cannot find the file specified.

I want the system to generate the file.

a.borgeld Ten Centuries Points: 1395 More actions · Answer 3

Solution found:

DECLARE @cmd VARCHAR(2048);

SET @cmd = --'bcp [myTestNativeData] out "/myTestUniNativeData-N.csv" -U<admabo> -N -T -t","';

'bcp "select * from [Staedion Test].[dbo].[Staedion$Werknemer Afdeling]" queryout C:\TEST.txt -S S-EMPACC -U admabo -N -T -t","';

EXEC xp_cmdshell @cmd

Martin Wills Ten Centuries Points: 1288 More actions · Answer 4

I would have thought that the following would work

DECLARE @cmd VARCHAR(2048);

SET @cmd = 'bcp "[Staedion Test].[dbo].[myTestNativeData]" out "C:\myTestUniNativeData-N.csv" -N -T -t","';

EXEC master..xp_cmdshell @cmd

In other words, since the dbtable parameter contains a space, you must enclose it in quotes.

This is a requirement of the standard Windows command processor which knows nothing about bcp or SQL naming conventions.

Martin