RO DA issues with WINDOWS 2016?

Data Abstract
1

Hi guys,

I’m trying to troubleshoot a couple of incidents happening with RO/DA (latest) Delphi Tokyo services under Windows Server 2016. Database MSSQL 2014, using FireDac.

First one is, when installed as a service or running as a program on a Windows 2016 Standard disregarding of using integrated security or sql user the active connection will switch to use implicit transactions, but if we run the service against the same Database server but from a Windows 10 machine it will run with implicit transactions off.

WIN2016 (Service) → Win2016(SQL)

– network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions on
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

WIN10 (Service) → Win2016(SQL)

– network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

This reading where taking out of the Audit Login/Logout of SQL Profiler. We DONT have ANSI DEFAULTS ON that will usually force an implicit transaction, and we use the same service for both. This will definitely kill our performance in production.

The next issue and that is stopping us from releasing into production is that under the same deployment WIN2016(Service) → WIN2016(SQL) we are getting the following error:

An exception was raised on the server: ‘2018-07-24 02:27:13.4860000’ is not a valid date and time

This happens while doing a FILL on a TDAMemDataTable, it is the first record of the table which matches to a DateTime2(7) field with a UTC time store on it.

This error does NOT happen when running the service from a WIN10 machine or a Windows 2008 SR2 connecting to a WIN2016(SQL).

We tried with both active directory and sql users with the same results explained above. I’m running out of ideas on what to try, any asssistance or things I could try will be highly appreciated.

Thank you.

2

I’ll be adding any information we can find out during the weekend. :frowning:

If we run service on Win10 not attached to a domain to a WIN2016 in a domain with sql login everything works.

I’m attaching the sample we are using with the resulting trace as a xml file.
WIN10WIN2016SQLUSER.xml (19.8 KB)

Now if we use a WIN10 and WIN2016(sql) both on a domain using sql user, things start to fail, it sets itself to implicit transactions even if the connection says it shouldnt and fails retrieving the date.

I’m attaching the resulting XML file
WIN10DOMAINWIN2016DOMAINSQLUSER.xml (24.5 KB)

Running the service as a program and elevating it to run as an admin on the WIN10 to WIN2016 on a domain setup didnt make any difference. Implicit transactions ON and failed to retrieve date.

Anything else we can try?

3

Another detail.

Non domain computer running service on Win10 connecting to Domain computer on WIN2016(MSSQL) same problem, implicit transactions ON and date invalid.

Could it be that something is pending to install on the service computer? ODBC driver (sql 2016) or sql client? (sql 2012) ?

We are trying the above but doesnt seem to make a difference.

4

Update.

Mystery solved. The driver needed was the ODBC driver 13 on the service machine. The confusing part was that the services didnt complaint because they use an existing sqlncli driver instead, but these drivers will not give support to Datetime2(7) which was introduced on the later sql versions.

Also no idea why it will automatically surround the connections with implicit transactions. That part continues to be a mistery but at least it doesnt happen now that we use the proper driver.

So there you go, hopefully this post will save someone the hours of troubleshooting we used on this.

Have a great weekend, like the one we will have now. :slight_smile: