Questions regarding user-defined types

[Nilss]

Hello,

I am evaluating working with user-defined types in our project, especially hierarchyID.
I encountered three questions I fail to answer myself. In my setup I'm using SQL Server 2014, SQL Server Native Client 11 and SDAC 7.1.3 / 7.3.12.

When accessing a hierarchyID field with the AsUDT property as demonstrated in the demo project I encounter an error due to wrong assembly version of 'Microsoft.SqlServer.Types.dll'. Version 11 of it is tried to being used but not accessible. Where is this version 11 coming from? The version of the assembly in the database is 12, so is the Native Client telling SDAC to use version 11? If that's the case is there any way to influence that?

If I try to pass a hierarchyID from a field to a parameter of a stored procedure, execution of the procedure fails with an 'Operand type clash: image is incompatible with hierarchyid' error. When debugging it seems that the the SubDataType of the parameter is not set and thus using dstImage as ParameterBindingInfo DataSourceType.
I'm assigning the parameter value this way:

Code: Select all

StoredProc.ParamByName('HID').Value := AnotherStoredProc.FieldByName('HID').Value

Is this the correct way of doing this? If so, how do I prevent this error?

TMSTableData seems not to support UDT fields. I could not access them with AsUDT and assigning a value with Value property caused an assertion error. Is this unsupported or did I do it the wrong way?

[ViktorV]

The version of the assembly of the dynamic library is returned by SQL Server. The "wrong assembly version" error occurs when your system has no registered dynamic library of the required assembly version. To solve the issue, please install the required library Microsoft.SqlServer.Types.dll to the system.

[Nilss]

Thank you for your reply. Yes, providing the requested assembly version solves one of the issues.

Do you have any input for my other two problems? I'm still unable to pass a UDT fields value to a parameter and table-valued parameters with UDT fields are causing trouble.

[ViktorV]

To solve the issue with setting the parameter value to UDT field value, please try not to call the TMSStoredProc.Prepare method.
The TMSTableData component is designed for work with Table-Valued Parameters only. See more details about this component in the SDAC documentation: https://www.devart.com/sdac/docs/?devar ... ledata.htm

[Nilss]

Thank you, not preparing the stored procedure did work. Is there no way around this restriction?

The TMSTableData component is designed for work with Table-Valued Parameters only. See more details about this component in the SDAC documentation: https://www.devart.com/sdac/docs/?devar ... ledata.htm

There might be a misunderstanding. What I meant was a table-valued parameter, where the table type contains an UDT field.

[ViktorV]

Please compose a small sample reproducing the described behavior and send it to viktorv*devart*com , including scripts for creating database objects, in order to be able to answer you in more details.

[Nilss]

Did you receive my sample?

[ViktorV]

We are sorry for the delay.
We have received your sample and reproduced the described behavior. We will notify you about the results as any are available.

[Nilss]

No need to be sorry, thank you very much.