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

问题描述

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屋！