Connecting to MSSQL server with a linked server

General Discussion
1

Hello, I'm trying to connect to a MSSQL server which is connected to our ERP system.
Since our ERP uses ODBC for connection I've set up a linked server in SSMS which works fine, although when I'm trying to run a query Ignition returns error -155.

Error from Designer:

Error running query: SELECT * FROM OPENQUERY([Server], 'SELECT * FROM [schema].[table]') WHERE status = 3 Unknown SQL type: -155

2

There is something in that linked server that is using a column type that your MS JDBC driver doesn't support. Are you using the latest JDBC driver for your java/sql server combination?

3

I'm running Ignition 8.1.4, with the JDBC driver 9.4.0. Would an upgrade possibly resolve the issue?

4

Maybe, but be aware that upgrading Ignition doesn't upgrade the JDBC driver. That would be a separate operation (that you can do now).

5

I tried updating the driver to the latest file from MS (12.8.1.0 java 11), but that resulted in an connection error to the database, so I had to go back to the original. Is there a way to set up a new database connection directly to my linked server?

6

Instead of doing a SELECT * list your columns and CAST the problematic column.

3 Likes
7

THIS WORKS!!! Thank you very much :smile:

2 Likes
8

If you're querying data from linked servers in MSSQL I would recommend putting your queries in stored procedures so future JDBC driver changes can't impact functionality.