Table valued parameters not working for the same Table type in different schemas
Posted: Wed 23 Oct 2013 11:10
If I define 2 table types with the same name but for different schemas for example:
for 2 different procedures that use TVP type but from different schemas (dbo, schema1) for example:
SQL Server is working fine but SDAC generates mi error:
Am I do something wrong or is this bug in SDAC.
Thx in advance for support.
Calkins
Code: Select all
CREATE TYPE dbo.DeptTableType AS TABLE(
DNAME VARCHAR(20),
LOC VARCHAR(20)
)Code: Select all
CREATE TYPE schema1.DeptTableType AS TABLE(
DNAME VARCHAR(20),
LOC VARCHAR(20)
)Code: Select all
CREATE PROCEDURE dbo.SP_InsertDept
@TVP dbo.DeptTableType READONLY
AS
BEGIN
INSERT INTO DEPT ([DNAME], [LOC])
SELECT * FROM @TVP
ENDCode: Select all
CREATE PROCEDURE schema1.SP_InsertDept
@TVP schema1.DeptTableType READONLY
AS
BEGIN
INSERT INTO DEPT ([DNAME], [LOC])
SELECT * FROM @TVP
ENDCode: Select all
Column, parameter, or variable #2: Cannot find data type MyDB.dbo.DeptTableType. ErrorCode=00000A9BThx in advance for support.
Calkins