SQL run from Excel cannot use a temporary table

Question

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 1

I wanted to add to the above answer - just using SET NOCOUNT ON at the top of the query, with a regular temp table SELECT name INTO #Names FROM Employee should work.

A table variable is not needed here.

You could also add SET ANSI_WARNINGS OFF to avoid messages like "NULL Value is eliminated by an aggregate".

Answer 2

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.