SQL to Text file

[KritiGuleria]

hi.
please guide me how to get the output of a PROCEDURE into a TEXT-FILE placed anywhere in hard-disc.
i am using SQL server 2000.

[KritiGuleria]

basically i have to keep the track of errors encountered in a procdure, similar to a log file. it should display successful if the execution succeeds and failure plus some additional info regarding the record if execution fails

[azimmer]

One option is to log to a table and then bcp it to a file when you're done. On bcp see: http://msdn2.microsoft.com/en-us/library/ms162802.aspx (I believe bcp is available for SQL Server 2000 as well but I'm not positive.)

Another option is to use the OPENDATASOURCE functionality with a text file output. I've never tried this but see no reason why it shouldn't work.

A third one is to use DTS packages (DTS can output to text file).

[KritiGuleria]

thanks a lot.... but using tables( for bcp) is something which is not cost efficient.
and the error logging through DTS is providing me the reason for error but not the record for which error was raised. i want the description corresponding to each recordset, whatever be the result, failure or success.

i will try for OPENDATASOURCE. in the meantime any other suggestions?

One option is to log to a table and then bcp it to a file when you're done. On bcp see: http://msdn2.microsoft.com/en-us/library/ms162802.aspx (I believe bcp is available for SQL Server 2000 as well but I'm not positive.)

Another option is to use the OPENDATASOURCE functionality with a text file output. I've never tried this but see no reason why it shouldn't work.

A third one is to use DTS packages (DTS can output to text file).

[KritiGuleria]

OPENDATASOURCE is not getting me anywhere...
cant figure out how could i write anything into a textfile using SQL

thanks a lot.... but using tables( for bcp) is something which is not cost efficient.
and the error logging through DTS is providing me the reason for error but not the record for which error was raised. i want the description corresponding to each recordset, whatever be the result, failure or success.

i will try for OPENDATASOURCE. in the meantime any other suggestions?

[azimmer]

OPENDATASOURCE is not getting me anywhere...
cant figure out how could i write anything into a textfile using SQL

Try this, it works:

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=d:\Temp',new#txt)
SELECT myField
FROM myTable
 

Important: d:\Temp\new.txt must exist before executing this statement and has to have a myField field header.

I hope it gets you somewhere. I'm no expert on this, sorry.

[KritiGuleria]

[quote=azimmer]Try this, it works:

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=d:\Temp',new#txt)
SELECT myField
FROM myTable
 

thanks. but in my case i have to get the result from procdure and not from any table present in database.

[azimmer]

[quote=KritiGuleria]

Try this, it works:

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=d:\Temp',new#txt)
SELECT myField
FROM myTable
 

thanks. but in my case i have to get the result from procdure and not from any table present in database.

INSERT works with fixed values (or variables) as well:

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=d:\Temp',new#txt)
VALUES ('Error code=12','Record no=1111')
 

I used SELECT as an example only. It is the same INSERT as for tables, only the "table" is an OPENROWSET.

[KritiGuleria]

[quote=azimmer][quote=KritiGuleria]
INSERT works with fixed values (or variables) as well:

hey, thanks a lot!!