MS SQL 2017 Driver and URL?

Questions and answers regarding general SQL and backend databases

MS SQL 2017 Driver and URL?

Postby bcusick » Fri Aug 10, 2018 7:57 pm

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)
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: MS SQL 2017 Driver and URL?

Postby kwpsd » Sun Aug 12, 2018 10:55 pm

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
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 653
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: MS SQL 2017 Driver and URL?

Postby bcusick » Mon Aug 13, 2018 6:30 pm

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...
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: MS SQL 2017 Driver and URL?

Postby patrick » Tue Aug 14, 2018 9:48 am

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...
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3692
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: MS SQL 2017 Driver and URL?

Postby marco.rossi » Wed Jul 31, 2019 4:27 pm

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?
Marco Rossi
Senior Analyst Developer
Freelance Consultant

IT Manager @Mantho
Webmaster @Sitoliquido
marco.rossi
 
Posts: 101
Joined: Sun Apr 12, 2015 9:33 pm

Re: MS SQL 2017 Driver and URL?

Postby kwpsd » Thu Aug 01, 2019 1:54 am

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.
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 653
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 2 guests