Using BCP and xp_cmdshell with T-SQL inside SSMS. "The syntax of the command is incorrect"

Question

So, I am playing around with BCP to get to know it better, and ran into an issue I am having trouble figuring out. I am getting an error, "The Syntax of the command is incorrect".

I have tested the complete file path which is building correctly, and the select query by itself, which is completing correctly. Any thoughts?

The code I am using is:

DECLARE @fileName varchar(128)
DECLARE @filePath varchar(128)
DECLARE @completeFilePath varchar(128)
DECLARE @sqlCmd varchar(4000)
DECLARE @BCPSwitches VARCHAR(64)
SET @filePath = 'xxxxxx'

SELECT TOP 5 [EMP]
  ,[SSNO]
  ,[NAME]
  ,[STREET]
INTO #ParticipantIdentifiers
FROM xxxxxxxxx

SET @BCPSwitches = '-w -T -t |'
SET @fileName = 'xxxxx.txt'
SET @completeFilePath = @filePath + @fileName
SET @sqlCmd = 'bcp "SELECT * FROM #ParticipantIdentifiers" queryout "'+ @completeFilePath + '" ' + @BCPSwitches 

EXEC DemoDB..xp_cmdshell @sqlCmd

If you are running this script from SQLCMD or from SSMS you can just put a "SELECT @sqlCmd" before the EXEC and it should show up in the results window or console. — vmachan, Jan 02 '16 at 20:37
bcp "SELECT * FROM #ParticipantIdentifiers" queryout "\NonEmployee_EEID.txt" -w -T -t | — Jeff.Clark, Jan 02 '16 at 20:37
I think the terminator "|" after -t needs to be enclosed in quotes as well since the shell might be interpreting this as a output pipe.. — vmachan, Jan 02 '16 at 20:41
You are a genius my friend :) Thanks! If you want to post that as the answer, I'll mark it. — Jeff.Clark, Jan 02 '16 at 21:01

score 1 · Accepted Answer · answered Jan 02 '16 at 22:31

1

I think the terminator "|" after -t (in the @BCPSwitches) needs to be enclosed in quotes as well since the shell might be interpreting this as a output pipe.

answered Jan 02 '16 at 22:31

vmachan

1,672
1
10
10

Using BCP and xp_cmdshell with T-SQL inside SSMS. "The syntax of the command is incorrect"

1 Answers1