SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Error


BCP Error

Author
Message
Srini kolar
Srini kolar
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 146
Hi

I'm trying to queryout a result from table to file using BCP in SQL server 2012 . My T-SQL is as below

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

Its throwing me the syntax error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT FileContent FROM
[StoreReferenceFiles] WHERE FileId = '1F4CA617-2296-482E-B909-24B69B83EDAA''.

Please help
Erland Sommarskog
Erland Sommarskog
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 875
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
alekya
alekya
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 178
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 :-) :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search