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


bcp syntax


bcp syntax

Author
Message
dallas13
dallas13
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1501 Visits: 1133
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 '+'.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 13268
What's the error message?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86125 Visits: 41096
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. :-D 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dallas13
dallas13
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1501 Visits: 1133
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
dallas13
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

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