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 determine date/time message was placed onto Queue?

I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.

Which time are you after. The time it was put on the initiator queue or the target queue?

You can add the time to the message when you send it.

|||

Ideally, both.

I was looking for something out-of-the-box... not something where I craft my own message. The messages I will be receiving are based on a pre-defined schema and I did not want to go back to the design table with adding new elements. You would think that the "queue" table would have an additional column for "created date/time" basically.

If anything else, I think that should be a consideration for a future enhancement in the next version of Broker.

|||

Please add a suggestion on connect.microsoft.com/sqlserver/feedback.

Make usre you say exactly which date you are after.

|||There would be a performance penalty for storing SENT and ENQUEUED times into the target queue for each message even if the user was not really interested in those statistics. Our current model is that any user-specific data (sent-timestamp, order-number, request-id, message-id, sender-user-name, etc) would be encapsulated into the message_body itself.