Page 1 of 1

MS SQL 2017 Driver and URL?

PostPosted: Fri Aug 10, 2018 7:57 pm
by bcusick
Hey Guys,

Has anyone had any success in connecting to a SQL Server 2017 database?

I downloaded the 7.0 version of the Microsoft JDBC driver, and according to the MS Site the configuration URL is:
Code: Select all
jdbc:microsoft:sqlserver://<server>:<port>;DatabaseName=<databaseName>


(Reference: https://docs.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?view=sql-server-2017)

The Driver is:
Code: Select all
com.microsoft.sqlserver.jdbc.SQLServerDriver


When I try to connect to a valid database - I get this error:
Code: Select all
Driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver' does not accept url 'jdbc:microsoft:sqlserver://10.14.80.107:1433;DatabaseName=Servoy_PROD_Respository'


Anyone have any ideas?

Bob

Java: 1.8.0_515_b12
Platform: Windows Server (NT 6.2)

Re: MS SQL 2017 Driver and URL?

PostPosted: Sun Aug 12, 2018 10:55 pm
by kwpsd
Hi. Bob.

No experience with MSSQL 2017 andjdbc 7.0. However, here is what we use for MSSQL 2016 and jdbc 4.2, and it works:

Code: Select all
jdbc:sqlserver://10.14.80.107:1433;DatabaseName=Servoy_PROD_Respository;SelectMethod=cursor

Re: MS SQL 2017 Driver and URL?

PostPosted: Mon Aug 13, 2018 6:30 pm
by bcusick
Thanks, Kim!

Still unable to get it to work right - but it's OK - I think I'll have them downgrade the DB to 2014 or 2016...

Re: MS SQL 2017 Driver and URL?

PostPosted: Tue Aug 14, 2018 9:48 am
by patrick
I'm running a SQL Server 2017 and connect to it just fine.

Your URL is odd. Where did you get that from? If you just create a new database connection from within Servoy using the MS driver, you get this kind of URL (as stated in the docs you link to):

Code: Select all
jdbc:sqlserver://localhost:1433;DatabaseName=<database_name>;SelectMethod=direct


Please note the SelectMethod=direct. In the past the default was "cursor", but that is probably less efficient for a Servoy application.

Make sure you download the latest JDBC driver and remove potentially old drivers from Servoy's driver dir. The latest driver should be available here:

https://www.microsoft.com/en-us/downloa ... x?id=57175

I think getting the connection right is a way easier task than downgrading the DB...

Re: MS SQL 2017 Driver and URL?

PostPosted: Wed Jul 31, 2019 4:27 pm
by marco.rossi
Hi all,
I'm facing a similar problem:

I have a machine where I installed MSSQL 2017, Servoy 2019 and JasperServer 7.

JasperServer works without problem with MSSQL but Servoy 2019 has troubles when I use the SmartClient (With NGClient and NGClient desktop it works fine).
I have a solution that onLoad event executes some queries. The same solution with the same queries works properly on NGClient And NGClientDesktop but has troubles with SmartClient.

With SmartClient the problem is that databaseManager.getDatasetBySQL returns always an empty dataset.

The JDBC connections show the table list properly.

Any idea?

Re: MS SQL 2017 Driver and URL?

PostPosted: Thu Aug 01, 2019 1:54 am
by kwpsd
Please note the SelectMethod=direct. In the past the default was "cursor", but that is probably less efficient for a Servoy application.


@Patrick: Just re-read this thread (a year later) and caught your note regarding the 'direct' vs. 'cursor' settings. I do not recall seeing the note before but wanted to let you know we made the changeover. Belatedly...thanks for pointing this out.