Hello.

Not 100% sure this is right group but here goes.

A lot of my exports from databases tend to end up in excel spreadsheets.
Which means I use the import function from Excel (Microsoft Query) and the
export to excel from Enterprise Manager, a lot.

Thing is lately I've run into a problem where both EM and Microsoft Query
shuts me down. It's when I "have to" (yeah i know...) use a temporary table
to cut down either execution time or actually get useful output.

Using the import data tool in Excel results in the data showing up in
Microsoft Query but it never makes it to the spreadsheet when I return the
data to excel. Enterprise Manager gives me a "invalid object name #tmp"
"error calling GetColumnInfo" message.

Any workarounds or fixes? Missing something obvious? Anyway help is
appreciated.

Truncated query below

Thanks.

/ragnar

Query:
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME
SET @start_date = '2005-01-01'
SET @end_date = '2005-11-01'

CREATE TABLE #tmp ([month] varchar(7),
x_sale int)

WHILE (@start_date < @end_date)
BEGIN
INSERT INTO #tmp
SELECT ...stuff...
FROM ...stuff...
SET @start_date = DATEADD(MONTH, 1, @start_date)
END

SELECT *
FROM #tmp

DROP TABLE #tmp

Hello Ragnar,

Try to create the temporary table as a regular table but in tempdb database.
This is equivalent with creating a temporary table because tempdb is hold in
RAM.
So replace "create table #tmp" with "create table tempdb.dbo.tmp ..." and it
will work. I have tested it using the export wizard from Enterprise Manager.
Of course you will need rights to create tables.

Good luck!

Pavel
www.reporting.ro


"Ragnar" wrote:

Hello Ragnar,

Try to create the temporary table as a regular table but in tempdb database.
This is equivalent with creating a temporary table because tempdb is hold in
RAM.
So replace "create table #tmp" with "create table tempdb.dbo.tmp ..." and it
will work. I have tested it using the export wizard from Enterprise Manager.
Of course you will need rights to create tables.

Good luck!

Pavel
www.reporting.ro


"Ragnar" wrote:

test

"Ragnar" wrote:

Thank you Pavel, I will look into this.

/ragnar

"Pavel" wrote: