主页 / dba / 问题 / 27324
Accepted
Justin Dearing
Asked: 2012-10-22 04:32:58 +0800 CST

有没有办法让 sql server 在当前数据库之前在 master 数据库中搜索 SQLCLR 存储过程？

  • 772

对于前缀为 sp_ 的 T-SQL 存储过程，SQL Server 将在 master 数据库中搜索并使用当前过程之前的过程。对于我使用内置部署的 Visual Studios 通过 SQL Server 2010 创建的 SQLCLR 存储过程，情况似乎并非如此。数据库服务器是 SQL Server 2008 R2 (SP1)。

我执行这个脚本：

SELECT name
from master.sys.procedures
WHERE type_desc='CLR_STORED_PROCEDURE' and schema_id = 1

USE tempdb
PRINT 'USING tempDb'
EXEC sp_RAISERROR_CaughtDemo;
GO
USE master
PRINT 'USING master'
EXEC sp_RAISERROR_CaughtDemo
GO
--SELECT * from sys.assembly_modules

并且存储过程将仅使用 master 数据库中的非限定名称执行：

name
----------------------------------
sp_RAISERROR_CaughtDemo
sp_RAISERROR_UncaughtDemo

(2 row(s) affected)

USING tempDb
Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'sp_RAISERROR_CaughtDemo'.
USING master
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

这些程序的代码如下：

[SqlProcedure(Name = "sp_RAISERROR_UncaughtDemo")]
public static void RaiserrorUncaught()
{
    short i = 0;
    using (var cn = new SqlConnection("context connection=true"))
    using (var cmd = cn.CreateCommand())
    {
        cn.Open();
        cmd.CommandText = "RAISERROR('RAISERROR() Uncaught Severity %d', @i, 1, @i)";
        cmd.Parameters.Add("@i", SqlDbType.SmallInt);
        while (true)
        {
            cmd.Parameters["@i"].Value = i++;
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
        cn.Close();
    }
}

[SqlProcedure(Name = "sp_RAISERROR_CaughtDemo")]
public static void RaiserrorCaught()
{
    try
    {
        short i = 0;
        using (var cn = new SqlConnection("context connection=true"))
        using (var cmd = cn.CreateCommand())
        {
            cn.Open();
            cmd.CommandText = "RAISERROR('RAISERROR() Caught Severity %d', @i, 1, @i)";
            cmd.Parameters.Add("@i", SqlDbType.SmallInt);
            while (true)
            {
                cmd.Parameters["@i"].Value = i++;
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
            cn.Close();
        }
    }
    catch(SqlException) {}
}

我可以做些什么来让 SQL Server 在 master 数据库中搜索以找到这个存储过程吗？我试过sys.sp_MS_marksystemobject了没有用。

sql-server sql-clr

1 个回答

  1. Best Answer

    我不知道解决方案（我不知道 CLR 脚手架是否曾设计为模仿您正在谈论的功能），但一种解决方法是在 master 中创建一个 T-SQL 存储过程作为将调用中继到 CLR 版本的包装器。不必将其标记为系统对象，只要用户数据库中不存在同名的存储过程即可。

    编辑 - 刚刚证明这对我自己和任何事情都很好用。首先，我创建了您的程序集并添加了重现所需的过程（为简洁起见，我不打算在此处包含所有内容）：

    USE master;
GO
CREATE ASSEMBLY [Justin]
    AUTHORIZATION [dbo]
    FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
    DROP FILE ALL
    ADD FILE FROM 0x4D6963726F... even more data here ...;
    AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO

    然后我创建了一个包装存储过程，确保完全限定 CLR 过程：

    CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
    SET NOCOUNT ON;
    EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO

    然后我调整了您的重现代码以改为从 tempdb 调用包装器：

    USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO

    结果：

    USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

    所以这表明您应该能够使用像这样的包装器来从其他数据库调用未引用的 CLR 过程。

    （但是，我建议一般来说这不应该是一个目标——您应该在适用的情况下使用三部分名称正确定义引用。）

    我承认我确实对您的存储过程进行了一次更改，以防止 Visual Studio 对我发牢骚。我变了：

    while (true)

    至：

    while (i <= 11)

    但当然，这种变化与范围界定问题无关。

    • 4

相关问题