Participating Frequently

Question

Coldfusion 10 cannot connect to 2016 Microsoft SQL Server

Forum|Forum|5 years ago
April 2, 2020
3 replies
3247 views

Hello there.

I cannot get my datasource connected to a Windows 2016 SQL Server from my CF 10 Enterprise.

I've updated to the latest patches.

I think I have to download the MS java db drivers for it to connect, correct?

Getting started

This topic has been closed for replies.

BKBK

Community Expert

Your being unable to run Windows 2016 SQL Server and CF 10 Enterprise may be a blessing in disguise. As @Dave_Watts says, ColdFusion 10 only has support for MS SQL Server 2005, 2008 and 2012. You can see this for yourself in the ColdFusion 10 Support Matrix.

If I were you, I would count myself lucky and stop right here. Better than carrying on to a full-blown application, with SQL Server 2016 and CF seemingly working fine, only to have everything crashing down around your ears later.

D

Dave Watts

Community Expert

I don't think you need the Microsoft drivers. They may work better as Jamo indicated, but I think you should still be able to connect to newer versions of SQL Server from the built-in drivers even if they're not listed as supported.

What actually happens when you try to connect? What error are you getting, if any?

Dave Watts, Eidolon LLC

James Moberg

Inspiring

Is there any information available regarding the SQL driver versions that are used in the most recent versions of CF10, 11, 2016, 2018 and 2020? (I'm not sure how to find this info.)

For example, I'm using CF2016 and I see this file: "\cfusion\db\slserver54\DataDirect.url"

and it references "http://www.datadirect-technologies.com"

This website doesn't appear to be online anymore. DataDirect was acquired in 2003 and has been redirecting requests to datadirect.com since May 2004. Since April 2014, the URL has been redirecting to progress.com... so why it this resource URL so outdated and using a pre-2005 URL in the source library for CF2016? The sub-directory is titled "slserver54". Does this mean that the DataDirect version for CF2016 is 5.4? (How can this be correctly identified?)

If I go to Progress/DataDirect's driver page for Microsoft Server, I see that their current version is 8.0.2.
https://www.progress.com/odbc/release-history/microsoft-sql-server-odbc

The latest 8.0.2 version is certified to work w/SQL 2019 and on Windows Server 2016.

The freely available Microsoft JDBC 7.4 & 8.2 drivers both work with MSSQL 2019. The max JDBC driver for JRE 1.7 (ie, CF9) is the 6.2 or 6.4 driver depending on whether you need to connect to SQL2008 or not.

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15#sql-version-compatibility

D

Dave Watts

Community Expert

That directory is for the ODBC Agent/Server stuff. I suspect that stuff hasn't been updated in some time. The JDBC drivers are within \cfusion\lib\adobe_drivers.jar, I think. If you copy that file somewhere else and extract it, you can probably find out what version of JDBC drivers are included.

All that said, in my experience older JDBC drivers often still work with newer versions of MS SQL Server, even if they're not listed as "supported". Officially, CF 2018 supports MS SQL Server 2014, 2016 and 2017. CF 2016 supports MS SQL Server 2012, 2014 and 2016. CF 10 supports 2005, 2008 and 2012. But I wouldn't be surprised if you see forward and backward compatibility with SQL Server for general SQL CRUD query use.

Finally, while I've seen situations where the Microsoft JDBC drivers work better, I've also seen the opposite. So I would personally hesitate about one being better than the other in all cases.

Dave Watts, Eidolon LLC

James Moberg

Inspiring

In my opinion, yes. I recommend installing the official Microsoft JDBC SQL drivers. I've used them with both CF10 & 2016 and haven't experienced any negative issues.

Here are the articles that helped me get it configured.

https://www.hass.de/content/coldfusion-10-macromedia-database-drivers-leaking-memory

https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-server

We're currently using v7.2.2 w/JRE 11, but I see that v8.2 is the current version. (I'll have to download and test this.):

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

Charlie Arehart

Community Expert

Thanks for that, James. Are you confirming you used that ms driver with cf10 and *sql server* 2016? Or did you mean perhaps cf10 and *cf* 2016? If so, what was the sql version used with cf10?

/Charlie (troubleshooter, carehart. org)

James Moberg

Inspiring

I meant "ColdFusion 10" and "ColdFusion 2016". (I believe that we may also used the free MSJDBC drivers with "ColdFusion 9".)

We've been able to connect to SQL Server 2008, 2012, 2016 & 2017. (I just tested 2017.)

Charlie, have you explored using the JDBC connector?

We've been using it since CF9/10 and found it to perform better/faster. You can run it along side your regular DSN. All you need to do is add the JAR to your path, configure a DSN and compare the queries. We initially configured it so that only developers would use the connector. I don't recall having to modify any CFML or SQL code when switching.

We explored the use of the JDBC Driver because we are required to use the latest versions of Microsoft software and couldn't convince the client to upgrade ColdFusion. A project required finding the closest locations to a user and we needed to use the SQL geometry data type, but the CF version we used at the time didn't natively support the SQL Server that had the geography feature. We added the JDBC driver and we were able to communicate with the SQL server without any problems.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded