在具有用户定义表类型的另一个数据库上调用过程

本文关键字：数据库 另一个 调用 过程 类型 用户 定义 | 更新日期: 2023-09-27 18:34:41

我有一个数据库的连接，并有权连接到另一个数据库。我想在另一个具有用户表数据类型参数的数据库上调用一个过程。但是无论我尝试什么，都找不到用户表数据类型。

  • 我尝试在 [dbo] 前面使用数据库名称。[myType]，但它不是一个有效的语法。
  • 我尝试在当前数据库中创建相同的类型
  • 我尝试在模型数据库中创建相同的类型
  • 我尝试在我的SqlCommand.Text顶部附加"USE MyOtherDatabase">

一切都失败了(我真的很讨厌"使用......"方法失败(。

我该怎么做？

代码示例：

// While connected to MyOtherDatabase
CREATE TYPE dbo.typeClubMembersVersion AS TABLE (
    ID INT
    , UNIQUE(ID)
    , [version] INT
)
GO
CREATE PROCEDURE dbo.spCheckCMembersMods (
    @pVersions AS dbo.typeClubMembersVersion READONLY
    , @pWhoID AS BIGINT
)
AS
BEGIN
    [...]
END



        SqlCommand com = new SqlConnection(functions.ConnectionString).CreateCommand();
        com.CommandText = @"
// While connected to CurrentDatabase
USE MyOtherDatabase
DECLARE @tbl AS dbo.typeClubMembersVersion
BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember
    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";
        com.Parameters.Add("@Title", SqlDbType.NVarChar, 20).Value = this.Title;
        com.Parameters.Add("@IdMember", SqlDbType.BigInt).Value = this.Id;
        com.Parameters.Add("@whoID", SqlDbType.BigInt).Value = (object)whoID ?? DBNull.Value;
        com.Connection.Open();
        try
        {
            com.ExecuteNonQuery();
        }
        catch (Exception exe)
        {
            throw exe;
        }
        finally
        {
            com.Connection.Close();
        }

在具有用户定义表类型的另一个数据库上调用过程

首先，您所谓的"架构"实际上是SQL Server中的"数据库"。对象名称中的"dbo."是 SQL Server 中的"架构"。"USE.."命令仅适用于数据库。

其次，不能引用或使用另一个数据库中的类型，必须在使用它的同一数据库中定义它。 类型可以位于其他 SQL Server 架构中，但不能位于其他数据库中，这是您在此处实际尝试执行的操作。

好的，正如你所指出的，你的类型是在[myOtherDatbase]中定义的，那么为什么它不起作用呢？ 可能是因为 USE.. 和 SQL 命令字符串不符合您的想象。 每当将这样的字符串传递给 SQL Server 并尝试执行它时：

com.CommandText = @"
// While connected to CurrentDatabase
USE MyOtherDatabase
DECLARE @tbl AS dbo.typeClubMembersVersion
BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember
    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";

SQL Server将首先编译整个字符串，然后尝试执行它。 这意味着在执行任何命令之前，首先编译所有命令。 这意味着您的DECLARE @tbl和UPDATE..命令是在执行USE命令之前编译的。 因此，当编译它们时，您仍然在尚未定义Type的先前数据库中。 这就是导致语法错误的原因(这些错误来自编译器，而不是来自它们的执行(。

有三种可能的解决方案：

  1. 也在当前数据库中定义类型(我很确定这有效，但不是 100%(。

  2. 使用指定"初始目录=我的其他数据库"的连接字符串重新连接。

  3. 使用动态 SQL 重新执行 USE 命令后的所有内容。

其中我会推荐#2。

愚蠢的我，我刚刚意识到还有另一种选择：

  • 首先只执行USE命令本身，
  • 然后，在同一连接上执行其余的 SQL 命令。

当然，这会让你留在 [MyOtherDatabase] 中，所以你可能想通过执行另一个USE命令回到你的原始数据库来结束它。

自从我不得不使用SqlConnection.ChangeDatabase以来，我已经有很长时间了。 到目前为止，我一直能够使用"完全命名的对象"来使我的数据库相互交互。
由于我目前被困住了，我会使用它，但我希望有人告诉我一种方法，不要强迫我放弃当前的数据库连接。

    SqlCommand com = new SqlConnection(functions.ConnectionString).CreateCommand();
    com.CommandText = @"
DECLARE @tbl AS dbo.typeClubMembersVersion
BEGIN TRANSACTION
    UPDATE dbo.tClubMembers
    SET
        Title = @Title
    OUTPUT inserted.ID, deleted.[version] INTO @tbl (ID, [version])
    WHERE IdMember = @IdMember
    EXEC dbo.spCheckCMembersMods @tbl, @whoID
COMMIT
";
    com.Parameters.Add("@Title", SqlDbType.NVarChar, 20).Value = this.Title;
    com.Parameters.Add("@IdMember", SqlDbType.BigInt).Value = this.Id;
    com.Parameters.Add("@whoID", SqlDbType.BigInt).Value = (object)whoID ?? DBNull.Value;
    com.Connection.Open();
    try
    {
        com.Connection.ChangeDatabase("MyOtherDatabase");
        com.ExecuteNonQuery();
    }
    catch (Exception exe)
    {
        throw exe;
    }
    finally
    {
        com.Connection.Close();
    }
相关文章：