MattClarke MattClarke - 1 year ago 113
SQL Question

SQL run from Excel cannot use a temporary table

I have been able to create a data connection from Excel to SQL Server and execute many SQL queries successfully. But I cannot get any TSQL to work if it includes a temporary table. For example:

select * into #t from compass3.dbo.freq
select * from #t where freq_id>2

(Clearly there is no need to use #t in this case: I'm just giving the most simple example.) This work fine in SSMS but when executed via Excel I get the error message "We couldn't refresh the connection 'audbbicube'. The table 'ion Query1' may not exist."

In some other SO posts people suggested adding
set nocount on
, but that made no difference in this case.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download