|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
January 9th, 2004, 04:43 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Statistics
Hello,
I want to be able to access the IO statistics and the Execution plans for stored procedures. I was trying to return the results to an ADO recordset in the syntax of:
Code:
set STATISTICS IO on
exec sp Get Item 1
GO
The IO statistics display the table processing information. I wanted to return this information back to an ADO recordset, so I can access it programmatically. I tried, but I kept getting an error. Does anybody know if this is possible in some way, shape, or form?
Thanks,
Brian Mains
__________________
Brian
|
January 10th, 2004, 10:22 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
|
|
Quote:
quote:
exec sp Get Item 1
|
I'm not sure what this is.
Quote:
quote:
The IO statistics display the table processing information. I wanted to return this information back to an ADO recordset, so I can access it programmatically. I tried, but I kept getting an error. Does anybody know if this is possible in some way, shape, or form?
|
What error did you get?
As I understand it, the SET STATISTICS IO option produces output in the same manner as a PRINT statement. The output of a PRINT statement is returned as an error of severity level 0. Try looking in the ADO connection object's error collection after you execute the command.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
January 12th, 2004, 12:32 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Hello,
I meant exec spGetItem; I don't know why the spaces were there. Anyways, the error I get is "syntax error or access violation" (80040E14, SQL Server specific error). My code is (to test if it works):
Code:
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open g_strConnectionString
set objCmd = Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn
objCmd.CommandType = 4
'Append the set STATISTICS IO on statement to the execute statement
objCmd.CommandText = CreateIOStatisticsSQL(strSQL)
set objRS = objCmd.Execute
Response.Write("EOF: " & objRS.EOF)
set objRS = nothing
set objCmd.ActiveConnection = nothing
set objCmd = nothing
set objConn = nothing
I get the response, but I want to return the response to a recordset.
Thanks.
|
January 12th, 2004, 03:48 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
|
|
What does the function 'CreateIOStatisticsSQL(strSQL)' do?
If you are getting a syntax error, perhaps this function is returning a malformed TSQL command string. Try response.writing the command text before you execute to insure it is properly formed.
Quote:
quote:
...I want to return the response to a recordset.
|
My previous message addressed this:
"As I understand it, the SET STATISTICS IO option produces output in the same manner as a PRINT statement. The output of a PRINT statement is returned as an error of severity level 0. Try looking in the ADO connection object's error collection after you execute the command."
In other words, you can't return the results as a recordset, but the results are returned as an error (of severity 0) so you can parse the (error) string apart to get what you want.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
January 12th, 2004, 05:05 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
I wasn't quite understanding that you could get the information from the connection error collection.
Thanks.
|
January 12th, 2004, 05:25 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
I tried to do this instead:
Code:
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open g_strConnectionString
objConn.Execute("set STATISTICS IO on exec spGetItem 1")
msgbox(objConn.Errors.Count)
but still no success (shows 0 errors).
Any other ideas?
Thanks.
|
January 12th, 2004, 06:42 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
|
|
I'm guessing now (I admit I was guessing then ), but as I read deeper into it, it would seem that the output of a TSQL PRINT statement (which presumably is the same type of output as the output of SET STATISTICS IO) is considered an 'informational' message and not an error. The docs actually said that a PRINT message was an error with severity level 0, but then elsewhere it alludes to this severity as 'informational'.
It seems Informational messages are communicated back to the client via the 'InfoMessage' event on the connection object. You'd have to set up an event handler on your connection object and trap the event to get the string - straightforward for a VB app, but I'm pretty weak on ASP script code, so perhaps someone else can advise you how to handle a connection object server side event in ASP, if that is even possible.
Sorry I couldn't be more help.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
January 13th, 2004, 10:30 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
I tried the InfoMessage event in VB, and I got the same issue, where the event wasn't raised. Does anybody know if maybe ADO.NET supports this functionality?
Thanks.
|
|
|