SQL Server 2005：“保护”存储过程从MS Access使用的FMTONLY模式 [英] SQL Server 2005: "Protecting" stored procedures from FMTONLY mode used by MS Access

问题描述

一些存储过程包含条件逻辑，如下所示：

 创建过程dbo.DoSomething 
 As 
 ... 
如果（一些条件）开始
设置@SomeVariable = SomeValue 
结束
 ... 
选择.. 。
  

当这样的存储过程用作MS Access表单的记录源时，使用内置的排序/过滤功能的形式，MS Access尝试在FMTONLY模式下执行存储过程（显然，寻找存储过程提供的行集的元数据）。

由于大多数人都知道（现在包括我们自己:-)，当FMTONLY设置为ON时，SQL Server忽略条件语句。在下面的示例中，无论是否 一些条件 ，<@> Set @SomeVariable = SomeValue 是真的，这显然给我们造成了一些麻烦。

  $ b  -  ------- 
创建过程dbo.DoSomething（...，@vcSomeDate as VarChar（50），...）
 As 
 ... 
 Declare @dtSomeDate As Datetime 
 If（IsDate（@vcSomeDateOrAgeInDays））Begin 
  - 当FMTONLY = ON时，下一条语句失败失败
设置@dtSomeDate = @vcSomeDateOrAgeInDays 
结束开始
 ... 
结束
 ... 
  

为了规避这个问题，我们包装条件逻辑（或任何受FMTONLY影响的其他代码片段）如下：

 创建过程dbo.DoSomething（一些参数）
 As 
 ... 
 
  -  HACK：防止意外的FMTONLY模式
声明@wasFmtonlyOn As Bit; If（0 = 1）Set @wasFmtonlyOn = 1; SET FMTONLY OFF 
 ... 
如果（一些条件）开始
设置@SomeVariable = SomeValue 
结束
 ... 
  -  / HACK：防止意外FMTONLY模式
如果（@wasFmtonlyOn = 1）SET FMTONLY ON 
 
 ... 
选择... 
  

（这个丑陋的单行格式的保护代码是有意的：我们认为解决一些奇怪的问题所需要的hack不值得适当的格式化;相反，我们认为它们应该适合尽可能少的代码行： - ）

无论如何，这个保护工作正常，但它有点太冗长，而不是像我们想要的那样封装。例如，我们肯定希望隐藏hack的实际逻辑 - 例如在标量UDF之后，像这样：

 创建过程dbo.DoSomething（一些参数）
 As 
 ... 
 
声明@wasFmtonlyOn为位;设置@wasFmtonlyOn = dbo.SetFmtonly（0）
 ... 
如果（一些条件）开始
设置@SomeVariable = SomeValue 
结束
 ... 
 dbo.SetFmtonly（@wasFmtonlyOn）
 
 ... 
选择... 
  

不幸的是，这似乎不工作 - 既不与标量UDF，不与另一个存储过程。看起来FMTONLY阻止任何地方返回任何数据。所以，这里有主要问题：

如果你还必须处理这个问题SQL Server的忽略条件在FMTONLY模式），你能够提出一个更好的保护成语比上述？

BTW，我还是不明白一件事：这个问题是SQL Server 2005中的一个错误或功能吗？如果它是一个功能，那么什么可能是一个很好的理由呢？

谢谢！

 如果（一些条件）开始
设置@SomeVariable = SomeValue 
 ELSE 
设置@SomeVariable = @SomeVariable  - 或dummy /默认值？ 
结束
  

您的代码是否基于此变量返回2个不同的记录集？
如果是这样，您必须将存储的proc拆分为2

此外，我发现了一个知识库文章解释原因。

编辑：
将分支更改为inline code ...

 设置@dtSomeDate = CASE WHEN ISDATE（@vcSomeDateOrAgeInDays）= 1 THEN @vcSomeDateOrAgeInDays ELSE NULL END 
  

Some of the stored procedures we have contain conditional logic, like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    Select ...

When such a stored procedure is used as a recordsource for an MS Access form, and user tries to use built-in sorting/filtering functionality of the form, MS Access tries to execute the stored procedure in FMTONLY mode (apparently, hunting for metadata of the rowset provided by the stored procedure).

As most people know (now including ourselves :-), when FMTONLY is set ON, SQL Server ignores conditional statements. In the example shown below, the Set @SomeVariable = SomeValue statement is executed regardless of whether Some Condition is true, which obviously creates some troubles for us.

-- EXAMPLE
-- -------
Create Procedure dbo.DoSomething(..., @vcSomeDate as VarChar(50), ...)
As
   ...
   Declare @dtSomeDate As Datetime
   If (IsDate(@vcSomeDateOrAgeInDays)) Begin
       -- The next statement fails miserably when FMTONLY=ON
       Set @dtSomeDate = @vcSomeDateOrAgeInDays
   End Else Begin
       ...
   End
   ...

In order to circumvent this problem, we "wrap" conditional logic (or whatever other code fragments affected by FMTONLY) like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...

    -- HACK: Protection from unexpected FMTONLY mode
    Declare @wasFmtonlyOn As Bit; If (0 = 1) Set @wasFmtonlyOn = 1; SET FMTONLY OFF
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    -- /HACK: Protection from unexpected FMTONLY mode
    If (@wasFmtonlyOn = 1) SET FMTONLY ON

    ...
    Select ...

(This ugly one-line formatting of the "protection code" is intentional: we believe that hacks required to solve some weird problems do not deserve proper formatting; quite the contrary, we believe that they should fit into as few lines of code as possible. :-)

Anyway, this "protection" works fine, but it is somewhat too verbose and not as much encapsulated as we would want it to be. For example, we'd definitely prefer to hide the actual logic of the hack - for example behind a scalar UDF like this:

Create Procedure dbo.DoSomething(Some Parameters)
As
    ...

    declare @wasFmtonlyOn as bit; set @wasFmtonlyOn = dbo.SetFmtonly(0)
    ...
    If (Some Condition) Begin
        Set @SomeVariable = SomeValue                
    End
    ...
    dbo.SetFmtonly(@wasFmtonlyOn)

    ...
    Select ...

Unfortunately, this does not seem to work - neither with scalar UDFs, not with another stored procedure. It looks like FMTONLY prevents return of any data from anywhere. So, here comes The Main Question:

If you also had to deal with this problem (SQL Server's ignoring conditionals in FMTONLY mode), were you able to come up with a better "protection idiom" than the one described above?

BTW, I still don't understand one thing: Is this problem a bug or a feature in SQL Server 2005? And if it is a feature, then what could possibly be a good reason for it?

Thank you!

解决方案

What about this?

If (Some Condition) Begin
    Set @SomeVariable = SomeValue
ELSE
    Set @SomeVariable = @SomeVariable --or dummy/default value?
End

Does your code return 2 different recordsets (columns and types) based on this variable? If so, you'll have to split the stored proc into 2

Also, I found a KB article that explains why.

Edit: Change the branch into inline code...

Set @dtSomeDate = CASE WHEN ISDATE(@vcSomeDateOrAgeInDays) = 1 THEN @vcSomeDateOrAgeInDays ELSE NULL END

这篇关于SQL Server 2005：“保护”存储过程从MS Access使用的FMTONLY模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！