小编典典

使用SET FMTONLY的SSRS中没有用于动态SQL存储过程的字段

sql

我有以下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;

我遇到的问题是,当我为SSRS报表添加数据集时,它没有在“字段”部分中提取任何字段/列。我猜这是由于动态SQL引起的吗?

我该如何解决?


阅读 244

收藏
2021-03-10

共1个答案

小编典典


包含Dynamic Sql和Temp表 的问题 存储proc是诸如SSRS和ORM生成器(如Linq2SQL和EF逆向工程工具)之类的向导的祸根。

这是因为在运行PROC之前SET FMTONLY ON;(或最近使用sp_describe_first_result_set)的工具是为了导出PROC生成的结果集架构,以便可以生成ReportViewer
UI的映射。但是,既FMTONLY ON没有sp_describe_first_result执行PROC ,也没有实际执行。

例如,该工具将执行以下操作:

SET FMTONLY ON;
EXEC dbo.MyProc NULL;

一些解决方法:

  • 手动编辑RDL / RDLC文件以插入实际结果集的列名称和类型。
  • 临时删除实际proc并将其替换为一个,它将返回零个或更多行的数据集,其中包含实际proc返回的实际数据类型和列名,并运行向导,然后还原真实proc。
  • 临时添加SET FMTONLY OFF;为PROC的第一行-这将强制执行PROC。完成后还原原始的PROC(尽管请注意,由于该工具传递的null或哑元参数,您的proc可能会失败)。此外,FMTONLY被弃用
  • 在proc的开始处,添加一个伪语句,该语句返回结果集的实际模式,并包装在一个永远不会执行的条件分支中。

这是最近一次黑客攻击的示例:

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;在proc中包含该语句(从上面的代码中即可!)

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