Search code examples
sql-servert-sqlssmsbcpxp-cmdshell

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

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
Solution

  • 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.