Нет полей для динамической хранимой процедуры SQL в SSRS с SET FMTONLY

У меня есть следующий SP, который работает правильно, когда запускался сам по себе:

USE [Orders]
GO
SET FMTONLY OFF; 

CREATE PROCEDURE [dbo].[Get_Details_by_Type]

@isArchived varchar(10),
@Type varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @sqlQuery nvarchar(max)
          IF(@isArchived = 'ALL')
            BEGIN
                set @sqlQuery  = 'SELECT *  FROM [dbo].[Orders] 
              WHERE ' + @Type + ' != € 
                ORDER BY [IDNumber]'
                exec sp_executesql @sqlQuery
            END
        ELSE
            BEGIN
            set @sqlQuery  = 'SELECT * FROM [dbo].[Orders] 
          WHERE ' + @Type + ' != € AND [isArchived] = ' + @isArchived + ' ORDER BY [IDNumber]'
            exec sp_executesql @sqlQuery
        END
END
SET FMTONLY ON;

Проблема, с которой я столкнулась, заключается в том, что когда я добавляю DataSet для отчета SSRS, он не извлекает никаких полей/столбцов в разделе "Поля". Я предполагаю, что это связано с динамическим SQL?

Как я могу это решить?

Ответ 1

Проблема
Хранимые процедуры, которые содержат таблицы Dynamic Sql и Temp, являются бичем мастеров, таких как генераторы SSRS и ORM, таких как инструменты обратного инжиниринга Linq2SQL и EF.

Это связано с тем, что инструменты SET FMTONLY ON; (или совсем недавно sp_describe_first_result_set) перед запуском PROC, чтобы получить схему набора результатов, созданную PROC, чтобы можно было создавать сопоставления для пользовательского интерфейса ReportViewer., Однако ни FMTONLY ON, ни sp_describe_first_result фактически не выполняют PROC.

например инструмент будет делать что-то вроде:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

Некоторые обходные пути:

Редактирование файла RDL/RDLC вручную для вставки фактических имен и типов столбцов набора результатов.
Временно отбрасывая реальный процесс и заменяя его на тот, который возвращает набор данных из нуля или более строк с фактическими типами данных и именами столбцов, возвращенными реальным процессом, запустив мастер, а затем возвращая реальный процесс.
Временное добавление SET FMTONLY OFF; в качестве первой строки в PROC - это заставит выполнение PROC. Верните исходный PROC, как только это будет сделано (хотя учтите, что ваш Proc может потерпеть неудачу из-за пустых или фиктивных параметров, переданных инструментом). Кроме того, FMTONLY устарела
В начале процедуры добавляется фиктивный оператор, который возвращает фактическую схему результирующего набора (ов), обернутую в условную ветвь, которая никогда не выполняется.

Вот пример последнего взлома:

CREATE PROCEDURE [dbo].[Get_Details_by_Type]
  @isArchived varchar(10),
  @Type varchar(50)
AS
BEGIN
   -- For FMTONLY ON tools only
   IF 1 = 2
     BEGIN
       -- These are the actual column names and types returned by the real proc
       SELECT CAST('' AS NVARCHAR(20)) AS Col1, 
              CAST(0 AS DECIMAL(5,3)) AS Col2, ...
     END;
-- Rest of the actual PROC goes here

FMTONLY ON/sp_describe_first_result_set одурачены фиктивным условным условием и принимают схему из никогда не выполняемой ветки.

Кроме того, для вашего же здравого смысла, я бы предложил, чтобы вы не указали SELECT * в вашем PROC - достаточно явно перечислить все реальные имена столбцов, возвращенные из Orders

Наконец, просто убедитесь, что вы не включили оператор SET FMTONLY ON; в свой процесс (из кода выше!)

END - Proc
GO **
SET FMTONLY ON; ** This isn't part of the Proc!

Ответ 2

Если кто-то все еще сталкивается с этой проблемой, я решил, что похоже на аналогичную проблему с ssrs и dynamic sql.

Чтобы ssrs правильно отображали поля из SP,
выберите опцию "Текст" в типе запроса,
введите имя и параметры SP, как вы его вызываете, и окно SSMS. sp_YourStoredProc @[email protected]
Нажмите кнопку "Обновить поля".
Когда оператор запускается, поля будут обновляться, и ваша матрица будет заполнена.

Кстати, я использую SQL 2012

Надеюсь, что это поможет.

Ответ 3

Ниже приводится то, что я сделал для преодоления проблемы. Поля не указаны в SSRS

Первоначально у меня хранилась процедура; и он возвращает данные, когда я выполняю набор данных. Но поля не указаны в SSRS
Я скопировал текст хранимой процедуры и сделал набор данных как Text вместо Stored Procedure. См. Как обновить поля для набора данных
Это вызвало ошибки, и я "проигнорировал" эти ошибки. См. Конструкция или оператор SQL объявления Declare не поддерживается.
Теперь я проверил, что поля заполнены для SSRS
Теперь я обновил набор данных, чтобы использовать Stored Procedure

После выполнения вышеуказанных шагов выполните обновление набора данных, как показано ниже:

Ответ 4

Следуйте приведенным ниже шагам.

•   Delete DataSource. Create a new Data Source .
•   Delete DataSet. Create a new DataSet .
•   Use Query Designer. 
•   Add valid parameter when asked  .
•   One should get result for provided prarameters . 
    Donot click on refresh Field .
•   Then Report parameters and Report field will appear .
•   Now Filter criteria should work.

ИЛИ

**Add the Report field manually . It works.**

Если хранимая процедура не может получить данные схемы или метаданные, мы должны вручную указать поля отчета.

Ответ 5

Это очень позднее дополнение, но для тех из нас, кто знаком с дизайном SSRS/Report: Не забудьте проверить порядок параметров на панели "Данные отчета" в Visual Studio. Они будут созданы сверху вниз, поэтому параметр сверху не может зависеть от параметра внизу. Это была проблема, с которой я столкнулся с одним сообщением, но сообщение об ошибке, которое я получил, не заявляло об этом, поэтому я несколько часов преследовал хвост, пытаясь его разрешить.

Ответ 6

Не означает оживить мертвую нить, но с ума сошел, когда обновлял наши отчеты от SSRS 2005 до SSRS 2016, где хранимые процедуры использовали openquery.

Мы сузили его до отчетов с полями с пустыми значениями. Поэтому мы добавили это в начале хранимой процедуры:

SET CONCAT_NULL_YIELDS_NULL OFF;

что означало, что нам не нужно было заполнять каждое поле.