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.
| Author |
Topic |
|
kidaduo
Starting Member
45 Posts |
 Posted - 2008-01-11 : 14:39:23
|
| Hellow-- Is it possible to use stored procedure to export data?-- I know u can use pcb utility and DTS pkg (sql 2000)/SSIS, but I want to see if there is some other options. If it can be done using stored procedure--please, let me knowThkJosephine |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-11 : 14:45:55
|
| You can call the BCP tool from stored procedure.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
kidaduo
Starting Member
45 Posts |
Posted - 2008-01-11 : 14:50:44
|
Thank u Dinakar,Attached is my BPC-- ------DECLARE @FileName varchar(50), @bcpCommand varchar(2000)SET @FileName = REPLACE('c:\mytable_' + CONVERT(char(8), GETDATE(), 1) + '.txt', '/', '-')SET @bcpCommand = 'bcp "SELECT * FROM mydatabase.dbo.mytable" out "'SET @bcpCommand = @bcpCommand + @FileName + '" -U userid -P 123 -c' EXEC master..xp_cmdshell @bcpCommand-------------------------------------how do u call? sorry, I'm new in this game!Thkquote:
Originally posted by dinakar
You can call the BCP tool from stored procedure.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Josephine |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-11 : 14:56:16
|
| Looks okay..have you tried running it?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
kidaduo
Starting Member
45 Posts |
Posted - 2008-01-11 : 14:58:54
|
When I run--I'm getting the following errors/msg---------------usage: bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n] [-c] [-t field_terminator] [-r row_terminator] [-U username] [-P password] [-I interfaces_file] [-S server] [-a display_charset] [-q datafile_charset] [-z language] [-v] [-A packet size] [-J client character set] [-T text or image size] [-E] [-g id_start_value] [-N] [-X] [-M LabelName LabelValue] [-labeled] [-K keytab_file] [-R remote_server_principal] [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]NULLThkquote:
Originally posted by dinakar
Looks okay..have you tried running it?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
Josephine |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-01-11 : 16:37:24
|
| Try putting a -S"ServerName" at the end and see if it helps..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
ranjeetsingah
Starting Member
3 Posts |
Posted - 2008-01-13 : 09:58:10
|
| Hi kidaduo,Try this one for data backup from sql server 2000 and more versions.declare @backup_location varchar(1000)BACKUP DATABASE [database_name] TO DISK = @backup_locationWITH NOFORMAT, NOINIT, NAME = N'database_name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOHave a Greate DayRanjeet SinghSoftware EngineerMobile- +91-9910893772 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-13 : 13:40:29
|
| Exporting data is different from db backup. |
|
|
|
|
|
|
|
|
Export data.