SQL error 145 querying a sqlexpress database

[Scott_Kay]

When ever I query the database and try to expand a entry in the database I get a sql query error 145. SQLunknown SQL type -145. The database shows a valid connection. Not sure what to do to solve this.

Database is a sqlexpress database

[pturmel]

Show your SQL query. Tell us the version numbers of everything involved, including the JDBC driver. Show a screen shot of the data before and after you try to expand it.

[Scott_Kay]

Just trying to expand it when opens I get the error

[Scott_Kay]

Just trying to browser the table

[Scott_Kay]

[pturmel]

Very likely there’s a column type in that table that isn’t supported by your JDBC driver.

{ For text, like error details or code, don’t use screenshots. Paste the raw text, highlight what you pasted, then click the “preformatted text” button. This one: </>. }

Try using SQL Server Management Studio to generate that table’s “create script” and paste it here.

[pturmel]

This topic might help, too:

SQL Server Connection Faulted

You might need to downgrade. The case I encountered was a SQL Server 2008, which isn’t supported past driver 6.2. See this compatibility chart: I think with the correct driver, I didn’t need the extra connection properties, just the java.security changes. YMMV, of course.

But note that the oldest JDBC drivers don't work in java 11, IIRC.

[Scott_Kay]

Create script

USE [Department505]

GO

/****** Object: Table [dbo].[AssemblyLineTransactions] Script Date: 12/30/2021 7:00:03 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[AssemblyLineTransactions](

[ItemGuid] [uniqueidentifier] NOT NULL,

[ROWID] [int] IDENTITY(1,1) NOT NULL,

[StationId] [int] NOT NULL,

[DateEntered] [datetime] NOT NULL,

[TransactionTypeId] [int] NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AssemblyLineTransactions] ADD CONSTRAINT [DF_Department505AssemblyLineTransactions_ItemGuid] DEFAULT (newid()) FOR [ItemGuid]

GO

ALTER TABLE [dbo].[AssemblyLineTransactions] ADD CONSTRAINT [DF_Department505AssemblyLineTransactions_DateEntered] DEFAULT (getdate()) FOR [DateEntered]

GO

[pturmel]

A bit of googling suggests it might be the uniqueidentifier column type. Check your JDBC driver against the compatibility matrix in the link I posted.

{ Please edit your comment to mark pasted SQL as preformatted text. }

[Scott_Kay]

I don’t see anything on the Microsoft site that discusses data type compatibility and the driver, maybe I am looking in the wrong place. I see database compatibility table.

[Scott_Kay]

Here is the link I am looking at Support matrix - JDBC Driver for SQL Server | Microsoft Docs

[pturmel]

Yes, that’s what I’m talking about. I want you to verify that your install of Ignition is using the latest possible JDBC driver for the database version you are connecting to. If it still doesn’t work after that, you will have to experiment with your tables, SELECTing column by column, until you find the one Java doesn’t accept. Omit those columns from future SELECT operations, or change your tables to not use them.

Or change your database to one that plays better with Java. I’d switch to PostgreSQL, fwiw.