May 28, 2013 at 7:13 am
Hello!
I created a type ( CREATE TYPE AS TABLE myType .. ) in my 2 databases to be passed as a parameter in a Procedure.
Follows the example:
declare @myType myType;
INSERT @myType (a,b)
SELECT
a, b
FROM DataBaseA..tableA
IF EXISTS(select * from @myType )
BEGIN
-- execute PD in other database
EXEC DataBaseB..PD_MyProcedure @myType
END;
The following error is occurring:
"Operand type clash: myType is incompatible with myType"
But when I run the same database, the error does not occur ..
Could you help me ?
Thanks,
Jose Anchieta C. JR
May 28, 2013 at 8:18 am
You can't use TableValued parameters across databases. It seems like it should be ok but the reality is the datatypes are NOT the same. You know they are declared the same but there is nothing in SQL that knows they are. They could have the same name but be completely different.
You can however use some dynamic sql to get around this. Something like this should be pretty close.
declare @sql nvarchar(max)
select @sql = 'declare @myType myType;'
+ (select 'Insert myType(a, b) select ' + a + ', ' + b + ';' from @myType for XML PATH(''))
+ 'exec PD_MyProcedure @myType;'
--select @sql
exec DataBaseB..sp_executesql @sql
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2013 at 2:29 pm
Thank you!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply