MS SQL query returns results in Microsoft Query but not in Excel 2012

[joeyslaptop]

I have a query that does the following:

Drops Table #Temp if it exists
Creates a new temporary table called #Temp (which I have in other queries that work)
Declares some variables
Sets a loop-count variable (by counting the rows of the Table Schema).

WHILE the @NumberofLoops <= my loop count variable
BEGIN
SET some variable values via a query of the schema table

BEGIN
EXEC ('Select * into #Temp ([column name],[columnName2]) SELECT * FROM ('+ @variables + 'some other stuff)')
END

SET the @NumberOfLoops to @NumberOfLoops +1
END the loop

SELECT * FROM #Temp​

This Executes in SQL Server, and in the Microsoft Query Editor window when I go to edit it. But when I run it via a Refresh in Excel, the status says that the query is running, and then it just ends without returning my results. I can post the whole query but would rather not if I don't have to.

Any ideas on what I'm doing wrong?

 

[joeyslaptop]

I found my answer here: sql server - SQL run from Excel cannot use a temporary table - Stack Overflow

I suspected that it was not able to return the value of the temporary table because it's stored in a temporary database in SQL Server. So - it wouldn't access outside of the database specified by the parameters of the setup wizard.

What I found was that some people will use another method by declaring the @Temp table rather than creating the #Temp table.

Additionally, doing "SET nocount on" and "SET ANSI_WARNINGS OFF" did the trick. I think I could have just used the set ansi_NUlls... whatever. This works, and it's not for a huge complex dataset.