F#SQL类型提供程序-为什么不提供所有存储过程? [英] F# SQL type provider - why aren't all stored procedures provided?

问题描述

我正在尝试通过Microsoft.FSharp.Data.TypeProviders.SqlDataConnection类型访问现有数据库.有一些未提供的存储过程(但大多数都有).

我正在尝试确定无法将无法提供的过程与其他过程区分开的原因-我认为自定义类型是类型设置可能会失败的原因之一，但它们似乎不在这里.

由于其他原因，我们的存储过程可能无法提供吗?

我已经确定以下块是导致无法提供的块:

      EXEC @intReturn = te_audit_log @action = 'I',
                                     @user_id = @intUserId,
                                     @table_id = 1,
                                     @audit_action = 'A',
                                     @data_id = @intStatus,
                                     @session_guid = @session_guid,
                                     @effective_date = @actual_timedate,
                                     @employee_id = @employee_id

...我认为这是因为正在执行"的sproc也具有从临时表返回值的路径.

我假定类型提供程序无法获取存储过程(或其他数据库对象)，因为类型提供程序无法获取有关结果集的元数据.检查 sp_describe_first_result_set 和设置FMTONLY

原因在备注部分的文档中指定:

在以下任何一种情况下，sp_describe_first_result_set都会返回错误.

如果输入@tsql不是有效的Transact-SQL批处理.通过分析和分析Transact-SQL批处理确定有效性.确定Transact-SQL批处理是否有效时，将不考虑该批处理在查询优化期间或执行期间引起的任何错误.

如果@params不为NULL并且包含不是语法上有效的参数声明字符串的字符串，或者它包含的声明任何参数超过一次的字符串，则为

.

如果输入的Transact-SQL批处理声明了与@params中声明的参数同名的局部变量.

如果语句使用临时表.

查询包括创建一个永久表，然后查询该表.

在一个批次中找到多个可能的第一条语句时，它们的结果在列数，列名，可空性和数据类型上可能会有所不同.这些差异的处理方式在此处进行了详细说明:

如果列数不同，则会引发错误，并且不返回任何结果.

如果列名不同，则将返回的列名设置为NULL.

可空性有所不同，返回的可空性将允许为空.

如果数据类型不同，则将抛出错误，并且除了以下情况以外，不会返回任何结果:

• varchar(a)到varchar(a')，其中a'> a.

• varchar(a)到varchar(max)

• nvarchar(a)到nvarchar(a')，其中a'> a.

• nvarchar(a)到nvarchar(max)

• varbinary(a)到varbinary(a')，其中a'> a.

• varbinary(a)到varbinary(max)

包含上述情况的数据库对象和查询根本不存在.

检查TSQL存储过程是否返回正确的元数据

SELECT * FROM sys.dm_exec_describe_first_result_set ('[schema].[name]',<params> , 0);

I'm trying to access an existing database via the Microsoft.FSharp.Data.TypeProviders.SqlDataConnection type. There are some stored procedures that are not provided (but most are).

I'm trying to determine what differentiates the unprovidable procedures from the others -- I believe that custom types are one reason the type-provision might fail, but they don't appear to be present here.

For what other reasons might our stored procedures be unprovidable?

Edit:

I've identified the following block as one that causes unprovidability:

      EXEC @intReturn = te_audit_log @action = 'I',
                                     @user_id = @intUserId,
                                     @table_id = 1,
                                     @audit_action = 'A',
                                     @data_id = @intStatus,
                                     @session_guid = @session_guid,
                                     @effective_date = @actual_timedate,
                                     @employee_id = @employee_id

...I think it is because the sproc that is being "exec"ed also having paths that return values from a temp table.

解决方案

I assume that type provider cannot get stored procedures(or other db object), because type provider cannot get metadata about result set. Check sp_describe_first_result_set and SET FMTONLY

Reasons are specified in documenation in remark section:

sp_describe_first_result_set returns an error in any of the following cases.

If the input @tsql is not a valid Transact-SQL batch. Validity is determined by parsing and analyzing the Transact-SQL batch. Any errors caused by the batch during query optimization or during execution are not considered when determining whether the Transact-SQL batch is valid.

If @params is not NULL and contains a string that is not a syntactically valid declaration string for parameters, or if it contains a string that declares any parameter more than one time.

If the input Transact-SQL batch declares a local variable of the same name as a parameter declared in @params.

If the statement uses a temporary table.

The query includes the creation of a permanent table that is then queried.

When multiple possible first statements are found in a batch, their results can differ in number of columns, column name, nullability, and data type. How these differences are handled is described in more detail here:

If the number of columns differs, an error is thrown and no result is returned.

If the column name differs, the column name returned is set to NULL.

It the nullability differs, the nullability returned will allow NULLs.

If the data type differs, an error will be thrown and no result is returned except for the following cases:

• varchar(a) to varchar(a') where a' > a.

• varchar(a) to varchar(max)

• nvarchar(a) to nvarchar(a') where a' > a.

• nvarchar(a) to nvarchar(max)

• varbinary(a) to varbinary(a') where a' > a.

• varbinary(a) to varbinary(max)

DB objects and queries that contain above cases are simply not present.

Checking if TSQL Stored Procedure return correct metadata

SELECT * FROM sys.dm_exec_describe_first_result_set ('[schema].[name]',<params> , 0) ;

这篇关于F#SQL类型提供程序-为什么不提供所有存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！