在SQL Server数据库之间传递用户定义的表类型

I have a user-defined table type in one database in SQL Server (let's call this DB1).

我在SQL Server的一个数据库中有一个用户定义的表类型(让我们称之为DB1)。

The definition for my type is very simple and just includes 2 columns. The script to create my type is below:

我的类型的定义非常简单,只包含2列。创建我的类型的脚本如下:

CREATE TYPE [dbo].[CustomList] AS TABLE
(
    [ID] [int] ,
    [Display] [NVARCHAR] (100)  
)

I've also run the same script on another database, so my type is on 2 databases (Let's call the 2nd database DB2).

我还在另一个数据库上运行相同的脚本,所以我的类型在2个数据库上(让我们调用第二个数据库DB2)。

I now call a stored procedure in DB1 from my C# app passing in the parameter for my CustomList user-defined type.

我现在从我的C#app调用DB1中的存储过程,传入我的CustomList用户定义类型的参数。

The procedure in DB1 now needs to call a procedure on DB2 passing in this CustomList.

DB1中的过程现在需要调用DB2上传递此CustomList的过程。

So, the procedure in DB1 looks like this:

所以,DB1中的过程如下所示:

ALTER PROCEDURE [dbo].[selectData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
    EXEC DB2.dbo.selectMoreData @psCustomList   
END

And the procedure in DB2 is like this (I've only show the parameter list as that's all that is needed):

DB2中的过程就像这样(我只显示参数列表,因为这就是所需要的):

ALTER PROCEDURE [dbo].[selectMoreData]
    @psCustomList CustomList ReadOnly
AS
BEGIN
......

When I run this I receive the following error:

当我运行这个时,我收到以下错误:

Operand type *: CustomList is incompatible with CustomList

操作数类型冲突:CustomList与CustomList不兼容

Anybody got any ideas what I'm doing wrong?

有人知道我做错了吗?

I'm using SQL Server 2008.

我正在使用SQL Server 2008。

Thanks in advance

提前致谢

1 个解决方案

#1

This is a duplicate of Can you create a CLR UDT to allow for a shared Table type across databases?

这是重复的是否可以创建CLR UDT以允许跨数据库的共享表类型?

Essentially, User-Defined Table Types cannot be shared across databases. CLR-based UDTs can be shared across databases, but only if certain conditions have been met, such as the same Assembly being loaded into both databases, and a few other things (details are in the duplicate question noted above).

实质上,用户定义的表类型不能跨数据库共享。基于CLR的UDT可以跨数据库共享,但仅在满足某些条件时才会共享,例如同一个程序集被加载到两个数据库中,以及其他一些事项(详细信息在上面提到的重复问题中)。

For this particular situation, there is a way to pass the info from DB1 to DB2, though it is not an elegant solution. In order to use a Table Type, your current database context needs to be the database in which the Table Type exists. This is done via the USE statement, but that can only be done in dynamic SQL if needing to be done within a Stored Procedure.

对于这种特殊情况,有一种方法可以将信息从DB1传递给DB2,尽管它不是一个优雅的解决方案。要使用表类型,您当前的数据库上下文必须是表类型所在的数据库。这是通过USE语句完成的,但只有在需要在存储过程中完成时才能在动态SQL中完成。

USE [DB1];
GO

CREATE PROCEDURE [dbo].[selectData]
    @psCustomList CustomList READONLY
AS
BEGIN
    -- create a temp table as it can be referenced in dynamic SQL
    CREATE TABLE #TempCustomList
    (
        [ID] [INT],
        [Display] [NVARCHAR] (100)
    );

    INSERT INTO #TempCustomList (ID, Display)
        SELECT ID, Display FROM @psCustomList;

    EXEC('
        USE [DB2];

        DECLARE @VarCustomList CustomList;

        INSERT INTO @VarCustomList (ID, Display)
            SELECT ID, Display FROM #TempCustomList;

        EXEC dbo.selectMoreData @VarCustomList;
     ');
END

UPDATE

Using sp_executesql, either in an attempt to avoid the local temporary table by simply passing in the UDTT as a TVP, or simply as a means of doing a parameterized query, does not actually work (though it certainly looks like it should). Meaning, the following:

使用sp_executesql,试图通过简单地将UDTT作为TVP传递来避免本地临时表,或者仅仅作为执行参数化查询的手段,实际上并不起作用(尽管它看起来应该如此)。含义如下:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeA
(
    @TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;

EXEC sp_executesql N'
  USE [DB2];
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  INSERT INTO @TableTypeDB2 ([Col1])
    SELECT tmp.[Col1]
    FROM   @TableTypeDB1 tmp;

  --EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
  ',
  N'@TableTypeDB1 dbo.TestTable1 READONLY',
  @TableTypeDB1 = @TheUDTT;
GO


DECLARE @tmp dbo.TestTable1;
INSERT INTO @tmp ([Col1]) VALUES (1), (3);
SELECT * FROM @tmp;

EXEC dbo.CrossDatabaseTableTypeA @TheUDTT = @tmp;

will fail on "@TableTypeDB2 has an invalid datatype", even though it correctly displays that DB2 is the "current" Database. It has something to do with how sp_executesql determines variable datatypes since the error referred to @TableTypeDB2 as "variable # 2", even though it is created locally and not as an input parameter.

将失败“@ TableTypeDB2具有无效的数据类型”,即使它正确显示DB2是“当前”数据库。它与sp_executesql如何确定变量数据类型有关,因为错误将@ TableTypeDB2称为“变量#2”,即使它是在本地创建的而不是作为输入参数。

In fact, sp_executesql will error if a single variable is declared (via the parameter list input parameter to sp_executesql), even if it is never referenced, let alone used. Meaning, the following code will run into the same error of not being able to find the definition for the UDTT that happens with the query immediately above:

事实上,如果声明了一个变量(通过sp_executesql的参数列表输入参数),sp_executesql将会出错,即使它从未被引用,更不用说了。意思是,以下代码将遇到同样的错误,即无法找到上面查询中发生的UDTT的定义:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeC
AS
SET NOCOUNT ON;

EXEC sp_executesql N'
  USE [DB2];
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  ',
  N'@SomeVar INT',
  @SomeVar = 1;
GO

(Thanks to @Mark Sowul for mentioning that sp_executesql doesn't work when passing in variables)

(感谢@Mark Sowul提到sp_executesql在传入变量时不起作用)

HOWEVER, this problem can be worked-around (well, as long as you aren't trying to pass in the TVP in order to avoid the temp table -- 2 queries above) by changing the execution database of sp_executesql so that the process will be local to the DB in which the other TVP exists. One nice thing about sp_executesql is that, unlike EXEC, it is a Stored Procedure, and a system stored procedure at that, so it can be fully qualified. Making use of this fact allows sp_executesql to work, which also means that there is no need for the USE [DB2]; statement within the Dynamic SQL. The following code does work:

但是,这个问题可以通过改变sp_executesql的执行数据库来解决这个问题(好吧,只要你不试图传入TVP以避免临时表 - 上面的2个查询)以便进程将是其他TVP所在的DB的本地。关于sp_executesql的一个好处是,与EXEC不同,它是一个存储过程,并且是一个系统存储过程,所以它可以是完全限定的。利用这个事实可以使sp_executesql工作,这也意味着不需要USE [DB2];动态SQL中的语句。以下代码确实有效:

USE [DB1];
GO
CREATE PROCEDURE dbo.CrossDatabaseTableTypeD
(
    @TheUDTT dbo.TestTable1 READONLY
)
AS
SET NOCOUNT ON;

-- create a temp table as it can be referenced in dynamic SQL
CREATE TABLE #TempList
(
    [ID] [INT]
);

INSERT INTO #TempList ([ID])
   SELECT [Col1] FROM @TheUDTT;

EXEC [DB2].[dbo].sp_executesql N'
  SELECT DB_NAME() AS [CurrentDB];

  DECLARE @TableTypeDB2 dbo.TestTable2;
  INSERT INTO @TableTypeDB2 ([Col1])
    SELECT tmp.[ID]
    FROM   #TempList tmp;

  EXEC dbo.CrossDatabaseTableTypeB @TableTypeDB2;
  ',
  N'@SomeVariable INT',
  @SomeVariable = 1111;
GO

#1