December 29, 2008 at 2:53 pm
We are successfully passing table type parameters to stored procedures within a 2008 database. However, I cannot get this to work when I pass a table type param to a stored procedure on the same server, but a different database. I have defined the same table type in both databases with the same table name, column names and datatypes. The error I get when trying to execute the stored procedure from the other database is:
Msg 2715, Level 16, State 3, Line 5
Column, parameter, or variable #1: Cannot find data type typeCloseOutControl2.
Parameter or variable '@testType' has an invalid data type.
Msg 1087, Level 16, State 1, Line 3
Must declare the table variable "@testType".
I've discovered that you cannot pass a table type param to a remote stored procedure (on a linked server). http://www.sommarskog.se/tableparam.html. Would this be true of a call to a stored procedure on the same server but different database?
January 23, 2009 at 9:06 am
I'm having the same problem.
I'm trying to use a table-valued parameter between two databases. I created the table type in both dbs (here called CallingDB and OtherDB) and they have the same structure and permissions. I think an example will make my problem clearer than trying to explain so here goes.
USE CallingDB
GO
CREATE TYPE [dbo].[tblTest] AS TABLE([THIS_Id] [int] NULL,[THAT_Id] [tinyint] NULL)
GO
USE OtherDB
GO
CREATE TYPE [dbo].[tblTest] AS TABLE([THIS_Id] [int] NULL,[THAT_Id] [tinyint] NULL)
GO
I'm the sysadmin so permissions don't matter here.
I create a procedure that utilizes this new table type and joins it to a table called dbo.SmallNums (simply contains an INT column with values from 1-1000):
USE OtherDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.PROC_Test @tblTest tblTest READONLY
AS
SET NOCOUNT ON
SELECT sn.n,t.THIS_Id FROM dbo.SmallNums sn INNER JOIN @tblTest t ON t.THIS_Id=sn.n
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
I can make use of this table type in dynamic SQL if I stay within one DB for the whole process:
USE OtherDB
GO
DECLARE @tblTest tblTest,@Command nVARCHAR(max)
INSERT INTO @tblTest(THIS_Id,THAT_Id)
SELECT 33,35
UNION ALL
SELECT 334,35
UNION ALL
SELECT 8753942,35
UNION ALL
SELECT 2360,35
SELECT @Command='EXEC dbo.PROC_Test @tblTest'
EXEC sp_executesql @Command,N'@tblTest tblTest READONLY',@tblTest
GO
But when I try to call that same procedure from another db and pass in the table type parameter I get a compatibility error:
Msg 206, Level 16, State 2, Procedure PROC_Test, Line 0
Operand type clash: tblTest is incompatible with tblTest
USE CallingDB
GO
DECLARE @tblTest tblTest,@Command nvarchar(max)
INSERT INTO @tblTest(THIS_Id,THAT_Id)
SELECT 33,35
UNION ALL
SELECT 334,35
UNION ALL
SELECT 8753942,35
UNION ALL
SELECT 2360,35
SELECT @Command='EXEC OtherDB.dbo.PROC_Test @tblTest'
EXEC sp_executesql @Command,N'@tblTest tblTest READONLY',@tblTest
So what gives? I hope it's something simple that I've missed.
January 23, 2009 at 9:50 am
I tried all of the same things. Your example is very close to what I did. But I never resolved the problem. I think it has to do with the fact that the Table Type is passed only as a pointer. Apparently the DB cannot resolve this across databases.
I ended up do a cross database select, though that was not the optimal solution.
August 17, 2010 at 8:19 am
having the exact same thing:
use [db1]
CREATE type dbo.ParetoValues2 as table ( RowNbr bigint primary key not null,Val float)
use [db2]
CREATE type dbo.ParetoValues2 as table ( RowNbr bigint primary key not null,Val float)
create function dbo.fn_W2( @a dbo.ParetoValues2 readonly)
returns table
as
RETURN
SELECT * from @a
use [db1]
declare @a ParetoValues2
Insert into @a
select ROW_NUMBER() OVER(ORDER BY column_id desc) RowNbr,column_id as val
FROM sys.columns
select * FROM [db2].dbo.fn_W2( @a)
--Msg 206, Level 16, State 2, Line 8
--Operand type clash: ParetoValues2 is incompatible with ParetoValues2
Seems like a bug to me.
August 17, 2010 at 8:28 am
This was so long ago that I'd forgotten. It's been answered elsewhere but here's the gist of the explanation: user defined data types and table tables are CLR objects(compiled code) and one database doesn't know the internal code of another database's CLR objects. So database A can't instantiate database B's object.
August 17, 2010 at 11:00 am
That is correct. All UDF types are database specific and cannot cross database boundaries. This is great for security and all, but very frustrating if you would like to pass things like table valued parameters between procs in different databases.
I had to resort to global temporary tables for this. (##temptable).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply