Export table into a file? 2

[ranchan02]

Hi Everyone:

I created a stored procedure that updates records in the master table (we'll call it master_table) with data from another table (we'll call it update_table). A feature I want to add to my stored procedure is to export the data from the update_table into a file (csv, xls, etc) and once the file has been created, delete the update_table from the database. How do I go about this? Here's the specs of the environment: SQL Server 2000 running on Windows Server 2003.

 

[SQLDenis]

here ia 1 example of copying data to a file by using bcp and xp_cmdshell

master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout c:\Authors.txt -c '

once you run this a file should be created on you c drive named Authors.txt

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Dashley]

SQlDenis,

That would make a great tip. I used to use the spool cmd with Oracle but about gave up on SqlServer. It works great

 

[ranchan02]

Hi SQLDenis:

Thanks for the tip! I have a question though...in your example it creates this text file into the c: directory, but what would happen if the file already exist in the directory? does it overwrite it? I would prefer that if the file already exist, another output file (leaving the original one intact). How would I go about that?

 

[SQLDenis]

declare @b int

exec master..xp_fileexist 'c:\Authors.txt' ,@b output
select @b


@b will be 1 if the file exists otherwise it will be 0

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ranchan02]

Hi SQLDenis:

I tried doing a quick test with the sample you posted and I keep getting an error:

output
-------------------------------------------------------------------------
Password:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PART_UPDATING_CNT'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

Here's the portion from my sp:

My SP
...
EXEC master..xp_cmdshell 'bcp "SELECT * FROM UPDATE_TABLE" queryout c:\Authors.txt -c '
...

Am I doing something wrong?

 

[SQLDenis]

you have to prefix with your dab and owner

in my example pubs..authors instead of pubs
you need something like DbName.dbo.UPDATE_TABLE or DbName..UPDATE_TABLE


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ranchan02]

Hi SQLDenis:

Okay i changed it up and it works now...but when I tried to change it and save it to a network path, it gives me this error message:

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Password:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

Here's the line:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM database.dbo.update_table" queryout \\machine_name\shared_folder\test.xls -c '

Am I missing something?

P.S. Thank you for your help.

 

[SQLDenis]

looks like your server can't see/use that path
You can always use DTS and FTP or use the FileSystemObject to do that (more complicated of course)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/