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

joeyslaptop

New Member
Joined
Aug 26, 2011
Messages
24
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

joeyslaptop

New Member
Joined
Aug 26, 2011
Messages
24
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.
 
Last edited:

Forum statistics

Threads
1,144,517
Messages
5,724,817
Members
422,580
Latest member
ScotIain76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top