Procedure with table-value parameter
Posted: Fri 18 Jan 2013 08:52
Hello
I have stored procedure with table-value parameter. Procedure and table type are defined in a schema other than dbo, ie. myschema.myproc and myschema.mytype.
On execution I get error: Column, parameter, or variable #1: Cannot find data type myschema.mytype.
When I set TableTypeName property as 'mytype' (without schema name) i can succesfully execute procedure, but in this case mytype cannot exist in more, then one schema. If it does TMSTableData object contains fields from types from all schemas.
Also Delphi designer lists and saves to .dfm file table-value types without schemas (TMSTableData component, TableTypeName dropdown list).
I use SDAC v.6.6.11, Delphi XE2 Update 4 HotFix 1 and SQL Server 2008 R2.
I have stored procedure with table-value parameter. Procedure and table type are defined in a schema other than dbo, ie. myschema.myproc and myschema.mytype.
Code: Select all
Items := TMSTableData.Create(nil);
Items.Connection := SomeConnection;
Items.TableTypeName := 'myschema.mytype';
Items.Open;
Items.Append;
Items.Fields[0].AsString := 'abc';
Items.Post;
Proc.ParamByName('tableparam').AsTable := Items.Table;
Proc.ExecProc; // ErrorWhen I set TableTypeName property as 'mytype' (without schema name) i can succesfully execute procedure, but in this case mytype cannot exist in more, then one schema. If it does TMSTableData object contains fields from types from all schemas.
Also Delphi designer lists and saves to .dfm file table-value types without schemas (TMSTableData component, TableTypeName dropdown list).
I use SDAC v.6.6.11, Delphi XE2 Update 4 HotFix 1 and SQL Server 2008 R2.