主页 / dba / 问题 / 115023
Accepted
David Clarke
Asked: 2015-09-15 17:41:50 +0800 CST

SQL Server 2012 中的 FMTONLY 问题

  • 772

我支持使用 CodeSmith 和 NetTiers 模板生成 C# 代码的应用程序。CodeSmith 检查数据库并使用SET FMTONLY ON设置来确定应为其生成代码的列。

不幸的是，在从 SQL Server 2005 迁移到 2012 的过程中，有一种特殊情况现在不再有效。有一个存储过程在链接服务器上执行存储过程，并且为该存储过程生成的代码不正确。

我已经设法将问题EXEC与SET FMTONLY ON. 以下（仅示例）SELECT适用于 2005 和 2012 实例：

SET FMTONLY ON
SELECT TOP(10) [MCMCU]
              ,[MCSTYL]
              ,[MCDC]
FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]
SET FMTONLY OFF

正如预期的那样，这仅返回列标题。以下仅适用于 2005 实例：

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE_CRP].[CRPDTA].[F0006]') AT [JDE]
SET FMTONLY OFF

在 2012 实例上运行时，SSMS 显示消息“命令已成功完成”，但不显示列标题。

我在这里有什么遗漏吗？也许我需要更改设置？链接服务器定义相同，包括用于连接的标识。是的，我知道这FMTONLY已被弃用，但我没有任何能力改变 CodeSmith 询问数据库的方式。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    我可以在 SQL Server 2012 上重现相同的行为。但是，我可以通过将链接服务器名称放回查询中来使其工作。AT您将其移到子句中是否有原因？

    尝试以下操作：

    SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]');
SET FMTONLY OFF

    好吧，虽然上述方法在 SQL Server 2012 上确实有效，但它不是一个好的测试，因为它在执行存储过程时不起作用。它可能与它是一种不同类型的调用有关（即存储过程调用是 RPC，并且必须单独配置）。

    我什至只是尝试添加WITH RESULT SETS ((...))，但仍然没有用。

    好的，所以我发现了一些东西。众所周知，FMTONLY ON它实际上并不运行代码，它只是扫描它的SELECT语句。但是，这也意味着它不知道如何处理条件代码。所以它返回任何可以返回的结果集，即使没有代码路径会返回其中的一些。

    示例 1 有两个SELECT语句，但一次只能返回一个。尽管如此，当运行时FMTONLY ON，两者都返回：

    CREATE PROCEDURE dbo.FmtOnlyTest1
AS
SET NOCOUNT ON;
  DECLARE @A INT = 5;

  IF (@A <> 6)
  BEGIN
    SELECT TOP 5 * FROM [master].[sys].[objects];
  END;
  ELSE
  BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
    EXEC(@SQL);
END;
GO

    结果：

    -- Running normally returns one result set of 5 rows from sys.objects:
EXEC Test.dbo.FmtOnlyTest1;

-- With FMTONLY ON, it returns two result sets: one from sys.objects AND one with [IntVal]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest1;
SET FMTONLY OFF

    示例 2 显示了无法运行代码的另一个后果。如果代码动态生成结果集，则无法由FMTONLY ON. 这可能解释了为什么当代码是远程的时它不能跟随 EXEC 调用。我怀疑调用本地存储过程只需在本地系统目录表中查找该定义即可。

    CREATE PROCEDURE dbo.FmtOnlyTest2
AS
SET NOCOUNT ON;
  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

    结果：

    -- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest2;

-- With FMTONLY ON, there are no result sets returned (just like with the remote procedure)
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest2;
SET FMTONLY OFF

    知道了我们现在所知道的，我们可以利用这样一个事实，即FMTONLY ON在不实际运行代码的情况下找到所有结果集，即使不理想，也可以伪造一个结果集以使 CodeSmith（和其他仍然以这种方式工作的工具）再次工作.

    示例 3 表明我们可以有效地将虚拟结果集隐藏在无法逻辑执行的代码块中。只有FMTONLY ON将能够看到这一点，因此它不会对任何其他代码造成问题（好吧，我确实尝试sys.dm_exec_describe_first_result_set过，但并不真正喜欢它，但我认为在这种情况下这不是问题）。

    CREATE PROCEDURE dbo.FmtOnlyTest3
AS
SET NOCOUNT ON;

  IF (1 = 0)
  BEGIN
    SELECT CONVERT(INT, NULL) AS [FieldName1],
           CONVERT(DATETIME, NULL) AS [FieldName2];
  END;

  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

    结果：

    -- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest3;

-- Running with FMTONLY ON returns one result set of [FieldName1], [FieldName2]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest3;
SET FMTONLY OFF

    对于这个测试，我让代码为IF (1 = 0)块返回不同的结果集，以使行为差异更加明显。SELECT但实际上，您可能希望在远程存储过程返回时返回完全相同的结果集结构。

    这里明显的缺点是，如果您更改该远程过程的结果集，您将必须记住还要更改IF (1 = 0)块中的定义，但至少 CodeSmith 将起作用。

    • 5

相关问题