SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Exporting Data Programatically with bcp and xp_cmdshell
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/01/2001 :  22:23:49  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 01/03/2006 :  13:24:13  Show Profile  Reply with Quote
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?
Go to Top of Page

sqlqs
Starting Member

2 Posts

Posted - 01/03/2006 :  13:36:07  Show Profile  Reply with Quote
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


:)
Go to Top of Page

tonyknibb
Starting Member

3 Posts

Posted - 05/01/2007 :  06:20:32  Show Profile  Reply with Quote
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")
Go to Top of Page

tonyknibb
Starting Member

3 Posts

Posted - 05/01/2007 :  06:24:53  Show Profile  Reply with Quote
I am a fool.

I just found this:

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

reyesj
Starting Member

1 Posts

Posted - 09/06/2007 :  19:28:11  Show Profile  Send reyesj a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 09/06/2007 :  19:40:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 09/06/2007 19:41:09
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/07/2007 :  08:56:49  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 09/07/2007 08:58:08
Go to Top of Page

matofarides
Starting Member

2 Posts

Posted - 03/03/2008 :  07:17:47  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 03/03/2008 :  13:23:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

matofarides
Starting Member

2 Posts

Posted - 03/04/2008 :  04:30:12  Show Profile  Reply with Quote
Yes, It was saved on the server. Thanks!
Go to Top of Page

spy008
Starting Member

1 Posts

Posted - 06/24/2008 :  17:54:17  Show Profile  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/24/2008 :  18:08:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/24/2008 :  18:33:06  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/24/2008 :  19:13:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 06/25/2008 :  07:55:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 06/25/2008 :  09:28:20  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/25/2008 :  12:32:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 06/25/2008 :  23:15:28  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 06/26/2008 :  00:30:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 06/26/2008 :  02:38:01  Show Profile  Visit jezemine's Homepage  Reply with Quote
true. I see what you mean now. :)


elsasoft.org
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000