Reports containing a text filter may fail when used with parameterized query against older SQL Server versions resulting in a potential drop table error

Description 

When parameterized query is used against older SQL Server versions, like SQL Server 2008 and earlier, reports containing a text filer may fail. You may receive a report execution error, such as a "drop table error," as shown below: 

Steps to reproduce 

Pre-condtions: 

• The report uses an older version of the SQL Server Warehouse, e.g., SQL Server 2008 or an earlier version.
• The Use parameterized queries option is enabled in the Database Connections dialog. 
  

• The report uses the Implicit Table option under Table Create Type in the VLDB Properties dialog. 
  

The issue can be reproduced with the Strategy Tutorial project and the following steps:

• Create a metric M1 with Cost fact, or use the Cost metric.
• Create a metric M2 with Cost fact and set the level to Zip Code.
  

• Create a report with Zip Code, Customer, M1, and M2.
• Add a filter on Customer Last Name. For example, Last Name is Exactly James.
• Execute the report. It runs as expected.
  

• Partial SQL View:
  

  
  Pass0 - 
  select	a11.CUSTOMER_ID  CUSTOMER_ID,
  	sum(a11.TOT_COST)  WJXBFS1
  into #ZZTPW00005YMD000
  from	CUSTOMER_SLS	a11
  	join	LU_CUSTOMER	a12
  	  on 	(a11.CUSTOMER_ID = a12.CUSTOMER_ID)
  where	a12.CUST_LAST_NAME = 'James'
  group by	a11.CUSTOMER_ID 
  
  Pass1 - 	
  select	a12.ZIPCODE  ZIPCODE,
  	sum(a11.TOT_COST)  WJXBFS1
  into #ZZTPW00005YMD001
  from	CUSTOMER_SLS	a11
  	join	LU_CUSTOMER	a12
  	  on 	(a11.CUSTOMER_ID = a12.CUSTOMER_ID)
  where	a12.CUST_LAST_NAME = 'James'
  group by	a12.ZIPCODE 
  

  
   
  When parameterize query is turned on, the report fails, and you can notice the SQL is different.
  

•  
  Partial SQL View:
  

  
  Pass0 - 
  select	a11.CUSTOMER_ID  CUSTOMER_ID,
  	sum(a11.TOT_COST)  WJXBFS1
  into #ZZTPW00005WMD000
  from	CUSTOMER_SLS	a11
  	join	LU_CUSTOMER	a12
  	  on 	(a11.CUSTOMER_ID = a12.CUSTOMER_ID)
  where	a12.CUST_LAST_NAME = ?
  group by	a11.CUSTOMER_ID 
  with parameters:
  	James
  
  Pass1 - 
  select	a12.ZIPCODE  ZIPCODE,
  	sum(a11.TOT_COST)  WJXBFS1
  into #ZZTPW00005WMD001
  from	CUSTOMER_SLS	a11
  	join	LU_CUSTOMER	a12
  	  on 	(a11.CUSTOMER_ID = a12.CUSTOMER_ID)
  where	a12.CUST_LAST_NAME = ?
  group by	a12.ZIPCODE 
  with parameters:
  	James
  

  
   
  

Why is this happening?

In some older versions of the SQL Server database, you are not allowed to create tables using parameterized queries.
With the SQL Server Warehouse default setting, whenever the Engine needs to create a temp table, it will use the implicit table creation, meaning it uses a syntax like

SELECT … INTO #TEMP_TABLE

WHERE

Solution

You should edit the Table Creation Type VLDB setting to use explicit table. 

• At the report level, click Data > VLDB Properties.
• In the VLDB Properties dialog, expand Tables > Table Creation Type.
  

• Deselect the Use default inherited value - (DBMS level) option.
• Select the Explicit Table option.
• Save and close.

After the VLDB setting is changed to explicit and the

CREATE

create table #ZZTPW00007UMD000(
	CUSTOMER_ID	SMALLINT, 
	WJXBFS1	FLOAT)

Pass1 - 	
insert into #ZZTPW00007UMD000 
select	a11.CUSTOMER_ID  CUSTOMER_ID,
	sum(a11.TOT_COST)  WJXBFS1
from	CUSTOMER_SLS	a11
	join	LU_CUSTOMER	a12
	  on 	(a11.CUSTOMER_ID = a12.CUSTOMER_ID)
where	a12.CUST_LAST_NAME = ?
group by	a11.CUSTOMER_ID
with parameters:
	James