SSRS SQL Server 2012: создание отчета с использованием глобальных временных таблиц

ProgramBox

SSRS SQL Server 2012: создание отчета с использованием глобальных временных таблиц

Post author:admin
Запись опубликована:17 февраля, 2023
Post category:Вопросы по программированию

#sql-server #stored-procedures #ssrs-2012

#sql-server #хранимые процедуры #ssrs-2012

Вопрос:

Я создаю отчет SSRS с использованием хранимой процедуры. Хранимая процедура выполняет динамический поворот пару раз. Схема промежуточных таблиц динамична, и поэтому я использую временные таблицы. Поскольку в процедуре используется динамический поворот, я также мало использую динамический SQL; Поэтому я не могу использовать локальную временную таблицу в своем скрипте. Хранимая процедура выполняется нормально; но когда та же хранимая процедура внедряется в конструктор запросов SSRS, выдается сообщение об ошибке, что глобальная временная таблица уже существует.Не говоря уже о том, что в конце я удаляю их все в коде.

пожалуйста, найдите код :

 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


END

1. может быть, отчет выполняется параллельно?

2. Вы имеете в виду, нет нескольких подключений?

3. Я получаю ошибку только в конструкторе запросов, на начальном этапе

4. Почему вы не создаете SP постоянно в DB? Вы получаете ошибку в конструкторе запросов во время ввода запроса, пытались ли вы запустить отчет после ошибки?

5. Я создал SP в DB, и он отлично работает в SSMS, но тот же SP, если я попытаюсь вызвать конструктор запросов, я получаю сообщение об ошибке, касающееся создания временных таблиц.

Ответ №1:

Существует несколько причин, по которым этот код подвержен сбоям.

При использовании глобальных временных таблиц с одинаковыми именами эта процедура не может выполняться параллельно (не может иметь перекрывающихся исполнений). На второй итерации будет предпринята попытка создать таблицы, которые уже существуют.
Размещение инструкций DROP TABLE в конце без обработки ошибок означает, что если эта процедура по какой-либо причине завершится неудачно, инструкции DROP TABLE не будут выполняться все, оставляя временные таблицы на пути следующего выполнения. Это то, что вызывает ошибку, которую вы видите.

Что вы могли бы сделать, чтобы исправить это (поскольку вы уже используете динамический SQL), так это динамическое присвоение таблицам имен с помощью одного суффикса, определенного в начале процедуры, например:

 DECLARE @TableSuffix NVARCHAR(10))
SET @TableSuffix = CAST(ABS(CHECKSUM(NewId())) % 9999999999 AS NVARCHAR(10));

А затем для каждой таблицы укажите суффикс:

 SET @DynamicPivotQuery = 
N'select * into ##Final'   @TableSuffix   
N' from 
    (SELECT ID, FLOODZONE, New_Version'  '
     FROM ##Temp'   @TableSuffix   N')SRC
     PIVOT(MAX(FLOODZONE) 
     FOR New_vERSION IN ('   @ColumnName   ')) AS PVTTable'

Вам нужно будет сделать создание каждой глобальной временной таблицы динамическим. SSRS должны быть в порядке с динамическим SQL, если вы не открываете процедуру в конструкторе запросов. Определенно избегайте использования DDL процедуры в качестве встроенного SQL отчета.

Вопрос:

Комментарии:

Ответ №1:

Вам также может понравиться

Apache Airflow запускает RBAC в LDAP

Как определить имя процесса, который вызвал срабатывание триггера

Javascript List reducer для CouchDB