topic Re: Object reference not set to an instance of an object after upgrade to QS Sept 2019 in Connectivity & Data Prep

Object reference not set to an instance of an object after upgrade to QS Sept 2019

chadwatson — Thu, 23 Dec 2021 15:03:14 GMT

We have just upgraded our Qlik Sense environment from the November 2018 Patch 2 to the September 2019 release. A number of our apps are giving me this error now when I attempt to reload the data:

The following error occurred:

Unable to get column information for the fields that are used in the query: Object reference not set to an instance of an object.

The error occurred here:

SQL SELECT DISTINCT PERIOD_VALUE ,PERIOD_START_DT_LAST_12 ,PERIOD_END_DT_LAST_12 ,PERIOD_START_DT ,PERIOD_END_DT ,YEAR_DT ,1 as My_Count ,AS_OF_DATE INTO #DATE_TABLE --drop table #DATE_TABLE FROM (SELECT 'CY' PERIOD_VALUE ,DATEADD(MONTH, -11, PERIOD_END_DT) PERIOD_START_DT_LAST_12 ,EOMONTH(PERIOD_END_DT) PERIOD_END_DT_LAST_12 ,DATEADD(MONTH, -23, PERIOD_END_DT) PERIOD_START_DT ,EOMONTH(A.PERIOD_END_DT) PERIOD_END_DT ,YEAR(PERIOD_END_DT) YEAR_DT ,CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS_OF_DATE FROM (SELECT DATEADD(DAY, -(DAY(GETDATE()) - 1), CAST(GETDATE() AS DATE)) PERIOD_END_DT) A UNION SELECT 'PY' PERIOD_VALUE ,DATEADD(MONTH, -23, PERIOD_END_DT) PERIOD_START_DT_LAST_12 ,EOMONTH(DATEADD(MONTH, -12, PERIOD_END_DT)) PERIOD_END_DT_LAST_12 ,DATEADD(MONTH, -35, PERIOD_END_DT) PERIOD_START_DT ,EOMONTH(DATEADD(MONTH, -12, PERIOD_END_DT)) PERIOD_END_DT ,YEAR(DATEADD(MONTH, -12, PERIOD_END_DT)) YEAR_DT ,CAST(DATEADD(YEAR, -1, DATEADD(DAY, -1, GETDATE())) AS DATE) AS_OF_DATE FROM (SELECT DATEADD(DAY, -(DAY(GETDATE()) - 1), CAST(GETDATE() AS DATE)) PERIOD_END_DT) A) A

We are using the Microsoft SQL Server connection type (connected to a SQL Server 2016 database). The connections work, I can use the data connector tool to look at the database structure and data. No changes were made to the app itself.

Any ideas what could be causing this error?

Re: Object reference not set to an instance of an object after upgrade to QS Sept 2019

chadwatson — Tue, 12 Nov 2019 22:21:20 GMT

We applied this fix but it did not help:

https://support.qlik.com/articles/Basic/Connector-reply-error-Executing-non-SELECT-queries-is-disabled-Please-contact-your-system-administrator-to-enable-it?articleId=Connector-reply-error-Executing-non-SELECT-queries-is-disabled-Please-contact-your-system-administrator-to-enable-it

Re: Object reference not set to an instance of an object after upgrade to QS Sept 2019

chadwatson — Tue, 19 Nov 2019 16:47:47 GMT

I am finding that this has something to do with SQL temp tables. It doesn't seem to want to select INTO a Temp table (#MyTempTable). Does anyone else use SQL temp tables in their load scripts and then use those temp tables in subsequent queries in the load script?

Re: Object reference not set to an instance of an object after upgrade to QS Sept 2019

chadwatson — Thu, 05 Dec 2019 20:46:02 GMT

This issue was resolved by installing updated OLE DB connection database drivers and using the OLE DB connection instead of the Microsoft SQL Server connection.