Opening an ADODB recordset from SQL Server 2012 Stored Procedure

• normturgeon

  Old Hand

  Points: 337

  More actions

  November 11, 2013 at 4:25 pm

  #284876

  I have a large SQL Server database with hundreds of complex stored procedures, many of which execute a Select statement from a local temporary table. Here is a simplified example.

  CREATE PROCEDURE [dbo].[usp_getMyPremiums]

  @PolicyID int

  AS

  CREATE TABLE #tblPremiums (Premium money NULL, MemberPremium money NULL) ON [PRIMARY];

  INSERT INTO #tblPremiums (Premium, MemberPremium)

  SELECT SUM(LiabilityPremium), SUM(MemberLiabilityPremium)

  FROM tblACCTCharge

  GROUP BY PolicyID

  HAVING (PolicyID = @PolicyID)

  SELECT Premium, MemberPremium

  FROM #tblPremiums

  Note that in this version of the stored procedure we first Insert values into a local temporary table and then selects the values from that table.

  A different version of this stored procedure that does not use a temporary table is as follows:

  CREATE PROCEDURE [dbo].[usp_getMyPremiums]

  @PolicyID int

  AS

  SELECT SUM(LiabilityPremium) AS Premium, SUM(MemberLiabilityPremium) AS MemberPremium

  FROM tblACCTCharge

  GROUP BY PolicyID

  HAVING (PolicyID = @PolicyID)

  Both versions return the same values when executed from SQL Server Management Studio's with

  EXECUTE usp_getMyPremiums 11407

  This is true with the database in both SQL Server 2008 and SQL Server 2012.

  I call on such stored procedures to open ADODB recordsets from MSAccess 2010 applications. Typical VBA code is as follows:

  Public Function getMyPremiums(PolicyID As Long) As String

  On Error GoTo Err_getMyPremiums

  Dim usp As String

  Dim rst As ADODB.Recordset

  Set rst = New ADODB.Recordset

  rst.CursorLocation = adUseServer

  usp = "usp_getMyPremiums " & PolicyID

  Call CheckConnection

  rst.Open usp, gCnn, adOpenForwardOnly, adLockReadOnly, adCmdText

  Debug.Print rst!Premium

  Debug.Print rst!MemberPremium

  Debug.Print "getMyPremiums executed without error"

  Exit_getMyPremiums:

  On Error Resume Next

  rst.Close

  Set rst = Nothing

  Exit Function

  Err_getMyPremiums:

  MsgBox "The application encountered unexpected " & _

  "error # " & Err.Number & " with message string " & _

  Chr(34) & Err.Description & Chr(34) & ".", _

  vbExclamation, "getMyPremiums"

  Resume Exit_getMyPremiums

  End Function

  gCnn above is a connection string to the SQL Server database.

  CheckConnection above verifies that the connection is open.

  Now, here is my problem:

  When I call such a function, and the stored procedure is in a SQL server 2008 database, both versions of the above stored procedure work properly.

  When I call such a function, and the stored procedure is in a SQL server 2012 database, only the second version of the above stored procedure works properly. The first version which relies on a temporary table returns an error # 3704 "Operation is not allowed when the object is closed". This error occurs on the first statement that follows the rst.open statement.

  Can anyone explain why the first version works with SQL Server 2008 but not with SQL Server 2012 when called by the rst.open statement in the VBA code? I set the database compatibility level to SQL Server 2008; but that didn't help.

  As I stated earlier, I have many such complex stored procedures in a large database, and I hate the thought of having to revise them all especially since my client has not yet moved to SQL Server 2012.

  Thanks in advance for any help you can give me.