How can I get the result of a SQL PRINT Statement

I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.

I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.

I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.If you have the SQL Client tools then you can use Profiler to catch the statement that is sent as D-SQL. This is the best way to extract the D-SQLs that are getting executed rather than the Print statement ...

You can on the contrary use a Select and pass this D-SQL statement and catch it in your recordset returned ...

These are some of the options I can think of ...|||Where would I get the SQL Client tools?|||The simplest answer might be to create a DebugLog table and insert your dynamic SQL statement into it.

First, create the table to log your SQL statement:

CREATE TABLE
DebugLog
(
SQLStatement varchar(8000),
AddDate datetime DEFAULT GETDATE()
)

Next, add code to your stored procedure to insert your statement into the log file:

DECLARE @.SQLStatement Varchar(8000)
SET @.SQLStatement = 'SELECT * FROM test'
INSERT INTO DebugLog (SQLStatement) VALUES (@.SQLStatement)
EXEC(@.SQLStatement)

Next, take a look at the SQL statements that were executed (the most recent statement will be on top):

SELECT * FROM DebugLog ORDER BY AddDate DESC

Alternately, you could create an OUTPUT parameter in your sproc and pass the value of @.SQLStatement back to it and display it on your ASP.NET page.

Terri|||Thanks for the help. I'll try that. In point of fact the dynamic sql I'm trying to debug is using sp_executesql with an output parameter. I am trying to retrieve the recordset count based on the criteria parameters I'm passing to it from my asp.net application. In the application, the value returned is zero, which I know cannot be correct. Once I verify the proper assembly of the query, I can concentrate on the syntax of sp_executesql.|||Hi Terri,

When I first saw this post, I was going to respond that PRINT only writes to Query Analyzer. But upon researching it, I found that BOL has this to say about it in the Using PRINT topic:

"The message is returned as an informational error in ADO, OLE DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set to 0, and the error message string is set to the character string specified in the PRINT statement."

This suggests that one could capture the error and do something with the PRINT text. But there can be multiple PRINT statements in a sproc, so I assume this would be an SqlException with nested exceptions.

Anyone ever done anything with this? Could be an interesting thing to explore.

Don|||yes, in my data access layer the error handling is like so.

try
{
oCmdExecute.ExecuteNonQuery(); // or ExecuteReader etc...
}
catch(SqlException ex)
{
foreach(SqlError err in ex.Errors)
{
strErrorString = strErrorString + "SqlError: #" + err.Number.ToString () + "\n" + err.Message;
}
strErrorString = strErrorString + "\n\nStored proc: " + sSql + "\n";
}

the SqlError collection will contain all the print statements, of course this only when the stored procedure fails, so you would have to call RAISERROR(' test error ', 16, 1) to force it into the exception block.

Note: that is RAISERROR not RAISEERROR go figure?|||Cool.

Note: that is RAISERROR not RAISEERROR go figure?

Yeah, that's been something odd since the statement was first added to T-SQL.

Don