使用 SET FMTONLY 的 SSRS 中没有动态 SQL 存储过程的字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21434827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
No fields for dynamic SQL stored procedure in SSRS with SET FMTONLY
提问by sd_dracula
I have the following SP which works correctly when ran on its own:
我有以下 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;
The problem I'm having is that when I add a DataSet for a SSRS report, it pulls no fields/columns in the Fields section. I'm guessing it's due to the dynamic SQL?
我遇到的问题是，当我为 SSRS 报告添加数据集时，它在“字段”部分中没有提取任何字段/列。我猜这是由于动态 SQL？
How can I resolve that?
我该如何解决？
回答by StuartLC
The Problem
Stored procs which contain Dynamic Sql and Temp tables are the bane of wizards like SSRS and ORM generators like Linq2SQL and EF reverse engineering tools.
包含动态 Sql 和临时表的问题存储过程是诸如 SSRS 和 ORM 生成器（如 Linq2SQL 和 EF 逆向工程工具）之类的向导的祸根。
This is because the tools SET FMTONLY ON;(or more recently, sp_describe_first_result_set) prior to running the PROC, in order to derive the resultset schema produced by the PROC so that mappings for the ReportViewer UI can be generated. However, neither FMTONLY ONnor sp_describe_first_resultactually execute the PROC.
这是因为在运行 PROC 之前的工具SET FMTONLY ON;（或最近的sp_describe_first_result_set）是为了派生由 PROC 生成的结果集架构，以便可以生成 ReportViewer UI 的映射。但是，既不执行FMTONLY ON也不sp_describe_first_result实际执行 PROC。
e.g. the tool will do something like:
例如，该工具将执行以下操作：
SET FMTONLY ON;
EXEC dbo.MyProc NULL;
Some Workarounds:
一些解决方法：
- Manually editing the RDL / RDLC file to insert the actual result set column names and types.
- Temporarily dropping the real proc and substituting it with one which returns a data set of zero or more rows with the actual data types and column names returned by the real proc, running the wizard, and then reverting the real proc.
- Temporarily adding
SET FMTONLY OFF;as the first line in the PROC - this will force execution of the PROC. Revert the original PROC once done (although note your proc may fail because of null or dummy parameters passed in by the tool). Also,FMTONLYis being deprecated - At the start of the proc, adding a dummy statement which returns the actual schema(s) of the result set(s), wrapped in a conditional branch which never gets executed.
- 手动编辑 RDL/RDLC 文件以插入实际结果集列名称和类型。
- 暂时删除真实的 proc 并将其替换为一个返回零行或多行数据集的数据集，其中包含真实的 proc 返回的实际数据类型和列名，运行向导，然后恢复真实的 proc。
- 临时添加
SET FMTONLY OFF;为 PROC 中的第一行 - 这将强制执行 PROC。完成后恢复原始 PROC（但请注意，您的 proc 可能会因为工具传入的空参数或虚拟参数而失败）。此外，FMTONLY正在被弃用 - 在 proc 开始时，添加一个虚拟语句，该语句返回结果集的实际模式，包装在永远不会执行的条件分支中。
Here's an example of the last hack:
这是最后一次黑客攻击的示例：
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_setare fooled by the dummy conditional and assumes the schema from the never-executed branch.
FMTONLY ON/sp_describe_first_result_set被虚拟条件愚弄，并从从未执行过的分支中假设架构。
As an aside, for your own sanity, I would suggest that you don't SELECT *in your PROC - rather explicitly list all the real column names returned from Orders
顺便说一句，为了您自己的理智，我建议您不要SELECT *在 PROC 中 - 而是明确列出从Orders
Finally, just make sure you don't include the SET FMTONLY ON;statement in your proc (from your code above!)
最后，请确保您没有SET FMTONLY ON;在 proc 中包含该语句（来自上面的代码！）
END - Proc
GO **
SET FMTONLY ON; ** This isn't part of the Proc!
回答by ChadB
If anyone is still facing this issue, I solved what appears to be a similar problem with ssrs and dynamic sql.
如果有人仍然面临这个问题，我用 ssrs 和动态 sql 解决了一个类似的问题。
- For ssrs to properly map the fields from the SP,
- select the "Text" option under query type,
- enter the SP name and parameters like you are calling it from and SSMS window.
sp_YourStoredProc @Parameter1....@ParameterN - Click on the refresh fields button.
- When the statement runs, the fields will refresh, and your matrix will be populated.
- 为了使 ssrs 正确映射来自 SP 的字段，
- 选择查询类型下的“文本”选项，
- 输入 SP 名称和参数，就像您从 SSMS 窗口调用它一样。
sp_YourStoredProc @Parameter1....@ParameterN - 单击刷新字段按钮。
- 当语句运行时，字段将刷新，并且您的矩阵将被填充。
BTW, I'm using SQL 2012
顺便说一句，我正在使用 SQL 2012
Hope this helps.
希望这可以帮助。
回答by LCJ
Following is what I did to overcome the issue - Fields are not listed in SSRS
以下是我为克服该问题所做的工作 - SSRS 中未列出字段
Originally I have stored procedure; and it returns data when I execute the dataset. But the fields are not listed in SSRS
I copied the text of the stored procedure and made the dataset as
Textinstead ofStored Procedure. Refer How to: Refresh Fields for a DatasetIt produced errors and I "ignored" those errors. Refer The Declare cursor SQL construct or statement is not supported.
Now I verified that the fields are populated for SSRS
Now I updated the dataset to use my
Stored Procedure
原来我有存储过程；它在我执行数据集时返回数据。但这些字段未在 SSRS 中列出
我复制了存储过程的文本并将数据集设为
Text而不是Stored Procedure. 请参阅如何：刷新数据集的字段它产生了错误，而我“忽略”了这些错误。请参阅不支持声明游标 SQL 构造或语句。
现在我验证了这些字段是为 SSRS 填充的
现在我更新了数据集以使用我的
Stored Procedure
After the above steps, do a refresh of the dataset as shown below:
完成上述步骤后，刷新数据集如下图：
回答by Bhushan Mahajan
Follow below given steps
按照下面给出的步骤
? 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.
OR
或者
**Add the Report field manually . It works.**
If Stored procedure can not retrieve schema data or metadata , we should manually specify the report fields.
如果存储过程无法检索架构数据或元数据，我们应该手动指定报告字段。
回答by frustratedInFresno
This is a very late addition, but for those of us new to SSRS/Report design: Remember to check the order of the parameters within the Report Data pane in Visual Studio. They will be created top to bottom, so a parameter on top cannot depend on a parameter on bottom. This was an issue I had with one report but the error message I received did not state that, so I chased my tail for hours trying to resolve it.
这是一个很晚的补充，但对于我们这些不熟悉 SSRS/报告设计的人：请记住检查 Visual Studio 的“报告数据”窗格中参数的顺序。它们将从上到下创建，因此顶部的参数不能依赖于底部的参数。这是我在一份报告中遇到的一个问题，但我收到的错误消息没有说明这一点，所以我追了几个小时试图解决它。
回答by Phelzier
Don't mean to revive a dead thread but was driving me crazy when upgrading our reports from SSRS 2005 to SSRS 2016 where Stored Procedures used openquery.
并不是要恢复死线程，而是在将我们的报告从 SSRS 2005 升级到 SSRS 2016 时让我发疯，其中存储过程使用了 openquery。
We narrowed it down to reports that had fields with empty values in. So we added this at the start of the Stored Procedure:
我们将范围缩小到包含空值字段的报告。所以我们在存储过程的开头添加了它：
SET CONCAT_NULL_YIELDS_NULL OFF;
which meant we didn't need to CAST every field.
这意味着我们不需要对每个字段进行 CAST。
回答by greg
I ran into a similar issue. I have some debug statments that I often turn on/off w/ a flag variable.
我遇到了类似的问题。我有一些调试语句，我经常使用标志变量打开/关闭它们。
this line was still affecting the results, even though it doesnt run (@debug =0)
这行仍然影响结果，即使它没有运行（@debug =0）
--check your work
if @debug =1 select @reportStartDate RSD ,@reportEndDate
, @yearStart YS, @folderStartDate FSD, @MonthCount MC
, @dataStart DataStart
i discovered the issue by running the sp w/ fmtONly on
我通过运行带有 fmtONly 的 sp 发现了这个问题
SET FMTONLY ON
exec rpt_mo_DashBoard_YTD_Total 'ajax20','12/1/2019'
I solved the problem by commenting out the code in the SP. Yes, now I have to uncomment /comment each return statement when its time to debug...
我通过注释掉SP中的代码解决了这个问题。是的，现在我必须在调试时取消注释/注释每个返回语句......
