How to direct T-SQL executoin output to a file

Is there a SQL Server 2000 equivalent of the Oracle SPOOL command like SPOOL 'C:\TEMP\MyFile.txt' which can be placed at the top of a series of SQL commands to direct the execution messages and results to the nominated file ? I want to use this in a Stored Procedure executed from within Access and capture all system and error messages at the server level.

hi,

open sql query analyzer

click on "query"

click on "results to file"

run a query.

or better yet

run BCPor the bulk copy program

regards,

joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

I found Steve's tip very helpful, however I also found out that you have to refer to the database name that the table belongs to even if the database you are refering to is the currently selected database within query analyser. This is because the command shell cannot see what the default database within query analyser is.

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

How to direct T-SQL executoin output to a file

Is there a SQL Server 2000 equivalent of the Oracle SPOOL command like SPOOL 'C:\TEMP\MyFile.txt' which can be placed at the top of a series of SQL commands to direct the execution messages and results to the nominated file ? I want to use this in a Stored Procedure executed from within Access and capture all system and error messages at the server level.

hi,

open sql query analyzer

click on "query"

click on "results to file"

run a query.

or better yet

run BCPor the bulk copy program

regards,

joey

|||

This will work if xp_cmdshell is enabled

DECLARE @.isqlString varchar(255)

SELECT @.isqlString = 'isql -Q "SELECT top 10 FROM TABLE" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @.isqlString

|||

Hi,

I found Steve's tip very helpful, however I also found out that you have to refer to the database name that the table belongs to even if the database you are refering to is the currently selected database within query analyser. This is because the command shell cannot see what the default database within query analyser is.

Please find the following example; -

declare @.str varchar(255)
select @.str = 'isql -Q"select * from spintlslive.dbo.T_Payments WHERE Paydate = ''07/13/2007''" -E -w255 -oD:\UPLOADS\payments.txt'
exec master..xp_cmdshell @.str

how to delete text file

hi...
how to delete text files from c drive (ex: c:\sale\300407.txt) using ms sql express edition 2005 ?
plz help me...

enable xp_cmdshell from start-- programs -- SQL Server 2005-- SQL Sserver surface Area Configuration tool -- Surface area confiugration for feature --

enable XP_Cmdshell

then run

exec xp_cmdshell 'del c:\yourfilename.txt'

Madhu

|||Be aware that if you are using Windows Authentication, the User who is accessing th e procedure will need the appropiate rights to delete the file. If you are using SQL Server authentication, it has to be the SQL Server Service account, if you don′t won′t either one, you will have to create a proxy account for the XP_CMDSHELL.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||i'm using windows authentication. the command given still error. here the error :

c:\010407_S13103.txt
Access is denied

how about delete multiple text file from folder (exale) in drive c using ms sql express edition 2005. is using the same command?|||

its a permission problem. this user do not have permission in the folder. Jens's post already mention how to handle it. Basically, what xp_cmdshell used for is to run a shell command from SQL Platform. Delete multiple files use

xp_cmdshell del *.txt

Madhu

|||the user is admin. there should be no permission problem. but i still get the same error after i run the command above for multiple files. please be remind that i'm using ms sql express edition 2005.|||Are you using WIndows authentication for connecting to the database ?

|||yes i'm using windows authentication|||Then you either have a proxy account configured which is not able to access the file, or the user you are currently using for the connection is not allowed, or the usage of the directory (if you using Vista) is required elevation.

|||i try this command in ms sql 2005 express edition but still get the same error. here the command :

declare @.del varchar(1000)
select @.del = 'del c:\Transfer\*.TXT'
exec xp_cmdshell @.del

here the error:
c:\Transfer\010407SALE.131.TXT
Access is denied

i'm using windows authentication and using admin account. so there should be no permission problem. i already enable xp_cmdshell in SQL Server Surface Area Configuration. why this error still occur? plz help me. i'm using ms windows xp.|||Is the folder encrypted ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||no