Forum Discussion

jkrolczy's avatar
jkrolczy
Regular Contributor
6 years ago
Solved

TestComplete: Any issues know for connecting to SQL 2016

All,

 

I have a TestComplete automation fwk where I use the SQL Native Client and ADO for accessing SQL DBs.

This has worked for the past 9+ years for my auto fwk approach.

 

Connecting to an SQL Server instance

 

The syntax of specifying the server instance in the value of the server key is the same for all
connection strings for SQL Server.

 

Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

  or

Provider=SQLNCLI11;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;

 

This works for me when accessing a SQL 2008 server or SQL 2014 server. 

Some of our SQL DB servers are being upgraded to SQL 2016 and now when using the

same connection string I get the following error msg:

 

'TCP Provider: An existing connection was forcibly closed by the remote host'

 

This comes from when execution the the cmd.

 

RecSet = Cmd.Execute();

 

Anyone ever experience this issue ?

 

I would think the same connection string I am using would still be valid for access to  SQL 2016 ?

 

I have tried this on TestComplete 12.42, 12.60

I have used SQLNCLI10, SQLNCLI11

 

Just trying to isolate the issue.

 

I think maybe the newly created SQL 2106 server has a wrong configuration setting ?

or

Maybe my connection string is invalid for SQL 2016 use?

 

Any help would be much appreciated !

 

Thanks,

JamesK

 

  • jkrolczy's avatar
    jkrolczy
    6 years ago

    Solution found!!!

    It was NOT a SQL Server issue nor connection string issue:

     

    The normal SQL Native Client 11.0 deployed and used is:

     

                 SQL Server Native Client 11.0  2011.110.2100.60

                 or

                 SQL Server Native Client 11.0  2011.110.3000.0

     

    In testing my connection string against the above SQL Server Native Client 11.0 

    version, the server does not get recognized correctly with either above versions.

     

    If you install SQL Server 2016, SQL Server Native Client 11.0  version 2011.11.0.65xx.x

    also comes along and gets install.  For one of my developers, this allows the SQL Server

    Native Client 11.0 connection string test to work for him. 

    So now we narrow down the issue to driver version.

     

    Unaware of any of the latest version of SQL Server Native Client 11.0,

    searching on the internet I came across:

     

    https://www.microsoft.com/en-us/download/details.aspx?id=50402

    Microsoft SQL Server 2012 Native Client - QFE (Quick Fix Engineering) – a patch/hotfix

     

    When installed, it is the following version:

                   SQL Server Native Client 11.0  2011.110.7001.00

    higher then what comes with SQL 2016 Server. 

    So the hotfix must have come from SQL 2016 breaking other users like myself.

     

    In manually testing my connection string against the above SQL Server Native Client 11.0 

    version, the server does get recognized correctly and the connection test PASSES.

     

    Testing in automation using TestComplete now works as well for that testing

    using SQL Server Native Client 11.0  2011.110.7001.00.

     

    So from what all that was explained above, automation is now ok with SQL 2016.

    The appropriate QFE will be applied accordingly and connection string modified

    for SQLNCLI11 use.

     

    Regards,

    JamesK

2 Replies

  • AlexKaras's avatar
    AlexKaras
    Champion Level 3

    Hi James,

     

    Haven't seen you here for a long time...

     

    > Maybe my connection string is invalid for SQL 2016 use?

    Examples of connection strings can be seen, fer example, here: https://www.connectionstrings.com/sql-server-2016/

    To verify correctness of the connection string for ADO I used to use .udl files, setup connection via their UI and use obtained connection string. https://community.smartbear.com/t5/TestComplete-Functional-Web/Can-anyone-help-me-how-to-access-Mongo-database/m-p/86460#M20415

    This gives me confidence that connection string itself is correct and I need to check the code if something does not work.

    Note, bitness of ADO provider must match bitness of TestComplete. Likewise, if you are using 32-bit TestComplete on 64-bit machine, you must use 32-bit .udl wizard. This can be achieved with the following command executed from the command prompt (one line):

    C:\Windows\SysWOW64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile "drive:\path\to\your.udl"

     

    • jkrolczy's avatar
      jkrolczy
      Regular Contributor

      Solution found!!!

      It was NOT a SQL Server issue nor connection string issue:

       

      The normal SQL Native Client 11.0 deployed and used is:

       

                   SQL Server Native Client 11.0  2011.110.2100.60

                   or

                   SQL Server Native Client 11.0  2011.110.3000.0

       

      In testing my connection string against the above SQL Server Native Client 11.0 

      version, the server does not get recognized correctly with either above versions.

       

      If you install SQL Server 2016, SQL Server Native Client 11.0  version 2011.11.0.65xx.x

      also comes along and gets install.  For one of my developers, this allows the SQL Server

      Native Client 11.0 connection string test to work for him. 

      So now we narrow down the issue to driver version.

       

      Unaware of any of the latest version of SQL Server Native Client 11.0,

      searching on the internet I came across:

       

      https://www.microsoft.com/en-us/download/details.aspx?id=50402

      Microsoft SQL Server 2012 Native Client - QFE (Quick Fix Engineering) – a patch/hotfix

       

      When installed, it is the following version:

                     SQL Server Native Client 11.0  2011.110.7001.00

      higher then what comes with SQL 2016 Server. 

      So the hotfix must have come from SQL 2016 breaking other users like myself.

       

      In manually testing my connection string against the above SQL Server Native Client 11.0 

      version, the server does get recognized correctly and the connection test PASSES.

       

      Testing in automation using TestComplete now works as well for that testing

      using SQL Server Native Client 11.0  2011.110.7001.00.

       

      So from what all that was explained above, automation is now ok with SQL 2016.

      The appropriate QFE will be applied accordingly and connection string modified

      for SQLNCLI11 use.

       

      Regards,

      JamesK