Trying to connect MS SQL Server 9 (2005) from IRIS Community through JDBC
.png)
I cannot get any further hint from the error message in red in this screenshot. Is there any clue or details about the connection failure that I could look for?
Comments
You can enable SQL Gateway Logging. Also make sure that the necessary JDK/JRE version is installed and configured correctly.
For simplicity, I would check the connection outside of IRIS, for example through DbVisualizer/DBeaver/SQuirreL/etc.
Thanks, Vitality. I got these error messages captured in the log file of the SQL Gateway Logging. It's good to see something new now.
Invalid Message Count (452); expected: -1 got: 1. Server returned: 19801. Closing Connection
Double check that the JDBC 12.2.0 driver has the appropriate permissions, and is still compatible with SQL 2005. It doesn't look like that driver is compatible anymore with 2005.
Microsoft JDBC Driver Support Matrix
Might need to use the jTDS driver but it has it own flaws..jTDS Driver
You can try testing the connection via Terminal..https://docs.intersystems.com/iris20232/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&CLASSNAME=%25SYSTEM.SQLGateway by getting the connection and using TestConnection() to see if it will give you a more verbose output or errors to variables.
Hi Scott,
I tried to test the connection to MS SQL Server 2005 with JDBC by a third-party SQL tool SQuirrel. Having the same connection string set up as the one in IRIS, using the same JDBC jar file, yet with a recent JDK (openjdk version "18.0.2" 2022-07-19), I was able to connect to the database of the target MS SQL Server instance.
Now I'm trying to figure out whether the JDK version made the differences. I didn't mention that in order to make it work, I had to modify the file java.security according to some posts (https://stackoverflow.com/questions/69623611/how-do-i-allow-java-client…). But this was done before I switched JDK so that should be independent.