Using SQL & Temp Tables in Excel Pivot Tables connected to SQL Server database

[TimatLA]

I'm having some problems running some SQL from an Excel Data Connection (Excel 2007) which connects to Microsoft SQL Server 2008. I set up the connection using Excel's data connection wizard.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Basically I'm trying to connect some excel pivots to a database to run some SQL queries which use temp tables.
<o> </o>
For some reason I get the following error and I can't work out what the problem is.
<o> </o>
"The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."
<o> </o>
<o> </o>
Perhaps it's some kind of permission problem or is there something else I need in my connection string?
<o> </o>
Perhaps the problem occurs because my login account does not have full access to the temporary folder of the SQL Server. Otherwise could it be Excel is not configured to run distributed queries?
<o> </o>
Below is a simple script I'm using to test the process and they run fine in Microsoft Server Management Studio
<o> </o>
Select top(10)*<o></o>
into ##Test<o></o>
from Atlasrpt.dbo.GMGRA_GRANT<o></o>
select * from ##Test
<o> </o>
Other code I'm trying is:
<o> </o>
Select top(10)*<o></o>
into UsrTemp.dbo.Test<o></o>
from Atlasrpt.dbo.GMGRA_GRANT<o></o>
select * from UsrTemp.dbo.Test<o></o>
drop table UsrTemp.dbo.Test<o></o>
<o> </o>
If I use the above second SQL code it will create the table and write records to the UsrTemp database but it still gives me the error below and won't read the records back into my Pivot Table.
<o> </o>
Below is my connection String:
<o> </o>
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ATLASRPT;Data Source=lac-rptsqln-08;Extended Properties="Retainsameconnection = true;delayvalidation = true";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=CENTDAUD82605XY;Use Encryption for Data=False;Tag with column collation when possible=False<o></o>
<o> </o>
Any help would be greatly appreciated!

 

[Colin Legg]

This is a guess but, based on my experiences of using VBA/ADO to create and add data to SQL temp tables, try adding this line to the start of your query:

SET NOCOUNT ON

 

[sirguise]

Typically if it's an issue within the script, there will be a little more detail from the error. Of course you can log into the SQL server using the same user you are for your connection and make sure that the script will run. I'm betting all that works, which means it's your connection string, not the script.

Another question is, why the temp tables? I'm guessing your example isn't necessarily representative of your end result. But I have to ask the question.

Personally, if I'm running script to excel where I need a temp table, I put the SQL script in a stored procedure and then simply call the sproc from the excel workbook, something like Exec usp_BlahBlah.

As long as the user id that your using has access to read, they typically have access to the tempdb, but it's a good place to start looking. I don't think the no count is an issue, but I've seen weirder.

-Dave

 

[TimatLA]

Thanks Colin...it works!!!

Cheers Dave, I'm going to give the stored procedure a go to.

 

[Colin Legg]

Cool, glad it worked! Sprocs offer plenty of advantages if the database owner lets you have them, but it's worth noting that you will still need to include SET NOCOUNT ON in this particular sproc if you want to call it from Excel.