Microsoft KB Archive/253240: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
(3 intermediate revisions by the same user not shown) | |||
Line 60: | Line 60: | ||
== CAUSE == | == CAUSE == | ||
The SQL Server OLE DB provider provides more accurate information (than ODBC) to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a | The SQL Server OLE DB provider provides more accurate information (than ODBC) to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected or a result set. You can walk through these result sets in ADO using the NextRecordset method on the Recordset object.<br /> | ||
<br /> | <br /> | ||
The SQL Server ODBC provider, however, does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement, if it has one. This is why this problem may not manifest with ODBC.<br /> | The SQL Server ODBC provider, however, does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement, if it has one. This is why this problem may not manifest with ODBC.<br /> | ||
Line 96: | Line 96: | ||
<li><p>Create an ASP page with the following code:<br /> | <li><p>Create an ASP page with the following code:<br /> | ||
<br /> | <br /> | ||
'''Note''' You must change UserID= | '''Note''' You must change UserID=<username> to the correct value before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.</p> | ||
<pre class="codesample"> | <pre class="codesample"><% | ||
Set oConn = Server.CreateObject( | Set oConn = Server.CreateObject("ADODB.Connection") | ||
Set Rs = Server.CreateObject( | Set Rs = Server.CreateObject("ADODB.Recordset") | ||
On Error Resume Next | On Error Resume Next | ||
oConn.Open( | oConn.Open("Provider=SQLOLEDB.1;User ID=<username>;Initial Catalog=Northwind;Data Source=DataSourceName") | ||
Set oCmd = Server.CreateObject( | Set oCmd = Server.CreateObject("ADODB.Command") | ||
Set oCmd.ActiveConnection = oConn | Set oCmd.ActiveConnection = oConn | ||
oCmd.CommandText = | oCmd.CommandText = "TestProc" | ||
oCmd.CommandType = adCmdStoredProc | oCmd.CommandType = adCmdStoredProc | ||
oCmd.Parameters.Append oCmd.CreateParameter( | oCmd.Parameters.Append oCmd.CreateParameter("RETURN_VALUE",3,4,0) | ||
oCmd.Execute() | oCmd.Execute() | ||
If oCmd.ActiveConnection.Errors.Count | If oCmd.ActiveConnection.Errors.Count > 0 Then | ||
For Each oError in oCmd.ActiveConnection.Errors | For Each oError in oCmd.ActiveConnection.Errors | ||
Response.Write | Response.Write "Description = " & oError.Description & "<BR>" | ||
Next | Next | ||
End If | End If | ||
Response.Write( | Response.Write( "Return Value = " & oCmd.Parameters("RETURN_VALUE") & "<BR>") | ||
oConn.Close | oConn.Close | ||
Line 128: | Line 128: | ||
Set oCmd = Nothing | Set oCmd = Nothing | ||
% | %> | ||
</pre></li> | </pre></li> | ||
<li><p>Create a stored procedure similar to the following:</p> | <li><p>Create a stored procedure similar to the following:</p> | ||
Line 137: | Line 137: | ||
insert into xxx values(1) | insert into xxx values(1) | ||
If @@error | If @@error <> 0 | ||
Begin | Begin | ||
rollback | rollback | ||
Line 143: | Line 143: | ||
End | End | ||
insert into yyy values( | insert into yyy values(<Invalid Data>) | ||
if @@error | if @@error <> 0 | ||
begin | begin | ||
rollback | rollback | ||
Line 151: | Line 151: | ||
insert into zzz values(3) | insert into zzz values(3) | ||
if @@error | if @@error <> 0 | ||
begin | begin | ||
rollback | rollback |
Latest revision as of 13:52, 21 July 2020
Article ID: 253240
Article Last Modified on 12/3/2003
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.1 Service Pack 1
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q253240
SYMPTOMS
When executing a stored procedure with multiple queries (SELECTs, INSERTs and UPDATEs), the ActiveX Data Objects (ADO) errors collection appears not to be populated after an error occurs in the stored procedure. The stored procedure might also not return values from its RETURN statement.
CAUSE
The SQL Server OLE DB provider provides more accurate information (than ODBC) to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected or a result set. You can walk through these result sets in ADO using the NextRecordset method on the Recordset object.
The SQL Server ODBC provider, however, does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement, if it has one. This is why this problem may not manifest with ODBC.
In both cases, return values from the stored procedure may not be returned.
RESOLUTION
To resolve this problem, make sure that the SET NOCOUNT ON statement comes before any other SQL statements in the stored procedure, as in the following:
CREATE PROCEDURE TestProc AS SET NOCOUNT ON -- This is off by default SELECT au_lname FROM Authors RETURN 0
STATUS
This behavior is by design.
MORE INFORMATION
Steps to Reproduce the Behavior
Create an ASP page with the following code:
Note You must change UserID=<username> to the correct value before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.<% Set oConn = Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") On Error Resume Next oConn.Open("Provider=SQLOLEDB.1;User ID=<username>;Initial Catalog=Northwind;Data Source=DataSourceName") Set oCmd = Server.CreateObject("ADODB.Command") Set oCmd.ActiveConnection = oConn oCmd.CommandText = "TestProc" oCmd.CommandType = adCmdStoredProc oCmd.Parameters.Append oCmd.CreateParameter("RETURN_VALUE",3,4,0) oCmd.Execute() If oCmd.ActiveConnection.Errors.Count > 0 Then For Each oError in oCmd.ActiveConnection.Errors Response.Write "Description = " & oError.Description & "<BR>" Next End If Response.Write( "Return Value = " & oCmd.Parameters("RETURN_VALUE") & "<BR>") oConn.Close Set oConn = Nothing Set oCmd = Nothing %>
Create a stored procedure similar to the following:
ALTER Procedure TestProc AS -- Uncommenting the line below resolved this problem -- set nocount ON begin tran insert into xxx values(1) If @@error <> 0 Begin rollback return 1 End insert into yyy values(<Invalid Data>) if @@error <> 0 begin rollback return 2 end insert into zzz values(3) if @@error <> 0 begin rollback return 3 end commit tran return 0
- Run the ASP page, and you should get the following result instead of an error message and a return value of 2.
Return Value =
Keywords: kberrmsg kbcodesnippet kbdatabase kbprb KB253240