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

Question

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!

gembird · Accepted Answer · 2013-02-18 16:17:35Z

6

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)"

answered Feb 18, 2013 at 16:17

gembird

14.1k5 gold badges39 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kali_89 Over a year ago

Thanks for this - works a treat. Do you know why this works instead of my first attempt?

gembird Over a year ago

Recodset needs to know what should be in it, so you have to provide SQL code. Have a look e.g. here support.microsoft.com/kb/168336. There are different methods how to open the recodset. Have a nice day.

Dan Bracuk · Accepted Answer · 2013-02-18 15:40:14Z

0

My understanding of temp tables is that they are only available to the connection that created them. That being the case, attempting to drop one from another connection is unwise.

The error message does not state which line of code caused it. That being the case, I suggest that you test your code piecemeal. Start by creating the connection. Then open and close it. Then start doing things while the connection is open, but one thing at a time.

answered Feb 18, 2013 at 15:40

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

1 Comment

Robino Over a year ago

In SQL Server ##temp_table is a global temp table. By attempting to drop the table at the start of code execution ensures (race-conditions aside) that the code doesn't bawk when trying to create a table that already exists. It's a best practice to drop the (probably non-existent) table before creating it.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related