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

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Calkins
Posts: 10
Joined: Thu 12 Sep 2013 10:40

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

Post by Calkins » Wed 23 Oct 2013 11:10

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

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

Post by AndreyZ » Wed 23 Oct 2013 14:12

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
Posts: 10
Joined: Thu 12 Sep 2013 10:40

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

Post by Calkins » Thu 24 Oct 2013 05:20

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

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

Post by AndreyZ » Thu 24 Oct 2013 09:00

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
Posts: 10
Joined: Thu 12 Sep 2013 10:40

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

Post by Calkins » Tue 05 Nov 2013 08:27

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

AndreyZ

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

Post by AndreyZ » Tue 05 Nov 2013 08:41

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

Post Reply