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 if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.
Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END
sql

How to determine if a linked server is available

Hello all,
I need to write data from one SQL server in to a linked server running on an
other machine. How can i check with T-SQL commands if this server is
available?
If it is not available the stored procedure directly quits with an error.
But this procedure should continue in differt ways, depending on the result
of the linked server transaction.
Both servers are MS SQL 2000, SP3a
Thanks a lot.Try
if exists(select * from master.dbo.sysservers where srvname =
'OtherServer')
BEGIN
-- my code
END

How to delete repeated entries from table using T-SQL statement

Hi Friends..

I want delete repeated entries which comes twice in a table. How to delete that extra entry and keep each single entry using T-SQL statement(SQL server 2000). Please give me the example.

Thanks & Regards,
Ravi.Hi,

You may select distinct the duplicate entry and save it in a temporary table, then delete the double entry to the main table and insert the content of the temporary table to the main table.|||Hi,

Thank you for giving solution, but still I don't know how to do that, can you send me code and e.g. It will help me for understanding. I hope you will give this solution very soon.

Thanks & Regards,
Ravi.