Table valued parameters not working for the same Table type in different schemas

[Calkins]

If I define 2 table types with the same name but for different schemas for example:

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)
)

for 2 different procedures that use TVP type but from different schemas (dbo, schema1) for example:

Code: Select all

CREATE PROCEDURE dbo.SP_InsertDept
  @TVP dbo.DeptTableType READONLY
AS
BEGIN
  INSERT INTO DEPT ([DNAME], [LOC])
    SELECT * FROM @TVP
END

Code: Select all

CREATE PROCEDURE schema1.SP_InsertDept
  @TVP schema1.DeptTableType READONLY
AS
BEGIN
  INSERT INTO DEPT ([DNAME], [LOC])
    SELECT * FROM @TVP
END

SQL Server is working fine but SDAC generates mi error:

Code: Select all

Column, parameter, or variable #2: Cannot find data type MyDB.dbo.DeptTableType. ErrorCode=00000A9B

Am I do something wrong or is this bug in SDAC.

Thx in advance for support.
Calkins

[AndreyZ]

To avoid the problem, you should specify the schema name in the TMSTableData.TableTypeName property. Here is a code example:

Code: Select all

MSTableData.TableTypeName := 'dbo.DeptTableType';

[Calkins]

We do exactly this way

Code: Select all

MSTableData.TableTypeName := 'dbo.DeptTableType';

and we get this error.

BTW in SDAC components I don't see schema name in properties only name of object from DB for example "DeptTableType" without "dbo" schema name.

[AndreyZ]

We fixed this problem in SDAC version 6.6.12. To avoid the problem, you should upgrade SDAC to version 6.6.12 or higher.

[Calkins]

In newest version all is working fine. Thx for support.

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.