datetime2 gives exception when reading using TMSQuery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
SwissQual
Posts: 11
Joined: Wed 17 Apr 2013 06:04

datetime2 gives exception when reading using TMSQuery

Post by SwissQual » Wed 17 Apr 2013 06:09

I recently changed my MS SQL datetime fields to datetime2. If I now access the datetime2 fields using TMSQuery, the type is widestring and gives an exception if I want to read it as AsDateTime (or AsFloat).
I currently use SDAC 6.1.4
Example:
qTemp1.FieldByName('MsgTime').AsDateTime

This statement works for datetime, but not datetime2

What I am doing wrong?

AndreyZ

Re: datetime2 gives exception when reading using TMSQuery

Post by AndreyZ » Wed 17 Apr 2013 07:51

This problem is caused by the OLEDB provider. OLEDB provider returns DATETIME2 columns in the string format. To solve the problem, you should use the SQL Native Client provider. To use SQL Native Client, you should set the TMSConnection.Options.Provider property to prNativeClient.

SwissQual
Posts: 11
Joined: Wed 17 Apr 2013 06:04

Re: datetime2 gives exception when reading using TMSQuery

Post by SwissQual » Wed 17 Apr 2013 14:41

Hi
Thanks for the answer. However if I set the provider to prNativeClient (I have to use OLEDBAccess, because otherwise the enum prNativeClinet is not known, correct?)
, I get the error
"Project Project1.exe raised exception class EDatabaseError with message 'Required provider is not installed'."

I have a SQL Server 2012 installed which includes the 2012 Native Client.
Do I have to change the Connection String?
The current Connectionstring is:
"Provider=SQLNCLI.1;Integrated Security=SSPI"

AndreyZ

Re: datetime2 gives exception when reading using TMSQuery

Post by AndreyZ » Thu 18 Apr 2013 07:33

The point is that SDAC supports SQL Native Client 11 (that is shipped with SQL Server 2012) since version 6.2.7. To avoid the problem, you should upgrade SDAC at least to version 6.2.7.

Post Reply