F# SQL 类型提供程序

本文介绍了F# SQL 类型提供程序 - 为什么不提供所有存储过程?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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) ;