title: Creating a temp table in SQL Server 2008 using an Excel Macro (ADODB)
body: <p>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).</p>
  
  <pre><code>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;" &amp; _
                          "Initial_Catalog=XXX;" &amp; _
                          "Integrated Security=SSPI;" &amp; _
                          "Persist Security Info=True;" &amp; _
                          "Data Source=XXX;" &amp; _
                          "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 " &amp; _
                     "(Source_Order_Number VARCHAR(255)) " &amp; _
                     "INSERT INTO #AgedProducts VALUES ('AB-123-456') " &amp; _
                     "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
  </code></pre>
  
  <p>When I run the query I get the following error message:</p>
  
  <p><code>Run-time error '-2147217887 (80040e21)':</code></p>
  
  <p><code>The requested properties cannot be supported</code></p>
  
  <p>The <code>NOCOUNT</code> line comes from <a href="http://support.microsoft.com/kb/235340" rel="noreferrer">http://support.microsoft.com/kb/235340</a> (as does much of the above code). I've added <code>IMEX=1</code> to take into account order number might have multiple types in there but I doubt that's where the problem is happening.</p>
  
  <p>Any help is greatly appreciated!</p>