Retrieving SQL output

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using ADO.NET from ASP.NET. Have a SqlConnection executing stuff (stored procedures)

I would like to be able to retrieve informational messages from the SQL sps, which comment on what they are doing (updating tables etc.), and show them to the user/write them to a file. Is there any way I can get at anything that comes from a SQL PRINT statement in this context? osql utility lets this go to standard output, which can be captured; that's the sort of thing I have in mind, but I suspect it may go into the blue nowhere from ADO.NET. Or is my best/only solution to rewrite messages via RAISERROR <severity-level-less-than-10>, and catch in code via InfoMessage event + InfoMessageEventArgs

And if there is a better ng than here to post this, please let me know...
 
Informational messages include PRINT and RAISEERROR (severity < 11)
statements - they won't just go into the blue nowhere but show up in
the InfoMessage event handler.

Just be aware that the events won't start firing until after the batch
has completed.
 
Any information you want from the Stored Procedure can be returned as an
output parameter.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

jb said:
Using ADO.NET from ASP.NET. Have a SqlConnection executing stuff (stored procedures).

I would like to be able to retrieve informational messages from the SQL
Click to expand...
sps, which comment on what they are doing (updating tables etc.), and show
them to the user/write them to a file. Is there any way I can get at
anything that comes from a SQL PRINT statement in this context? osql
utility lets this go to standard output, which can be captured; that's the
sort of thing I have in mind, but I suspect it may go into the blue nowhere
from ADO.NET. Or is my best/only solution to rewrite messages via RAISERROR
 
Scott, thanks, didn't try PRINT for InfoMessage, will try, sounds just what I need

(Kevin, thanks, I know about output parms, not at all suitable here, there will me messages all over the place, very long, in deeply nested sp calls etc.)

Since you guys seem so knowledgable and helpful, may I ask one other question here. I'm sure this isn't really the best ng for it, but someone might know or point me in the right direction

My code in question is to do with upgrading to later versions of the database. Often I want to insert a new column, and in development I have chosen to actually insert these columns between others, i.e. not just appended at the end. I know that ALTER TABLE ADD <column> is simple but will only append; this leaves me with upgraded tables havin a different column order from newly created ones, which worries me. I can see from EM etc. the script it generates to actually insert a column, involving new temp table, dropping & recreating constraints etc. etc. This script is obviously complex, and I could have trouble covering all the possibilities. For various reasons I can't just copy the script from EM

Does anyone know if there is, say, a generic stored procedure anywhere I could fetch which has been written to insert a column into the middle of other columns? I can't see one in MS stuff. It needs to be fairly robust

----- Scott Allen wrote: ----

Informational messages include PRINT and RAISEERROR (severity < 11
statements - they won't just go into the blue nowhere but show up i
the InfoMessage event handler.

Just be aware that the events won't start firing until after the batc
has completed.

-
Scot
http://www.OdeToCode.co

On Tue, 13 Apr 2004 05:16:02 -0700, "jb
 
Sorry, jb, I don't know of a procedure to do that. Perhaps someone in
the SQL newsgroup would know.
 
Back
Top