UPDATE - SQL Server Native access weird problem

Greetings,

Since WinDev 23 I have been using SQL Server Native Access with no problem. Today one machine and only one stopped being able to access SQL Server. It gets Error Number 117. followed by 80004005, Client unable to establish connection. SQL State 08001, error 22.

Uninstalling, rebooting and installing again makes no difference.

Then sub-error #1 says SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.

The same computer is able to access the SQL Server with their Visual Manufacturing application. So the error must be in WinDev somewhere. I have checked for quarantined WD....dll and can't find anything. All the same dlls seem to be installed as on computers that do work.

Two questions:
1- What could be wrong with WinDev?
2- How can I make a version that does not include Native Access for just this machine?

Jeff Graham
Cascade Consulting



Edited 2 time(s). Last edit at 03/05/2019 04:56PM by Jeff Graham.

Hi Jeff,

You could either use an INI file to control which access method or even control via using separate configurations. I do this in my system as some clients use native access

Long story short, the bolded part below is only difference


CASE "OLEDB"



HDescribeConnection("MyConnection", V_User,V_Password,V_Datasource,V_Databasename, hOledbSQLServer, hOReadWrite,"")

gsDBConnectType="SQL Server OLEDB"

CASE "Native Access"


gsDBConnectType="SQL Server Native Access"

HDescribeConnection("MyConnection", V_User,V_Password,V_Datasource,V_Databasename, hNativeAccessSQLServer, hOReadWrite,"")



Edited 1 time(s). Last edit at 02/23/2019 03:17AM by Hilltopper.

Thanks for the suggestion.

Hi

Hmm... I guess you have to use 32bit driver from
SQL Server 2000 - SQL Server - SQLSRV32.DLL
and install wd23-32bit Native driver into 32bit ODBC driver manager in windows C:\Windows\SysWOW64

and check it out @ C:\Windows\SysWOW64\odbcad32.exe, not sure 64-bit works out @ yours and
test with ODBC conn + 32 bit conn. If works, then compile yr wdApps in 32-bit env.

ps SQL Server Native Client 11.0 won't work for sqlServer2000.
and also look at native driver in wd that fits in sqlServer2000.

HTH

Cheers

King

What is the version of sql server you are connecting? You can check it with select @@version

SQLNCLI 11 can't connect to old version of sql server (<= 2000), for that you must use DBNETLIB or put WD CLIENT VERSION=2000 in the EXTENDED properties of your connection.

Regards

Paulo Oliveira

We are using SQL Server 2005. We have found that there was an update from Microsoft for SQL2014 that happened. We are trying to determine if that is the cause. Why it happened I don't know. I don't have access to the machine due to snow storms at the client's location,

Another weird thing is that I tried to just change the connection..provider on the Connection for SQL and that does not seem to affect the issue and timing tests show it runs about the same with either setting.

So I tried using a separate Connection and do an HChangeConnection but that connection will not open in code, it does with the test button in the analysis. Still trying to figure out why. There are no files assigned to the OLEDB connection at compile time will try that next.

The technical support person at the client was able to get to the machine with the problem. He found a newer SQL Server had been installed locally.. When that was removed and the standard server access client reinstalled, then all worked properly. Now the problem has shown up on two other machines that have various SQL Server newer versions installed.

Of possible interest for those using WinDev Native SQL Server driver, here are some issues I had trying to implement HillTopper's suggestion above. First, I added an option to the INI file to set the connection provider to OLEDB. That works but when I did timing tests, tt was as fast or faster than the NATIVE! So I then created a separate connection using the Microsoft OLEDB that I used before we bought the Native driver. It tested fine in the connection editor. However when I did hChangeConnection for the SQL Server files to the new OLEDB connection, it would not open.

Testing continues.

As i told you before you can use the WD CLIENT VERSION in the extended information of the connection to force the use of the SQLNCLI version for a specific mssql version as long as that SQLNCLI version is installed on the PC.

[doc.windev.com]

Regards

Paulo Oliveira

Hi Paulo,

Thanks for the info. It took me some time to figure out how to implement the extended information. Once I did and I found the correct parameter, the native driver started connecting again. I added an ini parameter to allow individual machines to use different values..For those interested, here is the syntax I used.

gsSQLServerVersion is string =INIRead("Server", "SQLServerVersion", "2005", gsIniFile)
// [doc.windev.com]
SQL_Server..ExtendedInfo += ";WD CLIENT VERSION="+gsSQLServerVersion

Ini file entry:
[Server]
# Change version below if SQL Server or other client is installed - Should be one of 2005, 2008, 2012 if not specified, it defaults to 2005
SQLServerVersion=2005

Jeff Graham
Cascade Consulting