Question in BCP - SQL 2008

Question

Question in BCP - SQL 2008

balasach82

SSCrazy Eights

Points: 9653
More actions
May 28, 2013 at 9:20 am

#277994

IS there a way to use BCP out statement in a single query like (without using xp cmdshell)
BCP ... out ....file1.csv
BCP ... out ....file2.csv

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

balasach82 (5/28/2013)
IS there a way to use BCP out statement in a single query like (without using xp cmdshell)
BCP ... out ....file1.csv
BCP ... out ....file2.csv

bcp is a command line/console application,and the expectation is to call a cmd prompt and run your script from there;

sqlcmd is another possibility, but it's also a cmd line application.

so outside of SQL server, yes, absolutely, you can call bcp without xp_cmdshell;

xp_cmdshell is specifically so you can call console executables and system functions from within TSQL;

My best guess is the real question is "how do i export data to a csv WITHOUT xp_cmdshell", is that right?

I certainly know you can do it via a CLR procedure, if you are allowed to install CLR;s but as far as I know, there is no native way way for SQL server to write to disk.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

Without using .cmd or .bat files, which has clear passwords, i can give it in a job. Then how to use BCP to export more than a file.

Lowell SSC Guru Points: 323518 More actions · Answer 3

not sure what you mean;two different files would require two calls to bcp.

you can call If the startup account for SQL Server is a domain account, you could use a trusted connection to call bcp, which avoids passwords.

if you wanted two queries in the same file, you could either use a UNION to join two queries, or use cmd arguments to append two files together;

is that the question?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 4

To given an example, I need 2 csv files on running an select query from 2 tables. I know how to use BCP to export to csv. I now need to export from 2 tables to 2 csv files. Instead of using 2 steps in sql job,[1 bcp command in 1 step..], I want to give in single step. is that possible?