Microsoft KB Archive/256234

= PRB: SQL Server Returns Output Parameters Only After Resultsets =

Article ID: 256234

Article Last Modified on 9/30/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 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.



Steps to Reproduce the Behavior
  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  Modify the query in the stored procedure to reflect the database in which it is created.  Insert the following code into a new ASP page and then save the page to a folder in your Web site: <%@ Language=VBScript %>     <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> </li> Create a DSN to point to the PUBS database of your SQL Server.</li> 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.</li></ol>

Keywords: kbprb KB256234

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.