# SQL Query type unknown as unknown **URL:** \ **Category:** Ignition\ **Tags:** database, scripting, ignition81, vision, perspective\ **Created:** [January 29, 2025, 8:56pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577 "2025-01-29T20:56:21Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Christopher\_Brunner](https://avatars.discourse-cdn.com/v4/letter/c/e8c25b/32.png) [@Christopher\_Brunner](https://forum.inductiveautomation.com/u/Christopher_Brunner)\ **Post date:** [January 29, 2025, 8:56pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/1 "2025-01-29T20:56:22Z")
Using Ignition 8.1.44 with the latest SQL drivers. Connecting to Microsoft SQL server I have a named Query called Recipe Read. It pulls in values from my SQL server. When I run RecipeRead from the testing area of the query builder - the ignition server has no quarentine items. I run Recipe Read by way of a pushbutton - and I get an error for "cannot convert UNKNOWN to UNKNOWN". The most interesting part of the entire situation is that in the SQL quarentine when I look at the XML file, it is also looking for a row that is no longer in the table (timestamp\_ndx - I deleted it because it wasn't used as an index, was never written to - and has previously caused type issues). I tried to start type casting the SQL query - but that did not help. Any thoughts? Here is the button: ```RecipeID values=[] data = system.db.runNamedQuery("RecipeRead" , {"RecipeNumber":RecipeID}) tagPath=['[Triumph_PLC_Tag_Provider]Recipe/Name', '[PLC_Tag_Provider]Recipe/Description', '[PLC_Tag_Provider]Recipe/LaminatorLineSpeed', '[PLC_Tag_Provider]Recipe/Note1', '[PLC_Tag_Provider]Recipe/Note2', '[PLC_Tag_Provider]Recipe/Note3', '[PLC_Tag_Provider]Recipe/Unwind1Tension', '[PLC_Tag_Provider]Recipe/Unwind1Diameter', '[PLC_Tag_Provider]Recipe/Unwind2Diameter', '[PLC_Tag_Provider]Recipe/Unwind2Tension', '[PLC_Tag_Provider]Recipe/RewindTension', '[PLC_Tag_Provider]Recipe/RewindTaper', '[PLC_Tag_Provider]Recipe/RewindCoreDiameter', '[PLC_Tag_Provider]Recipe/PolishFixedSpeed', '[PLC_Tag_Provider]Recipe/PolishRatio', '[PLC_Tag_Provider]Recipe/LaminatorLiftOff', '[PLC_Tag_Provider]Recipe/LaminatorRatio', '[PLC_Tag_Provider]Recipe/LaminatorTension', '[PLC_Tag_Provider]Recipe/InfeedRatio', '[PLC_Tag_Provider]Recipe/InfeedTension', '[PLC_Tag_Provider]Recipe/ImpressDiameter', '[PLC_Tag_Provider]Recipe/ImpressRatio', '[PLC_Tag_Provider]Recipe/ImpressTension', '[PLC_Tag_Provider]Recipe/GravureDiameter', '[PLC_Tag_Provider]Recipe/GravureIdleSpeed', '[PLC_Tag_Provider]Recipe/GravureIdleTorque', '[PLC_Tag_Provider]Recipe/GravureRatio', '[PLC_Tag_Provider]Recipe/GravureTension', '[PLC_Tag_Provider]Recipe/DryerZ1LEL', '[PLC_Tag_Provider]Recipe/DryerZ1ProcTemp', '[PLC_Tag_Provider]Recipe/DryerZ2LEL', '[PLC_Tag_Provider]Recipe/DryerZ2ProcTemp', '[PLC_Tag_Provider]Recipe/ApplicatorDiameter', '[PLC_Tag_Provider]Recipe/ApplicatorIdleSpeed', '[PLC_Tag_Provider]Recipe/ApplicatorIdleTorque', '[PLC_Tag_Provider]Recipe/ApplicatorRatio', '[PLC_Tag_Provider]Recipe/ApplicatorTension', '[PLC_Tag_Provider]Recipe/ChillRatio', '[PLC_Tag_Provider]Recipe/ChillTension', '[PLC_Tag_Provider]Recipe/ChillTemp', '[PLC_Tag_Provider]Recipe/LamTemp', '[PLC_Tag_Provider]Recipe/TreatkW', '[PLC_Tag_Provider]Recipe/DryerZ1StbyTemp', '[PLC_Tag_Provider]Recipe/DryerZ1WebTemp', '[PLC_Tag_Provider]Recipe/DryerZ2StbyTemp', '[PLC_Tag_Provider]Recipe/DryerZ2WebTemp']#, # '[PLC_Tag_Provider]Recipe/RecipeNumber']#, # '[PLC_Tag_Provider]Recipe/RecipeDateTime'] #values = [row [col] for row in system.dataset.toPyDataSet(data) for col in row] for row in range(data.getRowCount()): for col in range(data.getColumnCount()): #system.perspective.print(data.getValueAt(row, col)) values.insert(col,data.getValueAt(row, col)) system.tag.writeBlocking(tagPath,values) type or paste code here ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [January 29, 2025, 8:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/2 "2025-01-29T20:58:26Z")
The full error message would be useful, but the first thing to try is to make sure that the type of the `RecipeID` variable you're passing in is actually what it's supposed to be - as in, if it's expected to be numeric by the database, are you in fact passing a string, or vice versa? ---
**Author:** ![Christopher\_Brunner](https://avatars.discourse-cdn.com/v4/letter/c/e8c25b/32.png) [@Christopher\_Brunner](https://forum.inductiveautomation.com/u/Christopher_Brunner)\ **Post date:** [January 29, 2025, 11:02pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/3 "2025-01-29T23:02:10Z")
I would be happy to grab you the full error message - all I know of is that I see in the SQL Quarantine page in the gateway - would I find it in the gateway logs? The `RecipeID` is numeric (an int4 type) - and the most perplexing is that the script functions, even though I see the error on the gateway when both RecipeRead is operated by way of the pushbutton or the query builder test window. ---
**Author:** ![Christopher\_Brunner](https://avatars.discourse-cdn.com/v4/letter/c/e8c25b/32.png) [@Christopher\_Brunner](https://forum.inductiveautomation.com/u/Christopher_Brunner)\ **Post date:** [January 31, 2025, 1:25pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/4 "2025-01-31T13:25:09Z")
Is this the information you are looking for? ```python java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported. at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:47) at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(Unknown Source) at java.base/java.util.stream.ReferencePipeline$2$1.accept(Unknown Source) at java.base/java.util.stream.Streams$StreamBuilderImpl.forEachRemaining(Unknown Source) at java.base/java.util.stream.ReferencePipeline$Head.forEach(Unknown Source) at java.base/java.util.stream.ReferencePipeline$7$1.accept(Unknown Source) at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(Unknown Source) at java.base/java.util.stream.AbstractPipeline.copyInto(Unknown Source) at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(Unknown Source) at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(Unknown Source) at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(Unknown Source) at java.base/java.util.stream.AbstractPipeline.evaluate(Unknown Source) at java.base/java.util.stream.ReferencePipeline.forEach(Unknown Source) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:189) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:156) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AggregateSink.storeData(AggregateSink.java:165) at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:147) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:233) at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1118) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1725) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:1622) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:1631) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519) at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:519) at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:1104) at com.inductiveautomation.ignition.gateway.history.BasicHistoricalRecord.storeToConnection(BasicHistoricalRecord.java:248) at com.inductiveautomation.ignition.gateway.history.sinks.RecordDatasourceSink.storeDataToDatasource(RecordDatasourceSink.java:41) at com.inductiveautomation.ignition.gateway.history.sf.sinks.AbstractDatasourceSink.lambda$storeToDatasource$3(AbstractDatasourceSink.java:189) at com.inductiveautomation.ignition.common.functional.FragileConsumer.lambda$wrap$1(FragileConsumer.java:45) ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [January 31, 2025, 4:59pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/5 "2025-01-31T16:59:15Z")
Yeah, that's the extra context I was looking for. Unfortunately it doesn't add much; basically just confirms that we're just faithfully reporting the problem MSSQL reported to the MSSQL JDBC driver. > [@Christopher\_Brunner](#): > > I tried to start type casting the SQL query Can you share your query, your table structure, and how you attempted to cast? This `UNKNOWN to UNKNOWN` thing has come up before with MSSQL, and (from searching internal support archives) is usually fixable with some amount of explicit casting. ---
**Author:** ![Christopher\_Brunner](https://avatars.discourse-cdn.com/v4/letter/c/e8c25b/32.png) [@Christopher\_Brunner](https://forum.inductiveautomation.com/u/Christopher_Brunner)\ **Post date:** [January 31, 2025, 7:38pm UTC](https://forum.inductiveautomation.com/t/sql-query-type-unknown-as-unknown/101577/6 "2025-01-31T19:38:08Z")
Well, it turns out that is was a "user" issue. I couldn't understand why the query in quarrentine kept having a link for a column in the sql database that no longer existed (and was no longer being called). I had converted all of my direct access SQL statements over to named queries and gotten rid of the extra tag. The problem was actually a transaction group that I had thought I retired. The transaction group was still gasping life - and causing my SQL transaction errors.