Questions regarding user-defined types

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Nilss
Posts: 11
Joined: Thu 27 Jun 2013 12:23

Questions regarding user-defined types

Post by Nilss » Thu 23 Jun 2016 14:37

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
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Questions regarding user-defined types

Post by ViktorV » Fri 24 Jun 2016 12:09

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
Posts: 11
Joined: Thu 27 Jun 2013 12:23

Re: Questions regarding user-defined types

Post by Nilss » Mon 27 Jun 2016 09:20

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
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Questions regarding user-defined types

Post by ViktorV » Mon 27 Jun 2016 12:19

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
Posts: 11
Joined: Thu 27 Jun 2013 12:23

Re: Questions regarding user-defined types

Post by Nilss » Tue 28 Jun 2016 08:40

Thank you, not preparing the stored procedure did work. Is there no way around this restriction?
ViktorV wrote: 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
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Questions regarding user-defined types

Post by ViktorV » Fri 01 Jul 2016 07:33

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
Posts: 11
Joined: Thu 27 Jun 2013 12:23

Re: Questions regarding user-defined types

Post by Nilss » Wed 13 Jul 2016 10:58

Did you receive my sample?

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Questions regarding user-defined types

Post by ViktorV » Wed 13 Jul 2016 12:04

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
Posts: 11
Joined: Thu 27 Jun 2013 12:23

Re: Questions regarding user-defined types

Post by Nilss » Wed 13 Jul 2016 12:10

No need to be sorry, thank you very much.

Post Reply