Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Da_Retina
Posting Yak Master
109 Posts |
 Posted - 2001-12-08 : 03:44:10
|
| Hi..How can i print to a file in a stored procedure?..I want to track results being generated in a loop by appending the results to a text file in order to be able to trace the procedure.Thanks in Advance |
|
|
fisherman_jake
Slave to the Almighty Yak
159 Posts |
Posted - 2001-12-08 : 06:28:00
|
| Da_Retina, G'day I've had one too many sleepless nights with the new baby.. The simple solution to your answer is to use xp_cmdshell.. Now the hard and experimental thing to write would be the results into the text file. Plus you have to understand that xp_cmdshell will execute a command on the server's drives or mapped drives SO BE VERY CAREFUL. This an example of how you would run it:--<TSQL>--master..xp_cmdshell 'ECHO ' + @@ROWCOUNT + ' >> OUTPUT.TXT'--<TSQL>--Now let me explain what that line does, firstly "ECHO" is a dos command, @@ROWCOUNT is the number of rows returned from your last query, a single ">" will always wipe out your text file a double ">>" will always append, "OUTPUT.TXT" is any file name you want to name your output file. Now a simple way to retrieve your text file is through the use of xp_cmdshell as well..--<TSQL>--master..xp_cmdshell 'TYPE OUTPUT.TXT'--<TSQL>--Now that should display your result in the result pane in QA..Hope that helps..==================================================World War III is imminent, you know what that means... No Bag limits!!!Master Fisherman |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-08 : 06:46:52
|
| There are a few methods suggested at www.nigelrivett.comCreating a text file from a stored procedureWould agree that the redirection given above is probably the best method for a trace.I assume you want to trace things inside a transaction that gets aborted otherwise a simple write to a table would suffice.You could also try the profiler which can log to a table or file.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Printing To a File