SQL Run from Excel Cannot Use a Temporary Table

SQL run from Excel cannot use a temporary table

The following appears to work ...

set nocount on
declare @t table(fid int)  -- I'm sure I could add the rest of the columns if I wanted to
insert @t select freq_id from compass3.dbo.freq
select * from @t where fid>2

So as long as I turn nocount on and use a table variable rather than a temporary table, I can achieve what I need.

Excel - SQL Query - ## Temp Table

Read up on global temp tables(GTT). They persist as long as there is a session referencing it. In SSMS, if you close the session that created the GTT prior to using it in another session, the GTT would be discarded. This is what is happening in Excel. Excel creates a connection, executes and disconnects. Since there are no sessions using the GTT when Excel disconnects, the GTT is discarded.

I would highly recommend you create a normal table rather than use a GTT. Because of their temporary nature and dependence on an active session, you may get inconsistent results when using a GTT. If you create a normal table instead, you can be certain it will still exist when you try to use it later.

The code to create/clean the table is pretty simple.

IF OBJECT_ID('db.schema.tablename') IS NOT NULL
    TRUNCATE TABLE [tablename]
ELSE
    CREATE [tablename]...
GO

You can change the truncate to a delete to clean up a specific set of data and place it at the start of each one of your queries.

SQL Server : cannot access temporary tables

The server does not allow modification of any table in a function. Use a table variable instead.

declare @temp table (RowNum int, JOBDateTime DateTime, JOBEvent int)

insert into @temp
  SELECT ROW_NUMBER() OVER(ORDER BY JOBID) AS ROWNUM, 
         JOBDateTime,
         JOBEvent  
    FROM JOBsActivityData 
   where JOBID = @USERID and JOBDateTime >= @JOBStartDATE
...

when using table variables, you do not need to drop them.

Get Excel sheet into temp table using a script

There are 5 possible causes for this error.

1. The jet engine must be installed on the server. Installing MS Office on the server sorts that out.
2. The path to the xls is relative to the server, not the workstation you're running the command from
3. The account that runs the SQL server service must have write access to the folder where the xls is. One possible solution is to changing the temp= and tmp= environment variables of the service startup account (eg. Administrator) to (for example) c:\temp, then enable Full Control on c:\temp to Everyone

4...

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

5....

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 0 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 0
GO 

Now I don't know why this works, especially considering that everyone else says they should be set to 1. For me however, setting them to zero, did the trick for the following SQL statement on SQL Server 2008R2 32bit and M$ Office 2007

Select * 
into [temp_table$]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\EXPENDITURE REPORT.xls;HDR=YES;IMEX=1',
'SELECT * FROM [EXPENDITURE SHEET$]')

Note: I purposely have used an example in which both the filename and the worksheet name have spaces to show that this can be done.

How do I send data from Excel to SQL temp tables for processing using VBA?

I think it is because you are passing in strSQL, a String data type, as the first parameter of the .Open method, but the Open method requires a Command object (according to MSDN).

What you'll want to do is declare an ADODB.Command object and pass that through. I've modified your code to do this:

Option Explicit

Sub ado_test()

    Dim adoConnection As ADODB.Connection
    Dim adoRecordset As ADODB.Recordset
    Dim adoCommand As ADODB.Command
    Dim connectString As String
    Dim strSQL As String
    Dim sPath As String

    Worksheets("Sheet1").Select
    sPath = ThisWorkbook.FullName

    '-Create a new ADO connection --'
    Set adoConnection = New ADODB.Connection
    '-Create a new ADO recordset --'
    Set adoRecordset = New ADODB.Recordset

    '-Create a new ADO command --'
    Set adoCommand = New ADODB.Command

    '-Build our connection string to use when we open the connection --'

    connectString = "DRIVER=SQL Server;SERVER=MyServer;Trusted_Connection=yes;DATABASE=testDB"
    adoConnection.ConnectionTimeout = 20
    adoConnection.CommandTimeout = 20

    adoConnection.Open connectString
    strSQL = "sp_test_import"

    With adoCommand
        .ActiveConnection = adoConnection
        .CommandText = strSQL
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        .Parameters(1).Value = sPath
    End With

    Set adoRecordset = adoCommand.Execute

    If adoRecordset.EOF = False Then ThisWorkbook.Worksheets("YourSheetName").Cells(1, 1).CopyFromRecordset adoRecordset
    '--adoRecordset.Open adoCommand, adoConnection'

    '--Close the database connection'
    adoConnection.Close

End Sub

More information on the Command object.

I also added how I get values from SQL Server into the Excel workbook using the CopyFromRecordset method.

Use ADO recordset and connection to update same #TEMP table

I think the problem is that you are not closing the recordset before you issue the delete statement. i also changed the locktype, but that is probably a secondary issue. this works for me:

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer

Const connString As String = "Provider=sqloledb;Data Source=yourserver;Initial Catalog=yourdb;Integrated Security=SSPI;"

'Open Connection.
cn.Open connString

'Create temp table.
cn.Execute "CREATE TABLE #TEMP (COL1 INT)"

'Open rst.
With rst
    .ActiveConnection = cn
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
End With

'Add records to temp table using rst.
rst.Open "#TEMP"
 For i = 1 To 5
    rst.AddNew
    rst!COL1 = i
    rst.Update
 Next
rst.Close

'Delete records using connection.
cn.Execute "DELETE FROM #TEMP WHERE COL1 IN (2,4)"

'Display
rst.Open "#TEMP"
While Not rst.EOF
    Debug.Print rst!COL1
    rst.MoveNext
Wend
rst.Close

---

Related Topics