Create a text file from w/in a Stored Procedure

[Tim McDaniel]

Is there a way to use BCP or something else within a stored procedure to extract data from a select statement out to a text file?

[Junjun Olympia]

There are actually different ways of accomplishing this task. BCP is one of the simplest and most direct approach. You run BCP from a stored procedure by passing the complete statement, the one you'd normally type in the command line, as a parameter to the extended procedure xp_cmdshell.
Good luck.

[Nishi Narula]

Can you elaborate a bit more and specify what other methods can be used? I’m new to this field and this info will be very useful…


------------
Junjun Olympia at 9/8/99 1:54:58 AM

There are actually different ways of accomplishing this task. BCP is one of the simplest and most direct approach. You run BCP from a stored procedure by passing the complete statement, the one you'd normally type in the command line, as a parameter to the extended procedure xp_cmdshell.
Good luck.

So basically, you wanna create a stored procedure which will execute a
query and then store the results to a text file. As you probably know, bcp
in sql 7 can output the results of a query to a text file. So you have part
of your problem solved. The only issue now is how to call bcp, which is a
command line utility, from within a stored procedure. For this part, you
have the extended stored procedure xp_cmdshell. This very useful procedure
allows the user to call operating system commands like OS commands, batch
files, and executables. So, for example, you could type this in your Query
Analyzer and then it would display the contents of drive C:
master..xp_cmdshell 'dir c:'

As you probably would notice, xp_cmdshell takes in one string parameter and
executes it in the operating system as you would normally type it in the
command line yourself (dir c: for example).

Clear enough? Ok, I'll provide a sample stored procedure which implements
this:

use pubs
go
if exists(select name from sysobjects where name = 'queryOut' and type =
'P&#39
drop procedure queryOut
go
create procedure queryOut
@query varchar (200)
as
declare @stmt varchar (300)
set @stmt = 'bcp "' + @query + '" queryout cutput.txt -S junjun -T -c'
exec master..xp_cmdshell @stmt


Just copy this piece of code, change the "-S junjun" in the "set @stmt..."
line (2nd to the last line) to the name of your server, and then execute in
your QueryAnalyzer window.

If all goes well, you now have stored procedure name queryOut in your "pubs"
database.

Now to test this, just execute this in:

use pubs
go
exec queryOut 'select * from pubs..authors'

This procedure, when executed, will create cutput.txt and the file will
contain all the rows in your pubs..authors table.

Hope this lengthy reply helps.
Have a nice day.

[Si]

In SQL7 this can be done much more easily just by using DTS, follow the wizard step by step or do it yourself.


------------
at 9/9/99 4:34:24 AM

So basically, you wanna create a stored procedure which will execute a
query and then store the results to a text file. As you probably know, bcp
in sql 7 can output the results of a query to a text file. So you have part
of your problem solved. The only issue now is how to call bcp, which is a
command line utility, from within a stored procedure. For this part, you
have the extended stored procedure xp_cmdshell. This very useful procedure
allows the user to call operating system commands like OS commands, batch
files, and executables. So, for example, you could type this in your Query
Analyzer and then it would display the contents of drive C:
master..xp_cmdshell 'dir c:'

As you probably would notice, xp_cmdshell takes in one string parameter and
executes it in the operating system as you would normally type it in the
command line yourself (dir c: for example).

Clear enough? Ok, I'll provide a sample stored procedure which implements
this:

use pubs
go
if exists(select name from sysobjects where name = 'queryOut' and type =
'P&#39
drop procedure queryOut
go
create procedure queryOut
@query varchar (200)
as
declare @stmt varchar (300)
set @stmt = 'bcp "' + @query + '" queryout cutput.txt -S junjun -T -c'
exec master..xp_cmdshell @stmt


Just copy this piece of code, change the "-S junjun" in the "set @stmt..."
line (2nd to the last line) to the name of your server, and then execute in
your QueryAnalyzer window.

If all goes well, you now have stored procedure name queryOut in your "pubs"
database.

Now to test this, just execute this in:

use pubs
go
exec queryOut 'select * from pubs..authors'

This procedure, when executed, will create cutput.txt and the file will
contain all the rows in your pubs..authors table.

Hope this lengthy reply helps.
Have a nice day.