KB46840: Intermediate tables are not created as temporary tables in SQL Server 2008

• Strategy

SYMPTOM:

When using SQL Server 2008, intermediate tables are not created as temporary tables. In SQL Server 2008 temporary tables will typically be created with a hashtag/pound (#) sign in front of the table name as in the example below. The hashtag signals to the database that the table is created as a temporary table.


select a11.EMP_ID EMP_ID,
sum(a11.TOT_DOLLAR_SALES) Revenue,
sum(a11.TOT_COST) WJXBFS1,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) WJXBFS2,
rank () over ( order by sum(a11.TOT_DOLLAR_SALES) desc) WJXBFS3
into #ZZMD00

However, instead of seeing this desired syntax, the intermediate table is not created as a temporary table. There is no hashtag in front of the intermediate table name as in the example below:


create table ZZMD00;
WITH (DISTRIBUTION = HASH(EMP_ID))as
select a11.EMP_ID  EMP_ID,
sum(a11.TOT_DOLLAR_SALES)  Revenue,
sum(a11.TOT_COST)  WJXBFS1,
sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST))  WJXBFS2,
rank () over ( order by sum(a11.TOT_DOLLAR_SALES) desc)  WJXBFS3

CAUSE:

This issue is known to occur if the incorrect database connection type is selected in the Database Instance.

ACTION:

Follow the steps below to resolve this issue:

Browse to the relevant Database Instance under Administration -> Configuration Managers -> Databases Instances and open the Database Instance editor.
Select Microsoft SQL Server 2008 as the Database connection type

Intermediate tables will now be created with a hashtag in front of the table name as desired.

Comment

0 comments

Details

Knowledge Article

Published:

May 18, 2017

Last Updated:

May 18, 2017