User receives errors creating synonym for SQL script containing a temp table: Invalid object name


Diagnostics

Product: Reporting Server
Release: 8206
Primary OS: All Platforms
Solution Date:

Symptom

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02

(FOC1500)  :  Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name 

(FOC1500)  :  '#dte'. [42000] Statement(s) could not be prepared. [] Deferred prepare could not be completed.

(FOC1405) SQL PREPARE ERROR.

Problem

The user receives the error message above when creating a synonym for a SQL Server script that contains a temporary table.

Solution

To address this issue, use a Common Table Expression (CTE) to define the script's query with temporary tables.



For example:



WITH dim_cte ( DIMMEMBERID) as (SELECT DISTINCT DIMMEMBERID FROM laf_gl_DimMember) SELECT * FROM dim_cte



While creating the synonym, uncheck the SUBQUERY option.