21

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.

MattClarke
  • 1,647
  • 1
  • 11
  • 32
  • Why the #temp table at all? – Aaron Bertrand Jan 28 '14 at 22:08
  • Because in the real task I need to run a stored proc several times with different parameters, merge the output from each execution, and return the whole set of results to Excel. So I create a temp table and insert the output from the stored proc into that table after each execution, then return the temp table to Excel. – MattClarke Jan 28 '14 at 22:18
  • I also tried using a table variable as suggested in http://stackoverflow.com/questions/14545432/sql-server-cannot-access-temporary-tables. But I receive the same error in Excel. – MattClarke Jan 28 '14 at 22:19
  • 2
    So why don't you write a stored procedure that does all of that, and call *that* stored procedure once from Excel? The problem is going to be - I suspect - that Excel issues all of these queries individually, so a #temp table or @table variable is never going to live across the scope of those independent statements. – Aaron Bertrand Jan 28 '14 at 22:26
  • 1
    I'm the BI guy servicing various ad hoc requests from business users using the stored procedures already available. I'm not the DB guy with responsibility for writing stored procedures. It is unlikely that this specific need is going to warrant the change management associated with maintaining a new stored proc. – MattClarke Jan 28 '14 at 23:03
  • 1
    From my understanding, Aaron is correct with the reasoning...a temp table won't work here like that. Have you tried unioning your results from the stored procedures in the TSQL statement instead of trying to store individual ones in temp tables? (FYI that report creation structure you have there is horrible, your company has basically turned a stored proc into a data source and wrote reports from it. Heh, you're probably frusterated with the setup) – Twelfth Jan 28 '14 at 23:38
  • How do I union results from a stored procedure? If I have `exec SP @input_variable @output_variable` repeated 5 times with different @input_variables, how do I get all five values from the @output_variable into one table? I have been using a sequence of `exec` and `insert into #t` commands. – MattClarke Jan 28 '14 at 23:57
  • I had the same problem, SET NOCOUNT ON worked for me well – MSBI-Geek Sep 01 '16 at 15:12

2 Answers2

20

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

arjunrc
  • 301
  • 3
  • 5
  • 3
    This answer is correct, I had the same problem as the OP, when I tried to run an SP from Excel, which was supposed to return data from an embedded `SELECT`. It was caused by a *... row(s) affected* message being returned from the server. Apparently, Excel can't deal with this message correctly, and the returned data is ignored. Once I used `SET NOCOUNT ON` in my SP, the data was displayed correctly in Excel. – Treb Sep 09 '14 at 11:07
  • Does this change the ordering of the columns returned seems like its out of order compared to my select statement. – Lightsout Sep 09 '22 at 21:06
14

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.

MattClarke
  • 1,647
  • 1
  • 11
  • 32