# Unknown SQL type: -151 When SQL Query returns a datetime **URL:** \ **Category:** Ignition\ **Created:** [January 28, 2020, 3:13pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677 "2020-01-28T15:13:39Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 28, 2020, 3:13pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/1 "2020-01-28T15:13:39Z")
I’m trying to get some of our v7.9.12 vision screens to run on v8.0.7. I’m getting “Unknown SQL type: -151” exceptions whenever a SQL query is to return a “datetime”. Has anyone ran into this? Background: This Gateway has been running for a while with Perspective only. No SQL queries that return a “datetime”. We recently purchased the Vision Module for it so we can transition from version 7 to version 8 without risk of downtime. Here is the Console output: 08:56:50.359 [AWT-EventQueue-0] ERROR com.inductiveautomation.factorypmi.application.binding.AbstractPropertyAdapter - null java.lang.Exception: Error running query: SQLQuery(query=SELECT [Period] ,[PeriodEnd] ,[PeriodStart] ,[Year] FROM [PeriodCalendar], database=ProductionData)@0ms On: SQL Unknown Type.Root Container.Table.data at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:309) at java.base/java.lang.Thread.run(Unknown Source) Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayException: Unknown SQL type: -151 at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341) at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:315) at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.sendMessage(GatewayInterface.java:268) at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:843) at com.inductiveautomation.ignition.client.gateway\_interface.GatewayInterface.runQuery(GatewayInterface.java:814) at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:50) at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:657) at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:306) … 1 common frames omitted Caused by: com.inductiveautomation.ignition.client.gateway\_interface.GatewayResponse$GatewayThrowable: Unknown SQL type: -151 at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:118) at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68) at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:411) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:844) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:544) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1581) at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1307) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:482) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1549) at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1204) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59) at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127) at org.eclipse.jetty.server.Server.handle(Server.java:494) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:374) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:268) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171) at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129) at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:367) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:782) at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:918) at java.lang.Thread.run(null) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [January 28, 2020, 4:58pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/2 "2020-01-28T16:58:19Z")
Wild shot in the dark, but as someone who moved a 7.9 project to 8.0, is the database your querying set correctly in the tag? I see you are using `database=ProductionData`, is that what the new database you have configured is called on your gateway? I ask because it looks like your query is returning a null value, is that what’s happening now? ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 28, 2020, 6:03pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/3 "2020-01-28T18:03:28Z")
I don’t believe that is the issue, because if I cast the datetime as a varchar in the same Query it returns values. Query that works: ‘’‘SELECT [Period] ,cast([PeriodEnd] as varchar) as ‘PeriodEnd’ ,cast([PeriodStart] as varchar) as ‘PeriodStart’ ,[Year] FROM [PeriodCalendar]’’’ Query that doesn’t work: ‘’‘SELECT [Period] ,[PeriodEnd] ,[PeriodStart] ,[Year] FROM [PeriodCalendar]’’’ ---
**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\ **Post date:** [January 28, 2020, 6:22pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/4 "2020-01-28T18:22:02Z")
Is the actual datatype datetime or datetime2? ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 28, 2020, 6:24pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/5 "2020-01-28T18:24:55Z")
SQL Server 2005, datetime ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 29, 2020, 8:08pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/6 "2020-01-29T20:08:59Z")
**Some new info:** This only seems to be a problem if I’m querying our SQL Server 2005 database. SQL Server 2014 and MySQL seem to work fine. However most of the data I need is on the 2005 server. Did anything change in the database driver between 7.9.12 and 8.0.7? Making it incompatible with SQL Server 2005. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 29, 2020, 8:19pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/7 "2020-01-29T20:19:06Z")
> [@elmer.pannell](#): > > Did anything change in the database driver between 7.9.12 and 8.0.7? Making it incompatible with SQL Server 2005. If you did an upgrade it should be the same JDBC driver, but maybe if you did a fresh install of 8 it could be a newer one. ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 29, 2020, 9:46pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/8 "2020-01-29T21:46:02Z")
It looks like I have JDBC 4.2 compliant driver on the 8.0.7 Gateway. And JDBC 4.0 compliant driver on the 7.9.12 Gateway. Anyone know if 4.2 compliant driver works with SQL Server 2005? If not how do I fix it. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 29, 2020, 10:21pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/9 "2020-01-29T22:21:02Z")
What’s the driver version though? It’s probably not good that Microsoft doesn’t even list SQL Server 2005 in its compatibility matrix: [https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15](https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15) ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 30, 2020, 1:11pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/10 "2020-01-30T13:11:39Z")
How do I find the driver version? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 30, 2020, 1:15pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/11 "2020-01-30T13:15:01Z")
Huh, that’s annoying. I thought it was listed somewhere in the gateway. You can look at the filename of the driver in rhe `$IGNITION/user-lib/jdbc` folder I guess. The SQL Server JDBC JAR that came with a fresh install of Ignition 8.0.7 is version 7.2.1. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 30, 2020, 1:22pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/12 "2020-01-30T13:22:16Z")
Try uninstalling the more recent JDBC driver and install the old version you have on 7.9, which is probably version 4.1. An upgrade would have left the old driver in place. ---
**Author:** ![elmer.pannell](https://avatars.discourse-cdn.com/v4/letter/e/a88e57/32.png) [@elmer.pannell](https://forum.inductiveautomation.com/u/elmer.pannell)\ **Post date:** [January 30, 2020, 3:12pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/13 "2020-01-30T15:12:21Z")
That did the trick! Thank you ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 30, 2020, 3:38pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/14 "2020-01-30T15:38:01Z")
Great, count yourself a little lucky here. You need to seriously consider getting that SQL Server 2005 instance upgraded because 1) you can’t connect to newer versions with this old driver and 2) this old driver may stop working in some future Java version or other Ignition update if a newer version of JDBC becomes a requirement. This technical debt could be a serious liability. ---
**Author:** ![Kiriakos.Kalpakidis](https://avatars.discourse-cdn.com/v4/letter/k/f19dbf/32.png) [@Kiriakos.Kalpakidis](https://forum.inductiveautomation.com/u/Kiriakos.Kalpakidis)\ **Post date:** [February 13, 2020, 11:20pm UTC](https://forum.inductiveautomation.com/t/unknown-sql-type-151-when-sql-query-returns-a-datetime/31677/15 "2020-02-13T23:20:50Z")
I have just found myself in a situation where I have a 2005 SQL Server Express install that exists as a stand alone system. I was hoping to use a series of transaction groups to replicate the data into our main production system (2019), but I ran into this same incompatibility.