MS SQLEXPRESS Driver Connection String

[kwpsd]

Version: 6.1.0 - build 1418

Has anyone successfully connected to a Microsoft SQLEXPRESS server using the Microsoft driver (not the jtds driver)? If so, would you mind sharing the connection string?

I used Microsoft SQL Server Management Studio to verify that the TCP connection works over our network (I use the SQLEXPRESS 'sa' user account for this). However, I cannot connect from within Developer. Most permutations of the connection strings that I tried resulted in Developer just hanging, thereby forcing a restart. I have tried all the connections strings I could find on the forum to no avail. Nor could I find anything that explicitly states what the string is...just approximations.

I have exhausted my knowledge in this area and need some expert advice. Any help would be most appreciated!

[juan.cristobo]

Why do you want to use MS driver instead of jtds?

[kwpsd]

Hi, Juan.

The jtds driver development has not kept us with changes from Microsoft (most notably, see https://www.servoy.com/forum/viewtopic.php?f=4&t=17597).

We attempted to use the Microsoft driver last January at one of our customer's location on a full-blown MSSQL installation but could not get it to work. We ended up using the jtds driver, so we know that works. We started work on a new project and thought we would re-visit the Microsoft driver issue. Articles on the Forum suggest others have, at least, attempted to use the Microsoft driver, and we were hoping someone who succeeded could assist us.

When the Microsoft driver connection fails, Developer hangs and, if you click on Developer when in this state, the Developer screen turns a transparent white, and we have to use the Windows Task Manager to kill the process. There is nothing in the log file, so debugging becomes a guessing game.

We are still looking for that connection string.

[omar]

I understand the datetime / datetime2 incompatibility has been solved in Servoy 6.0.x? Besides that if you do the changes on the SQL Server side and use the datetime column and synchronize the database information you slould also be able to get it working the way you want?

[kwpsd]

Hi, Omar.

That's exactly what we did at the customer's installation last January...a real pain, because, at the time, we were unaware of the datetime2 incompatability, and when we imported our solution, all datetime columns were created as datetime2 columns, and jtds does not handle datetime2 structures (thus causing our solutuion to fail to run). And, you may be correct that Servoy has fixed this issue on the import.

However, we would like to take advantage of the datetime2 column structure for our current project, and jtds does not yet provide support for datetime2. That's why we are asking if anyone has successfully used the Microsoft JDBC driver.

It's supposed to be simple, right? Just drop the JDBC driver into the 'drivers' folder, set the connection string, and you're connected. But, not in our case. We think it has something to do with the connection string nomenclature; for example, when using Studio to connect, we have to use '.\SQLEXPRESS' in the path (note the period before the backslash). In the Microsoft JDBC connection string, we tried with and without the period but could not get a connection either way.

We are hoping someone can provide the proper connection string syntax.

[omar]

I believe the connection string syntax should be:

Code: Select all

jdbc:sqlserver://server:port;DatabaseName=dbname

where server can be the server name or an ip-address. The port number is most likely 1433.

[kwpsd]

Thank you for the string format; it is nearly the same as the one Servoy displays by default:

Code: Select all

jdbc:sqlserver://localhost:1433;DatabaseName=<database_name>;SelectMethod=cursor

Empirically, I did find some measure of success with the following connection string:

Code: Select all

jdbc:sqlserver://localhost:1433;DatabaseName=SQLEXPRESS/ucsdhc_recharge;SelectMethod=cursor

I now get a 'login failed' message instead of Developer hanging (Windows 7 spinning blue ring), when using the 'sa' account:





Using Studio and the same 'sa' account and password credentials, I can log into SQLEXPRESS from any computer on our network:





Perhaps, one of the Servoy engineers could comment?

[omar]

Hi Kim,

You should add the username and password to the connectionstring if you are using standard security:

Code: Select all

...; user=UserName;password=Password;


[kwpsd]

I tried appending the username and password to the connection string, but the connection still fails. I get one of two error messages with each connection attempt:

Cannot set up server: Cannot open database "SQLEXPRESS/ucsdhc_recharge" requested by the login. The login failed.

or

Cannot set up server: Connection reset

If I blank out the username and password fields, I get a thrid error message:

Cannot set up server: login failed for user ''.

EDIT: Are you certain about appending the credentials to the connection string? When I use the jtds driver to connect to SQLEXPRESS, it takes the username and password from the fields and not the connection string.

[juan.cristobo]

Try this:

Code: Select all

jdbc:sqlserver://localhost:1433;DatabaseName=ucsdhc_recharge;instanceName=SQLEXPRESS;SelectMethod=cursor

[lvostinar]

Version: 6.1.0 - build 1418

Has anyone successfully connected to a Microsoft SQLEXPRESS server using the Microsoft driver (not the jtds driver)? If so, would you mind sharing the connection string?

I used Microsoft SQL Server Management Studio to verify that the TCP connection works over our network (I use the SQLEXPRESS 'sa' user account for this). However, I cannot connect from within Developer. Most permutations of the connection strings that I tried resulted in Developer just hanging, thereby forcing a restart. I have tried all the connections strings I could find on the forum to no avail. Nor could I find anything that explicitly states what the string is...just approximations.

I have exhausted my knowledge in this area and need some expert advice. Any help would be most appreciated!

My connection string (from servoy.properties) is:

server.0.URL=jdbc\:sqlserver\://localhost\\SQLEXPRESS\:1433;DatabaseName\=cloud;SelectMethod\=cursor

[omar]

I got it to work with SQL Server 2012 using the following connection string (translated to your situation):

Code: Select all

jdbc:sqlserver://tcp:KIM-VAIO\SQLEXPRESS;DatabaseName=ucsdhc_recharge;SelectMethod=cursor;


I don't see why the connection to SQL Server 2012 should be any different from SQLEXPRESS. I trust you have enabled standard security in the server properties?

I downloaded the JDBC SQL driver from Microsoft.