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 syntax Expand / Collapse
Author
Message
Posted Tuesday, June 3, 2014 3:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,120, Visits: 863
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 '+'.
Post #1577171
Posted Tuesday, June 3, 2014 3:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,553, Visits: 10,429
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1577181
Posted Tuesday, June 3, 2014 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 36,947, Visits: 31,452
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1577200
Posted Wednesday, June 4, 2014 8:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,120, Visits: 863
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??
Post #1577373
Posted Wednesday, June 4, 2014 10:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:57 PM
Points: 1,120, Visits: 863
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.
Post #1577428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse