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 programatically what output of a sp will be?

Using Sql Srv 7

I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
to determine all the sps in a db, and what input parms there are for a
particular sp... but... if the sp returns a result set, is there a way to
find out the stru of that in a similar manner?Am Wed, 5 Oct 2005 09:04:14 -0500 schrieb spiffo:

> Using Sql Srv 7
> I know I can use the system sp's sp_stored_procedures and sp_sproc_columns
> to determine all the sps in a db, and what input parms there are for a
> particular sp... but... if the sp returns a result set, is there a way to
> find out the stru of that in a similar manner?

I never put time in finding an answer for this question but my first
thought is, that it cannot be possible, because i can write a procedure
like this:

create stored procedure myRandom as
if datepart(s,getdate()) % 2 = 1 select 'field_1', 1
else select 123, 'last'

So what is the struct of the resultset? It changes every second!

bye,
helmut

How to determine output format during rendering?

Is there a "global" variable I could test for the output format? I need to change styles/formats for a report based on whether its XML, Excel or CSV. There has to be a better way instead of developing reports for a specific format, in other words, I would like one report instead of multiple versions.

Any suggestions would be helpful.

Bob

Hi,

no so far there is no render-specific format. You will have to design the report for the appropiate format or produce a report to fit all rendering formats.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

sql

How to determine if stor proc parameter is output or input

I know that you can retrieve whether a parameter is for output buy way
of the "isoutparam" field, but is there anything that tells you whether
a parameter is input/output?
thanks(jw56578@.gmail.com) writes:
> I know that you can retrieve whether a parameter is for output buy way
> of the "isoutparam" field, but is there anything that tells you whether
> a parameter is input/output?

The only true output-only value is the return value. All parameters are
input/output. This is T-SQL, not Ada. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I am using a .net Parameter builder, to build paramter objects based
on system information. When it sees a parameter used for input and
output, it "isoutparam" indicates that it is an output, so its
"Direction" attribute is assigned a value of output. But if i want to
use it as an input, it doesn't work.|||(jw56578@.gmail.com) writes:
> I am using a .net Parameter builder, to build paramter objects based
> on system information. When it sees a parameter used for input and
> output, it "isoutparam" indicates that it is an output, so its
> "Direction" attribute is assigned a value of output. But if i want to
> use it as an input, it doesn't work.

That builder seems to have a bug. :-)

The only "parameter" that should have Direction.IsOutput is the return
value.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql