Article: Exporting Data Programatically with bcp and xp_cmdshell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Exporting Data Programatically with bcp and xp_cmdshell

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-01 : 22:23:49

This article demonstrates how to programmatically control bcp to generate text files. It uses dynamic SQL and xp_cmdshell to execute a call to bcp from within Query Analyzer.

Article Link.

sqlqs
Starting Member

2 Posts

Posted - 2006-01-03 : 13:24:13

Is there anyway that i can specify a delimiter to the exported file? I've tried using the /t switch but am unable to use the '|'(pipe) delimiter with it. Any ideas of how to export using a '|' as a field delimiter?

sqlqs
Starting Member

2 Posts

Posted - 2006-01-03 : 13:36:07

Hey found the answer searching around, thanks anyways.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34309

It was as easy as putting the delimiter in quotes:

SET @bcptext = 'bcp ' + @bcptext + ' queryout " ' + @outfile + '" -c -T /t "|"' 
EXEC @retrncd=master.dbo.xp_cmdshell @bcptext

tonyknibb
Starting Member

3 Posts

Posted - 2007-05-01 : 06:20:32

This stuff is fantastic!

I'm new to all of this though. And new to this forum... can anyone tell me how to convert this:


-- backup the Assessment questions data

DECLARE @FileName1 varchar(50),@bcpCommand1 varchar(2000)
SET @FileName1 = REPLACE('c:\TK_Assess_Qs_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand1 = 'bcp "SELECT * FROM headlice..AssessQs_tbl" queryout "'
SET @bcpCommand1 = @bcpCommand1 + @FileName1 + '" -S TONY\SQLEXPRESS -c -T /t "|"'
EXEC master..xp_cmdshell @bcpCommand1

-- backup the Assessment answers data

DECLARE @FileName2 varchar(50),@bcpCommand2 varchar(2000)
SET @FileName2 = REPLACE('c:\TK_Assess_As_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand2 = 'bcp "SELECT * FROM headlice..AssessAs_tbl" queryout "'
SET @bcpCommand2 = @bcpCommand2 + @FileName2 + '" -S TONY\SQLEXPRESS -c -T /t "|"'
EXEC master..xp_cmdshell @bcpCommand2

-- backup the Assessment users data

DECLARE @FileName3 varchar(50),@bcpCommand3 varchar(2000)
SET @FileName3 = REPLACE('c:\TK_Assess_Us_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand3 = 'bcp "SELECT * FROM headlice..AssessUsers_tbl" queryout "'
SET @bcpCommand3 = @bcpCommand3 + @FileName3 + '" -S TONY\SQLEXPRESS -c -T /t "|"'
EXEC master..xp_cmdshell @bcpCommand3

-- backup the Assessment 'users answers' data

DECLARE @FileName4 varchar(50),@bcpCommand4 varchar(2000)
SET @FileName4 = REPLACE('c:\TK_User_As_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand4 = 'bcp "SELECT * FROM headlice..UserAnswers_tbl" queryout "'
SET @bcpCommand4 = @bcpCommand4 + @FileName4 + '" -S TONY\SQLEXPRESS -c -T /t "|"'
EXEC master..xp_cmdshell @bcpCommand4
go

into something that creates INSERT statements for me and saves it all to the file? Like this:


INSERT INTO headlice.dbo.AssessAs_tbl VALUES ("1","Breathe through","1","1")

tonyknibb
Starting Member

3 Posts

Posted - 2007-05-01 : 06:24:53

I am a fool.

I just found this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76992&SearchTerms=bcp,export,insert

reyesj
Starting Member

1 Post

Posted - 2007-09-06 : 19:28:11

Hi Garth,

I am new to this. I am a mainframe person. I have similar requirements from a project I am presently working on to create text files during a nightly batch process to retrieve a MS SQL table from Windows 2000 server, formats, exports to text file and then ftp to MVS.

Can you please show me the remaining processes, e.g., the formatting, and the ftp batch file and others that I may need.

Thanks,
Jose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-06 : 19:40:53

Jose,

You've responded to an article that was posted 6 years ago. I don't think Garth is a frequent poster here if at all. You'll get better mileage with your questions if you were to start a new thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-07 : 08:56:49

Have a look at
http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/
and
http://www.nigelrivett.net/FTP/s_ftp_PutFile.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

matofarides
Starting Member

2 Posts

Posted - 2008-03-03 : 07:17:47

I tried to perform this operation in a stored procedure"

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)
SET @FileName = 'C:\TopUp_'+CONVERT(char(8),GETDATE(),112)+'.txt'

SET @bcpCommand = 'bcp "SELECT REPLICATE(''0'', 6 - Len(COUNT(serial_number))) + CAST(COUNT(serial_number) AS varchar(6)) + REPLICATE (''0'' , 5 - Len(CAST(SUM(amount*100) AS int))) + CAST(CAST(SUM(amount*100) AS int) AS varchar (13)) + REPLICATE ('' '' , 81) FROM CYTAPaymentGateway..Transactions WHERE GLDATE = ''2008/02/01''" queryout '
SET @bcpCommand = @bcpCommand + @FileName + ' -SWEBDEV -T -C ACP -c'
print @FileName
print @bcpCommand

EXEC master..xp_cmdshell @bcpCommand

But althoigh i get a message "1 rows copied", file d:\topup_20080303.txt is not saved. Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-03 : 13:23:59

d:\topup_20080303.txt would be located on the database server and not on your client machine. Did you check there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

matofarides
Starting Member

2 Posts

Posted - 2008-03-04 : 04:30:12

Yes, It was saved on the server. Thanks!

spy008
Starting Member

1 Post

Posted - 2008-06-24 : 17:54:17

I'd like to use this in a stored procedure:

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\authors_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM pubs..authors ORDER BY au_lname" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'

EXEC master..xp_cmdshell @bcpCommand

However, is there any way I can execute the command stored in @bcpCommand without using xp_cmdshell? In my current project we are not allowed to make use of xp_cmdshell as it is deemed to be non-secure. Any help would be greatly appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-24 : 18:08:37

The only way to call executables (bcp.exe for example) from a stored procedure is to use xp_cmdshell. It's the only way we can get to a cmd session from within SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-24 : 18:33:06

well if you're using sql server 2005 you can use CLR integration to run exe's.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-24 : 19:13:51

You might as well use xp_cmdshell though as using a CLR would have the same security concerns, right?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-25 : 07:55:04

you got me there... but probably yes.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-25 : 09:28:20

quote:
Originally posted by tkizer

You might as well use xp_cmdshell though as using a CLR would have the same security concerns, right?

I think xp_cmdshell is a bigger hole - unless you write a CLR proc called ExecuteAbitraryCommandLine() which is what xp_cmdshell does.

If your CLR proc is written to only launch a specific exe, it's a smaller hole.

elsasoft.org

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 12:32:55

But to allow the CLR to run bcp.exe, you have to set the unsecure option on the database, right?

I agree that the CLR could be a smaller hole, but xp_cmdshell requires very elevated permissions already so it's not like just anyone can run it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-25 : 23:15:28

quote:
Originally posted by tkizer

But to allow the CLR to run bcp.exe, you have to set the unsecure option on the database, right?

the assembly would have to be declared as UNSAFE. as I recall, this means you have to set the TRUSTWORTHY bit to TRUE on the database you import the assembly to.

my only point is that with xp_cmdshell you can do anything, but with proc that specially designed only to run bcp you can't do anything - you can only run bcp, and then only in the specific way the CLR proc allows (for example you could code it to only do exports, no imports, and only to a specific location, say).

elsasoft.org

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-26 : 00:30:55

Agreed but what I'm saying is that by setting the TRUSTWORTHY option to true, you've opened it up to other objects.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-26 : 02:38:01

true. I see what you mean now. :)

elsasoft.org

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources