Basic bcp usage


I've been looking through the forums and such on EE and just can't seem to piece together the right syntax for this bcp routine. I am simply trying to write the results of a stored procedure to a text file with each field being seperated by a comma.

This is what I have so far:

declare @bcp varchar(8000)

SET @bcp = 'exec UPS_EnrollmentFileTest queryout' + 'C:\text.text' + '" -U sa -P password -c -t,'  
EXEC master..xp_cmdshell @bcp

When I run this in query analyzer I get an error that returns as if it were a table with 3 rows. It reads:

row 1: 'exec' is not recognized as an internal or external command,
row 2: operable program or batch file.
row 3: null

If anyone could point me in the right direction I would really appreciate it.
jbaisdenAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to specify the database:
SET @bcp = 'bcp "exec database.dbo.UPS_EnrollmentFileTest" queryout "C:\text.text"  -U sa -P password -c -t,'  
EXEC master..xp_cmdshell @ncp
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi jbaisden,
> SET @bcp = 'exec UPS_EnrollmentFileTest queryout' + 'C:\text.text' +
> '" -U sa -P password -c -t,'  

SET @bcp = 'UPS_EnrollmentFileTest queryout' + 'C:\text.text' + '" -U sa -P password -c -t,'  

you don't need exec inside


Aneesh
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
SET @bcp = 'bcp "exec UPS_EnrollmentFileTest" queryout "C:\text.text"  -U sa -P password -c -t,'  
EXEC master..xp_cmdshell @bcp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jbaisdenAuthor Commented:
Duh! I also saw that I didn't include bcp in the @bcp variable. I modified it to:

declare @bcp varchar(8000)

SET @bcp = 'bcp UPS_EnrollmentFileTest queryout' + 'C:\text.text' + '" -U sa -P password -c -t,'  
EXEC master..xp_cmdshell @bcp

Now i'm getting different results, but no file.

row1: usage: bcp {dbtable | query} {in | out | queryout | format} datafile
row2-11: For some reason, these rows have all the options you can use in conjunction with bcp. It's like I printed a help ? command.
row 12: null

hmm....
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Angel is right ... .
0
 
jbaisdenAuthor Commented:
Oh angel. Didn't see your post till after I put mine up. I tried your approach. Now it has the following results:

row1: SQLState = 37000, NativeError = 2812
row2: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'UPS_EnrollmentFileTest'.
row3: NULL
0
 
jbaisdenAuthor Commented:
I know the procedure exists and works. I'm not sure how to tell BCP what server and database to use, which I think is the cause of this.
0
 
jbaisdenAuthor Commented:
YOU GUYS RULE!

With that addition, its working just fine, despite a slight performance hit, but I kind of figured that would happen. Just out of curiosity, is this considered a standard way to move things to a text file from an SQL query? What are the pros/cons of this approach? I'd be more than happy to open a new question for this if you would like. Just let me know when you can. Thanks again for all of your help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from sql server to a text file, yes, this is one of the ways to do it.

from text file to sql server, rather the instruction BULK INSERT is used
0
 
ptjcbCommented:
I use SQL DMO to push data from the database to a text file. You can also use DTS or SSIS (depending on the year version of your SQL Server).
0
 
jbaisdenAuthor Commented:
I've never heard of SQL DMO before. That's intriguing. I'll do some research on it to see if this might better suit my needs. Thanks again guys!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SQL DMO is the objects that SQL Server Enterprise Manager uses to show its screen.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.