ms sql server - bcp question. I've got this code Select @sql = 'bcp "Select * FROM [dbo].[TestingBCP] queryout' exec master..xp_cmdshell @sql but I get this error Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1 [Batch Start Line 0] Procedure expects parameter 'command_string' of type 'varchar'.

DECLARE @alenzi varchar(8000); -- Not varchar(max) SELECT @alenzi = 'bcp "SELECT * FROM [DATABASE_NAME].[dbo].[TestingBCP]" queryout "C:\TestingBCP.txt" -t, -c -T'; EXEC master..xp_cmdshell @alenzi ;

Using BCP with queryout - Microsoft Q&A

Accepted answer

Guoxiong 8,206 Reputation points

2020-11-10T15:57:40.987+00:00

DECLARE @alenzi varchar(8000); -- Not varchar(max)
SELECT @alenzi = 'bcp "SELECT * FROM [DATABASE_NAME].[dbo].[TestingBCP]" queryout "C:\TestingBCP.txt" -t, -c -T';
EXEC master..xp_cmdshell @alenzi ;
Please sign in to rate this answer.
Johnathan Simpson 586 Reputation points

2020-11-10T16:03:11.64+00:00

@Guoxiong

Okay, I see now i needed to add more code. I am using variables to determine the save location like this
Declare @saveloc nvarchar(4000),@apl nvarchar(100);
Set @apl = (Select datename(month, dateadd(month,-1,getdate())));
Set @saveloc = 'C:\Test\'+@employee+'\TestingData\'+@apl +'.csv';

How would my @alenzi statement look since adding in the @saveloc with variables?

Guoxiong 8,206 Reputation points

2020-11-10T16:08:25.063+00:00

Use varchar instead of nvarchar.

Johnathan Simpson 586 Reputation points

2020-11-10T16:10:45.41+00:00

@Guoxiong - changed variable types to varchar. How would my bcp statement look? Is this correct?

Select @sql = 'bcp "Select * from xxxx" queryout @variable -t, -c, -T';

Guoxiong 8,206 Reputation points

2020-11-10T16:14:23.867+00:00

Select @alenzi = 'bcp "Select * from xxxx" queryout ' + @Kritivasas + ' -t, -c, -T';

Johnathan Simpson 586 Reputation points

2020-11-10T16:22:49.953+00:00

@Guoxiong - I get an error of
bcp: unknown option ,

Guoxiong 8,206 Reputation points

2020-11-10T16:28:50.083+00:00

Can you print out @alenzi and post it here?

Johnathan Simpson 586 Reputation points

2020-11-10T16:30:01.353+00:00

@Guoxiong
bcp "Select * FROM [dbo].[XXXXXXXXXXXXX]" queryout C:\Reports\Customer1\MonthlyTrackingReport\October.csv -T

Completion time: 2020-11-10T11:29:05.8719819-05:00

Guoxiong 8,206 Reputation points

2020-11-10T16:39:21.753+00:00

bcp "Select * FROM [DATABASE_NAME].[dbo].[XXXXXXXXXXXXX]" queryout C:\Reports\Customer1\MonthlyTrackingReport\October.csv -t, -c -T
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Using BCP with queryout

0 additional answers

Your answer