Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Error Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 1:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:16 AM
Points: 138, Visits: 127
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
Post #1493011
Posted Tuesday, September 10, 2013 1:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:24 PM
Points: 805, Visits: 722
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 &lt;identifier&gt; . &lt;identifier&gt; . &lt;identifier&gt; &lt;identifer&gt; 


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
Post #1493028
Posted Friday, September 13, 2013 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:46 AM
Points: 6, 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
Post #1494494
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse