如何从函数调用扩展过程 [英] How to call an extended procedure from a function

问题描述

我在尝试使以下功能正常工作时遇到问题.

hi im having trouble trying to get the following function to work.

CREATE FUNCTION test ( @nt_group VARCHAR(128) )
RETURNS @nt_usr TABLE (
      [name] [nchar](128) NULL
    , [type] [char](8) NULL
    , [privilege] [char](9) NULL
    , [mapped login name] [nchar](128) NULL
    , [permission path] [nchar](128) NULL
    )
AS BEGIN 

    INSERT  INTO @nt_usr
    EXEC master.dbo.xp_logininfo 'DOMAIN\USER', @nt_group
     RETURN
   END

据我所知，我应该被允许调用扩展存储过程，但出现以下错误

As far as i know i should be allowed to call an extended stored procedure, im getting the following error

Mes 443，级别 16，状态 14

Mes 443, Level 16, State 14

会不会是 xp_logininfo 可能会根据参数返回不同的结果集?当我使用 openquery 时，我可以通过设置这个来克服这个问题:SET FMTONLY OFF.有谁知道我的问题是否有类似的解决方法?

Could it be that xp_logininfo might return different result sets depending on the parameters? When i use openquery i can overcome this by setting this: SET FMTONLY OFF. Does anyone know if there's a similar workaround for my problem?

推荐答案

你不能，因为这个 xp 返回数据.即使您正在加载表格.

You can't because this xp returns data. Even though you are loading a table.

基本上，udfs 中的 xps 不是初学者...我会使用存储过程

Basically, xps in udfs are a non-starter... I'd use a stored procedure

来自 创建函数

从函数调用扩展存储过程

扩展存储过程，当它从函数内部调用，无法将结果集返回到客户.任何返回的 ODS API结果集到客户端将返回失败.扩展存储过程可以连接回一个实例SQL 服务器；但是，它不应该尝试加入相同的交易作为调用扩展的函数存储过程.

The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL. The extended stored procedure could connect back to an instance of SQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.

类似于从批处理或存储过程，扩展存储程序将在Windows 安全的上下文SQL Server 所在的帐户跑步.存储的所有者程序应考虑这一点时给予 EXECUTE 权限用户.

Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL Server is running. The owner of the stored procedure should consider this when giving EXECUTE permission on it to users.

这篇关于如何从函数调用扩展过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！