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
– vmachan, Commented Jan 2, 2016 at 20:37
bcp "SELECT * FROM #ParticipantIdentifiers" queryout "<my personal path>\NonEmployee_EEID.txt" -w -T -t | — Jeff.Clark
– Jeff.Clark, Commented Jan 2, 2016 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
– vmachan, Commented Jan 2, 2016 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
– Jeff.Clark, Commented Jan 2, 2016 at 21:01

vmachan · Accepted Answer · 2016-01-02 22:31:34Z

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 2, 2016 at 22:31

vmachan

1,6821 gold badge10 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related