SSRS中动态SQL存储过程没有字段用于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;

我遇到的问题是,当我添加一个SSRS报告的DataSet时,它不会在Fields部分没有字段/列.我猜这是由于动态sql？

我该如何解决？

解决方法

问题
包含动态sql和Temp表的存储过程是像SSRS和ORM生成器(如Linq2sql和EF逆向工程工具)这样的向导.

这是因为工具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,并用一个返回数据集为零或更多行的实际数据类型和列名称,由real proc返回,运行向导,然后还原真正的proc代替.
>添加SET FMTONLY OFF;作为PROC中的第一行 – 这将强制执行PROC(尽管您的proc可能会失败,因为工具传入的空值或虚拟参数).另外,FMTONLY是being deprecated
>在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被虚拟条件所欺骗,并从未执行的分支假定模式.

除此之外,为了您自己的理智,我建议您在PROC中不要使用SELECT *,而是明确列出从Orders返回的所有真正的列名称

最后,请确保您不要包括SET FMTONLY ON;你的proc中的语句(从你的代码上面！)

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

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

解决方法

猜你在找的MsSQL相关文章