BCP Error

Question

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

When you have a syntax error, you need to read the query in tokens. You have:

exec master.sys.xp_cmdshell BCP "SELECT FileContent FROM

[StoreReferenceFiles] WHERE FileId = '1F4CA617-2296-482E-B909-24B69B83EDAA'" queryout "\\spicity605\Pricelist\docs_test.xlsx" -T-n

That is

EXEC <identifier> . <identifier> . <identifier> <identifer>

<idenifier> <identifier> <identifier> - <identifier> -

<identifier>

It is correct this far:

EXEC <identifier> . <identifier> . <identifier> <identifer>

SQL Server permits you to pass string literals that conforms to the rules for identifiers as unquoted (to permit things like "sp_help mytable"), why

exec master.sys.xp_cmdshell BCP

is equivalent to

exec master.sys.xp_cmdshell 'BCP'

But T-SQL then thinks that the procedure argument must be followed by one of:

1) OUTPUT, if the parameter in an output parameter

2) A comma to delimit the next parameter.

3) a WITH clause, for instance WITH RECOPILE

4) ; to terminate the statement.

5) A keyword that is a statement-leader for the next statement, for instance INSERT

You have neither: you have an identifier. Recall that in SQL "" delimits identifiers to permit for table names like Order Details.

Apparently you intended to pass a BCP commad to xp_cmdshell, but then you need to make it a single token, a single string literal.

By the way, I don't know what is that column, but I don't think you will not get a legal Excel document that way.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

alekya SSC Journeyman Points: 82 More actions · Answer 2

HI

try this

declare @sql varchar(8000)

SET @dir = 'D:\';

SET @sql = 'bcp "select * from Test.dbo.TableName" queryout "c:\bcptest3.txt " -c -t, -T -S';

EXEC master..xp_cmdshell @sql;

Hope this helps 🙂 🙂