SQL run from Excel cannot use a temporary table
excel, sql, sql-server
Solution
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.
Problem
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.