print stored procedure result data into text file
-
I'm currently usign a stored procedure that prints some data into the SQL Query Analyser message console with "print":
select @message = " Some label " + @vLocalVar
print @message
Does anyone know a way to put this result, programaticaly, into a text file?
Thanx!
GV
-
bcp will not work for stored procedures. You will need to use osql.
See BOL for examples of how to. Here's one:
To Execute an sp called sp_output and store the result in a text file:
osql /U user /P password /d database /S server /Q "sp_output" -o output.txt.
Watch out, though. The whole line is case sensitive. Get all the server/db names correct etc.
------------------------------
The Users are always right - when I'm not wrong! -
Great!!!
Thanks a lot.
GV
-
Is there any way of telling osql that I connect to the database by using windows authentification and not another security credential ?
GV
-
You need to specify -E. Never done so myself but take a look at this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_mta_01_2r1e.asp
------------------------------
The Users are always right - when I'm not wrong! -
This sort of question pops up now and again. Whilst the solutions presented will work and possibly satisfy most needs it got me thinking.
What if I only wanted the text from the print to appear in the output file? I could use SET NOCOUNT ON to remove the '(1 row affected)' output. Also what if I did not want to use osql/bcp etc.
So, I wrote an Extended Procedure called xp_print that takes 3 parameters (file, mode and string) to write/append the string to the file.
Not that I have a need for this but will wait for the day
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply