[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

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.
0
jbaisden
Asked:
jbaisden
  • 5
  • 4
  • 2
  • +1
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
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
 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now