PRB: SQL Server Returns Output Parameters Only After Resultsets (256234)


The information in this article applies to:

  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.01
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.1 SP1
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
This article was previously published under Q256234

SYMPTOMS

When stored procedures (SP) are executing in Microsoft SQL Server, output parameters are returned only after all of the resultsets have been completely fetched to the client. Attempts that are made to read output parameters prior to a full fetch of all resultsets do not return data.

CAUSE

The default cursorLocation property of a connection object is adUseServer, a server side cursor. When executing a SQL statement that returns a recordset with the default cursorLocation property, not all of the matching rows are returned to the client program at once. Until all of the rows are fetched and returned to the client, any output parameter from a stored procedure is not returned.

RESOLUTION

Setting the cursorLocation property of the connection object to adUseClient, a client side cursor, causes all the records in the resultsets to be fetched immediately and returned to the client program, allowing access to both the resultset and output parameters immediately after the procedure is run.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create a stored procedure in the PUBS database of your SQL Server by using the following script:
    CREATE PROCEDURE OutputParmTest

@OutputParm INT OUTPUT

AS

SET NOCOUNT ON 

SET @OutputParm = 23

SELECT * FROM authors
  2. Modify the query in the stored procedure to reflect the database in which it is created.
  3. Insert the following code into a new ASP page and then save the page to a folder in your Web site:
    <%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> </HEAD> <BODY> <% dim cn, cmd, rs const adInteger = 3 const adParamOutput = 2 set cn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
set rs = Server.CreateObject("ADODB.Recordset")
'cn.CursorLocation = adUseClient ' Uncomment this line to get the output parameter early. cn.Open "DSN=pubs;UID=sa;PWD=;" set cmd.ActiveConnection = cn cmd.CommandType = adCmdStoredProc cmd.CommandText = "OutputParmTest" cmd.Parameters.Append cmd.CreateParameter("Output", adInteger, adParamOutput) set rs = cmd.Execute Response.Write "Output parameter: " & cmd.Parameters("Output") & "<BR>" ' This will not return a value. %> </BODY> </HTML>
  4. Create a DSN to point to the PUBS database of your SQL Server.
  5. Move to the page from a Web browser.

    Unless the CursorLocation property line in the preceding code is uncommented, the value of the parameter is blank. When this line is uncommented, the code returns the value of the parameter.
Modification Type:MajorLast Reviewed:9/30/2003
Keywords:kbprb KB256234
©2003 Microsoft Corporation. All rights reserved.