Creating a temp table in SQL Server 2008 using an Excel Macro (ADODB)

excel, sql, sql-server-2008, vba

Solution

Modify the way how the recodset is opened, move the select from the command to the recodset open method call.

With adoCm
    Set .ActiveConnection = adoCn
    .CommandType = adCmdText
    .CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts"
    .Execute
    .CommandText = "CREATE TABLE #AgedProducts " & _
                   "(Source_Order_Number VARCHAR(255)) " & _
                   "INSERT INTO #AgedProducts VALUES ('AB-123-456') "
    .Execute
End With

Set adoRs = New ADODB.Recordset
With adoRs
    Set .ActiveConnection = adoCn
    .LockType = adLockBatchOptimistic
    .CursorLocation = adUseServer
    .CursorType = adOpenForwardOnly

End With
adoRs.Open "SELECT * FROM #AgedProducts (NOLOCK)"

Problem

After lots of Googling, I've ended up with the following macro that I hoped would connect to a database, drop any existing temp table and then create a new one (populate it, and view the results). ``` Dim adoCn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim adoCm As ADODB.Command Dim strSQL As String Set adoCn = New ADODB.Connection With adoCn .ConnectionString = "Provider=SQLOLEDB;" & _ "Initial_Catalog=XXX;" & _ "Integrated Security=SSPI;" & _ "Persist Security Info=True;" & _ "Data Source=XXX;" & _ "Extended Properties='IMEX=1'" .CursorLocation = adUseServer .Open End With Set adoCm = New ADODB.Command With adoCm Set .ActiveConnection = adoCn .CommandType = adCmdText .CommandText = "IF OBJECT_ID('tempdb..#AgedProducts') IS NOT NULL DROP TABLE #AgedProducts" .Execute .CommandText = "CREATE TABLE #AgedProducts " & _ "(Source_Order_Number VARCHAR(255)) " & _ "INSERT INTO #AgedProducts VALUES ('AB-123-456') " & _ "SELECT * FROM #AgedProducts (NOLOCK) " .Execute End With Set adoRs = New ADODB.Recordset With adoRs Set .ActiveConnection = adoCn .LockType = adLockBatchOptimistic .CursorLocation = adUseServer .CursorType = adOpenForwardOnly .Open "SET NOCOUNT ON" End With adoRs.Open adoCm MsgBox "Recordset returned...", vbOKOnly While Not adoRs.EOF Debug.Print adoRs.Fields(0).Value adoRs.MoveNext Wend adoCn.Close Set adoCn = Nothing Set adoRs = Nothing ``` When I run the query I get the following error message: `Run-time error '-2147217887 (80040e21)':` `The requested properties cannot be supported` The `NOCOUNT` line comes from http://support.microsoft.com/kb/235340 (as does much of the above code). I've added `IMEX=1` to take into account order number might have multiple types in there but I doubt that's where the problem is happening. Any help is greatly appreciated!

Original source