Exec a resultset to .txt file directly.

Hi all.
Is there a way I can have my resultset saved to a file directly from a SP
using SQL2000.
E.g.
use northwind
CREATE PROCEDURE ToTxt
AS
SELECT *
FROM Customers
/* I want something like*/
/*
SELECT *
FROM Customers
TO c:\Cust.txt
*/
/* Then I can run the following to create the file */
Exec ToTxt
Thanx all.
ghNo, One way is to use xp_cmdshell to execute either OSQL or BCP to output th
e text file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geir Holme" <geir@.multicase.no> wrote in message news:Odb$wT9JGHA.1312@.TK2MSFTNGP09.phx.gb
l...
> Hi all.
> Is there a way I can have my resultset saved to a file directly from a SP
> using SQL2000.
> E.g.
> use northwind
> CREATE PROCEDURE ToTxt
> AS
> SELECT *
> FROM Customers
> /* I want something like*/
> /*
> SELECT *
> FROM Customers
> TO c:\Cust.txt
> */
> /* Then I can run the following to create the file */
> Exec ToTxt
> Thanx all.
> gh
>
>

Exec a resultset to .txt file directly.

Hi all.
Is there a way I can have my resultset saved to a file directly from a SP
using SQL2000.
E.g.
use northwind
CREATE PROCEDURE ToTxt
AS
SELECT *
FROM Customers
/* I want something like*/
/*
SELECT *
FROM Customers
TO c:\Cust.txt
*/
/* Then I can run the following to create the file */
Exec ToTxt
Thanx all.
ghTry something like :
EXEC master..xp_cmdshell 'osql.exe -S MyServer -U MyUserName -P MyPassword -
Q "EXEC storedProcedure" -o "C:\savedResultset.txt"'
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains - www.ciquery.com/articles/art_
04.asp
"Geir Holme" <geir@.multicase.no> wrote in message news:%23rEX5SyJGHA.2808@.TK2MSFTNGP15.phx.
gbl...
> Hi all.
> Is there a way I can have my resultset saved to a file directly from a SP
> using SQL2000.
>
> E.g.
>
> use northwind
> CREATE PROCEDURE ToTxt
> AS
> SELECT *
> FROM Customers
> /* I want something like*/
> /*
> SELECT *
> FROM Customers
> TO c:\Cust.txt
> */
>
> /* Then I can run the following to create the file */
> Exec ToTxt
>
> Thanx all.
>
> gh
>
>|||It would be something like
e.g.: XP_cmdshell 'OSQL -E -Q"SELECT * FROM Customers" -O"C:\Cust.txt"'
paramters may vary on your server settings.
HTH, Jens Suessmeyer.sql