Run-Time Error 3704 with ADO & SQL

[Binoy]

Hey all

I'm running into the run-time error 3704 with a sql statement. It appears that the recordset is closing before I have a chance to copy from it and I can't figure out why. In using the exact same vb code with different sql statements I have no errors, but with this one in particular it fails. The code I'm using is below.

The sql runs without a problem in sql server mgmt studio, and I know my connection settings are correct. Any insights into how to solve this? The error happens with the CopyFromRecordset statement.

[vba]
Set Connection = New ADODB.Connection
Connection.CommandTimeout = 180
Dim ConnectionString As String

ConnectionString = "PROVIDER=SQLOLEDB; DATA SOURCE=sqlprod; "
ConnectionString = ConnectionString & "INITIAL CATALOG= database; INTEGRATED SECURITY = sspi"

Connection.Open ConnectionString
'Populate SQL with the sql used to retrieve the P&L Hedged data
Dim sql As String
sql = ""

sql = "SET NOCOUNT ON "
sql = sql & "DECLARE @CalcID1 INT "
sql = sql & "DECLARE @CalcID2 INT "
sql = sql & " "
sql = sql & "SET @CalcID1 = '" & PreviousCalcID & "' "
sql = sql & "SET @CalcID2 = '" & CurrentCalcID & "' "
sql = sql & " "
sql = sql & "DECLARE @Volumes TABLE( "
sql = sql & "SysDealID VARCHAR(25), "
sql = sql & "Location_Name VARCHAR(50), "
sql = sql & "Point_Date DATETIME, "
sql = sql & "Pr_Volume FLOAT, "
sql = sql & "Cr_Volume FLOAT) "
sql = sql & "INSERT INTO @Volumes "
sql = sql & "SELECT SysDealID, "
sql = sql & "Location_Name, "
sql = sql & "Point_Date, "
sql = sql & "SUM(CASE WHEN Calculation_ID = @CalcID1 THEN Point_Value END) AS Pr_Volume, "
sql = sql & "SUM(CASE WHEN Calculation_ID = @CalcID2 THEN Point_Value END) AS Cr_Volume "
sql = sql & "FROM BookmarkV2.dbo.uv_Deal_Point "
sql = sql & "WHERE Calculation_ID IN (@CalcID1, @CalcID2) "
sql = sql & "AND Measure_ID = 253 "
sql = sql & "AND Component_ID IN (3, 14) "
sql = sql & "GROUP BY SysDealID, Location_Name, Point_Date "
sql = sql & " "
sql = sql & "SELECT SysDealID, "
sql = sql & "RTRIM(LTRIM(LEFT(SysDealID,18))) AS Generic_SysDealID, "
sql = sql & "Location_Name, "
sql = sql & "Point_Date, "
sql = sql & "Pr_Volume, "
sql = sql & "Cr_Volume, "
sql = sql & "Cr_Volume - Pr_Volume AS Volume_Change "
sql = sql & "FROM @Volumes "
sql = sql & "WHERE ABS(Cr_Volume - Pr_Volume) >= 100 "
Sheets("Supplement").Select
Sheets("Supplement").Range("W3").Select
'Execute the SQL populted in the last step
Set adoComm = New ADODB.Command
Set DataSet1 = New ADODB.Recordset

With DataSet1
.ActiveConnection = Connection
.Open sql
Sheets("Supplement").Range("W3").CopyFromRecordset DataSet1
.Close
End With

[/vba]

Appreciate any help which gets offered.

Regards.

[Bob Phillips]

Have you put a stop on the code before the CopyFromRecordset, and looked at DataSet1 in the watch window?