Retrieve column names and column data from MS-SQL Stored Procedure

[ino_mart]

All

I use Access 2007 in combination with "MS SQL 2005".
On the SQL-server I imported the UberCrossTab-script in order to create dynamic Pivot tables.

When I launch the SQL-string on the SLQ-server, I do get the expected result in the "Results"-window.

I now need to use VBA-code in order to retrieve the data and column names in an ADODB.RECORDSET

Currently, I use next code. This does not give errors, so assume the data is somewhere in the recordset CN. But how can I read the table header names and the data.

Private Sub Command110_Click()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Set cn = New ADODB.Connection
With cn
    .Provider = "sqloledb"
    .Properties("Data Source").value = strODBCServer
    .Properties("Initial Catalog").value = strODBCdatabase
    .Properties("Integrated Security").value = "SSPI"
    .Open
End With
strSQL = "EXECUTE UberCrosstab @pivotRowFields = 'Location, Type' ,@pivotField = 'Date',@pivotTable = 'qryVMActualsClusterInfo',@aggField = 'Capacity',@aggFunc = 'SUM'"
cn.Execute strSQL
End Sub

 

[mdlueck]

This does not give errors, so assume the data is somewhere in the recordset CN. But how can I read the table header names and the data.

I would suggested using the debugger / stepped execution, put a watch on the... oh... you do not have an adoRS (Record Set) object.

That is interesting syntax to execute a SP. Here is the syntax I use to operate a SP which returns a RecordSet object:

Using VBA ADO objects to execute a Stored Procedure
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120


Specifically I discovered an adoRS object being returned by .Execute(), so captured that into a VBA variable and that recordset has the results from the SP.

Hopefully the answer will be similar in your case.

 

[ino_mart]

I have now changed the code, but on the WHILE-clause I get: "Run-time error 3704: Operation is not allowed when the object is closed."

Private Sub Command110_Click()
Dim adoCMD As adodb.Command
Dim adoRS As adodb.Recordset
Set adoCMD = New adodb.Command
With adoCMD
    .ActiveConnection = "Provider='sqloledb';Data Source='" & strODBCServer & "';" & _
        "Initial Catalog='" & strodbcdatabase & "';Integrated Security='SSPI';"
    .CommandText = "ubercrosstab"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@pivotRowFields").value = "Location, Type"
    .Parameters("@pivotField").value = "Date"
    .Parameters("@pivotTable").value = "qryVMActualsClusterInfo"
    .Parameters("@aggField").value = "Capacity"
    .Parameters("@aggFunc").value = "SUM"
    Set adoRS = .Execute()
End With
 
While adoRS.EOF = False And adoRS.BOF = False
    Debug.Print adoRS!Location
    adoRS.MoveNext
Wend