0

I am creating a SSRS report using a Stored procedure. The stored procedure does dynamic pivoting couple of times. The schema of the intermediate tables are dynamic and thus I'm using temporary tables. Since the procedure uses dynamic pivoting, I' using little of dynamic SQL as well; Therefore can't use local temporary table in my script. The stored procedure is running fine; but when the same stored procedure is embedded inside a SSRS query designer, it gives the error that the global temporary table already exists.Not to mention, I'm dropping all of them in the end in the code.

please find the code :

USE [IGD_HISTORY_COMBINED]
GO
/****** Object:  StoredProcedure [dbo].[USP_GETIGDDETAILS]    Script Date: 03-10-2016 12:05:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_GETIGDDETAILS]  @ID CHAR(17) ,@YEAR INT=null
AS 
BEGIN 




--declare  @ID CHAR(17)
--set @id='1208700418b8b9c44'



SELECT id, floodzone, FloodzoneLastUpdatedDate, versionid INTO ##HIST  
FROM IGD_HISTORY_MONROE
WHERE ID=@ID and floodzone is not null and RecordStatus='U'  
--and  VersionId BETWEEN 587 AND 748
ORDER BY VERSIONID desc

Select *, 'VERSION_'+ cast( ROW_NUMBER () over (order by VERSIONID) AS varchar)
as New_VERSION into ##temp from ##HIST ORDER BY VERSIONID ASC


--Dynamic Pivoting in order to put the Id and versionid values relational

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
+ QUOTENAME(New_VERSION)
FROM (SELECT DISTINCT New_VERSION FROM ##temp) AS Courses



--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'select * into ##Final from (SELECT ID, FLOODZONE, New_Version' +'
FROM ##Temp)SRC
PIVOT(MAX(FLOODZONE) 
FOR New_vERSION IN (' + @ColumnName + ')) AS PVTTable'


EXEC (@DynamicPivotQuery)


select a.*,b.ParcelIDActual, b.PropertyLocationStreet1,b.PropertyLocationStreet2,
b.PropertyCity,b.PropertyZip, B.CountyName
INTO ##CE
from ##Final a
join IGD_DEV_2012..igd b 
on a.id=b.id;

select * from ##CE;


WITH CTE_New as  (
select 
case when right (C.SourceFileName,8) like '[0-9]%'
Then C.SourceFileName 
When right (C.SourceFileName,8) not like '[0-9]%'
Then c.SourceFileName + '_'+ replace(cast(cast(C.CompletedTime as date) as varchar),'-','')
end as  SourceFileName,
 c.SourceVersion,
'Version_'+SourceVersion as Version1
 from
(select distinct versionid from ##temp) D
join igd_datasourcestatus C 
on d.versionid=c.SourceVersion
)
select VERSION1 +': '+upper(SourceFileName) as Version_information  into ##version_info from CTE_New;





DECLARE @DynamicPivotQuery1 AS NVARCHAR(MAX)
DECLARE @ColumnName1 AS NVARCHAR(MAX);


SELECT Version_information , 'VERSION'+ CAST(ROW_NUMBER () OVER ( ORDER BY Version_information) AS varchar)  vERSIONS
INTO ##TE
FROM ##version_info


--Get distinct values of the PIVOT Column 
SELECT @ColumnName1= ISNULL(@ColumnName1 + ',','') 
+ QUOTENAME(VERSIONS)
FROM (SELECT DISTINCT VERSIONS FROM ##TE) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery1 = 
N'select * into ##Final1 from (SELECT   Version_information, versions' +'
FROM ##TE)SRC
PIVOT(MAX(version_information) 
FOR versions IN (' + @ColumnName1 + ')) AS PVTTable'


EXEC sp_executesql @DynamicPivotQuery1

truncate table new1


SELECT A.*,B.* 
into ##TEMPO
FROM ##CE A 
JOIN ##FINAL1 B
ON A.ID = @ID 


 if exists (select  max(versions) from ##te having max(versions)='version7')
 begin 
 insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]
      ,[version7]
      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
      ,[version_7])
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]
      ,[version7]
      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
      ,[version_7]
      from ##TEMPO 
end

 if exists (select  max(versions) from ##te having max(versions)='version6')
 Begin 
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]
     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]
      ,[version6]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
      ,[version_6]

      from ##TEMPO 
end

if exists (select max(versions) from ##te having max(versions)='version5')
 Begin 
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]
     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]
      ,[version5]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]
      ,[Version_5]

      from ##TEMPO 
end

if exists (select  max(versions) from ##te having max(versions)='version4')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]
      ,[version4]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]
      ,[VERSION_4]

     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3],
       [version4]
  ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3],
       [version_4]
  from ##TEMPO 

end


if exists (select  max(versions) from ##te having max(versions)='version3')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]
      ,[version3]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]


     )
 select  [id]
      ,[version1]
      ,[version2]
      ,[version3]

      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]
      ,[VERSION_3]

     from ##TEMPO 

end

if exists (select  max(versions) from ##te having max(versions)='version2')
Begin
  insert new1 ( [id]
      ,[version1]
      ,[version2]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]



     )
 select  [id]
      ,[version1]
      ,[version2]


      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]
      ,[VERSION_2]


     from ##TEMPO 

end


if exists (select  max(versions) from ##te having max(versions)='version1')
Begin
  insert new1 ( [id]
      ,[version1]



      ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]




     )
 select  [id]
      ,[version1]
       ,[ParcelIDActual]
      ,[PropertyLocationStreet1]
      ,[PropertyLocationStreet2]
      ,[PropertyCity]
      ,[PropertyZip]
      ,[CountyName]
      ,[VERSION_1]


     from ##TEMPO 

end









Drop table ##TEMPO
drop table ##Final
DROP TABLE ##CE
DROP TABLE ##HIST
DROP TABLE ##TE
DROP TABLE ##temp
DROP TABLE ##version_info
DROP TABLE ##Final1



select * from NEW1
3
  • You don't have them created elsewhere (try dropping them all individually separately from this report on that database) there's a chance you might have left one in existence, also the error that is coming back, what does it actually say (it should say table x already exists).
    – Ste Bov
    Oct 3 '16 at 10:36
  • Yes, they are not created elsewhere.. and now I have added a if condition in the beginning to check for a existing temp table. But now , for the global temporary tables that are getting created in Dynamic SQL query block, it is throwing an error:TITLE: Microsoft SQL Server Report Designer An error occurred while the query design method was being saved. Invalid object name '##Final'. Oct 3 '16 at 10:39
  • Try actually creating the tables separately then inserting into them instead of creating them with the select column into #new, (do it for your first dynamic query EXEC (@DynamicPivotQuery) to see if it will get past that first
    – Ste Bov
    Oct 3 '16 at 10:54
1

I have been there trust me, to resolve this issue try modifying accordingly and then add this code in applicable places in your Stored Procedures:

BEGIN 
IF(1=0) Begin
SET FMTONLY OFF
END
EXEC(@YourDynamicSqlhere)
Select * from ##YourTempTable
drop table ##YourTempTable
END
1
  • This is likely the cause of your problems with the query designer. If you run SQL Profiler, you will see that the query designer calls your stored proc to get the result set, but does a SET FMTONLY ON before it, which causes it to take every branch of every IF statement, which can confuse things greatly. Sometimes it's easier to just temporarily put a SET FMTONLY OFF at the beginning and get your columns into the designer, then remove it. Or just leave it there, I don't know that it harms anything.
    – mannaggia
    Jul 23 '17 at 21:10
0

if you're happy to just drop it you can run this:

IF object_id('tempdb..##table') IS NOT NULL
    DROP table ##table

This will, if the table exists drop it, if it doesn't exist wont so you don't get the error.

I'd also do your table declarations outside of the 'into' statements and run it as INSERT INTO ##table SELECT ... I had some issues around inserting temp tables a while ago, both methods help different aspects

2
  • Doing this was creating another problem for me which was when more than 1 person was loading the same report almost simultaneously with a very small difference of time the check was dropping global temp table for one user due to this the other side user was getting partial results,now i know that #temp or local temp tables gets appended with a unique id for each user even if they are creating the tables with same name but i am not sure if same is the case with ##temp or global temp tables check this out : stackoverflow.com/questions/4725812/… Aug 26 '17 at 12:06
  • Here is a much better explanation of the situation with example : mssqltips.com/sqlservertip/4031/… Aug 26 '17 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.