bcp syntax

Question

bcp syntax

dallas13

SSCertifiable

Points: 7491
More actions
June 3, 2014 at 3:07 pm

#294077

Not sure whats the syntax error here..
SET @Command = 'C:\"Program Files"\"Microsoft SQL Server"\90\Tools\binn\bcp.exe "SELECT DocData FROM table WHERE DocId = ' + convert(VARCHAR,@DocId) + '" queryout "C:\user\' + @DocId'+'_'+'@FileName'+ '.' +'@FileExtension + '" -Sservername -U user -P password -T'
Please someone help. Thanks
Here is my error
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '+'.

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

What's the error message?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

You have single quotes around variables names. The RED plus signs are the give-away. You also had a scad of double quotes in the wrong places and you don't actually need the full path to BCP... it should be part of the Environment Path Variable.

There are also other things wrong with the BCP command. For example, if you specify the "-T" option (Trusted Connection), there is no need to use a user name or password. SQL Server will take care of it.

You also have to specifiy the 3 part naming convention for your table names.

To make life easier in the future, you need to make and test a BCP command with no variables at all. Once you have it working, then you can easily convert it to dynamic SQL using what I call "Dynamic SQL for the DBA". The whole idea is simply to take a known good BCP command, tokenize it for variables and the like, "Slosh" it for readability, and you're done.

Of course, there's also such a thing as "DOS INJECTION" and you really need to build a function to test the variables with to make sure that things like "&" and "&&" (DOS Command "stringing") don't show up in your final concatenated or tokenized code.

Here's what I came up with...

SELECT @Command = REPLACE(REPLACE(REPLACE(REPLACE('BCP "SELECT DocData FROM dbnamehere.schemanamehere.table WHERE DocId = <<@DocID>>" queryout "C:\user\<<@DocId>>_<<@FileName>>.<<@FileExtension>>" -S<<@@SERVERNAME>> -T -c -CRAW'

,'<<@DocID>>' ,CONVERT(VARCHAR(10),@DocID))

,'<<@FileName>>' ,@FileName)

,'<<@FileExtension>>',@FileExtension)

,'<<@@SERVERNAME>>' ,@@SERVERNAME)

;

Notice that all the difficulty with getting single quotes right and all the plus signs, etc, has simply vanished thanks to the tokenization of the command.

And, no... "Slosh" is not a term I made up. Look it up in Books Online by looking up the "\ (backslash)" character without the quotes. 😀 Even though it's not documented in versions earlier than 2008, it still works at least as far back as 2005. And, the version of BOL 2008 that comes with SQL Server 2008 disks is the ONLY place in any of the BOLs that I've seen where you can actually search for the word "slosh" and it takes you to the "\ (backslash)" section.

Here's the 2014 versio from the internet.

http://msdn.microsoft.com/en-us/library/dd207007.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dallas13 SSCertifiable Points: 7491 More actions · Answer 3

Okay Thanks a lot For your help.

Here is the working solution I created

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

SET @FileName = 'c:\dod\authors_06-04-14.doc' --REPLACE('c:\dod\authors_'+CONVERT(char(8),GETDATE(),1)+'.doc','/','-')

PRINT @FileName

SET @bcpCommand = 'C:\"Program Files"\"Microsoft SQL Server"\90\Tools\binn\bcp.exe "SELECT DocData FROM db.schema.table WHERE DocId = 1" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -S Server -c -T'

EXEC master..xp_cmdshell @bcpCommand

Here is the output I can see, in SSMS Results pane,

NULL

Starting copy...

NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 79 Average : (12.66 rows per sec.)

NULL

But when I try to open the pdf file it gives me error.

At the same time when I run the same command from CMD line it works fine and I can open the pdf file as well.

Also file size is 134 KB from cmd line but its 234 KB from SQL Query.

Any suggestion why??

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Okay It worked when I used -n switch.

And I wrote a cursor so it works.

But I have all different extensions like jpg,doc,zip etc.

It only works for PDF files for now. But does not work for jpg and doc files.

Not sure how to format them correctly So I am working on finding that, let me know if u have any idea. Thanks a lot once again.