Sql Temp Tables in June2019 (13.32.2) release don't work anymore for Microsoft Sql Server

[grangerats]

I'm using the QvOdbcConnectorPackage.exe in the June2019 (13.32.2) release to connect to Microsoft Sql Server in my load scripts. I'm aware of the really huge breaking change introduced (noted here in this post and here in article 000064538.). It's already annoying that you have to change the .exe.config file; I'm just waiting for a future upgrade to reset the file and lose our manual change; this should be in the connection string, not in some random config file in the file system.

(And it's really stupid that we have to change all of our scripts to add !EXECUTE_NON_SELECT_QUERY in all the right spots. How hard is it for Qlik's devs to simply to add an "if" to their code that first checks to see if any result-set was returned?  Right now, if you miss a spot, you get this error: "Error: Unable to get column information for the fields that are used in the query: Object reference not set to an instance of an object." error.  EDIT 2019-08-21: It's another bug; article 000055503 )

However, now, when I do something like this:

SQL CREATE TABLE #tempTable (id int NOT NULL PRIMARY KEY) !EXECUTE_NON_SELECT_QUERY;
SQL INSERT INTO #tempTable SELECT TheId FROM SomeTable !EXECUTE_NON_SELECT_QUERY;

I get an error like so:

Error: ERROR [S0002] Invalid object name '#tempTable'.,

Immediately, I assumed that the underlying connection behavior changed to the "new style" where each statement becomes an implicit stored-procedure. However, even changing to use a global temp table (e.g. "##tempTable" instead of "#tempTable") does not fix the problem; the same error occurs.

This must mean that Qlik is disconnecting between each SQL statement or otherwise "hard-resetting" the connection (like in connection-pool reuse). This means that temp-tables are useless. There is no workaround for me. I can only connect with a "readonly" connection, which means I can't even attempt to convert to using "real" tables.

At this point, we cannot upgrade QlikSense Enterprise anymore. Our ETL won't run because of these problems.

[grangerats]

Does anyone at Qlik care about these breaking changes? I just reviewed the September 2019 QlikSense release notes, and didn't notice any reference to something that may have addressed this.

It appears that there is one workaround, presently:  Abandon the QvOdbcConnectorPackage.exe and go back to using the SQLOLEDB provider. Of course that comes with the TLS 1.0 problem  unless that got fixed in the last 12 months or so. (The connector somehow forces use of TLS/SSL even though the options say "no encryption", and OLEDB only supports up to TLS 1.0---which is disabled on the target server, of course, because that's what security audits require one to do in order to pass them.)

So to summarize: I still can't upgrade to any newer version of QlikSense beginning with February 2019, forward.

1. SQLOLEDB - Forces use of TLS/SSL even though the connection string explicitly says "no encryption". It only supports up to TLS 1.0, so you can't use it if you've turned off everything older than TLS 1.2.
   1. EDIT:  E.g.:   OLEDB CONNECT TO [Provider=SQLOLEDB;Data Source={host};Initial Catalog={dbcat};Encrypt=false]
2. QvOdbcConnectorPackage.exe
   
   1. Manages connections in a new way that makes it impossible to use temp tables---both local (#) and global (##).
   2. Can no longer handle "non-SELECT" query statements like it used to. Instead of simply checking for null, some programming team invented an incredibly cumbersome workaround that requires...
      1. ...Mangling valid SQL queries to add a sentinel value at the end. It makes your queries invalid for any other connector that works with Sql Server, and you now have to hope that the Qlik "SQL" parser won't screw things up when you do complicated queries (which it has in the past).
      2. ...Manually editing an obscure .exe.config file to make the above option even possibly work. Instead of making this a custom connection-string parameter, you have to do it globally and hope that a future Qlik update won't accidentally wipe your changes.
3. ODBC - Worthless to me because it requires the connection to be setup out in the OS, not within QlikSense; horrible for maintenance.

[jsteyn]

Not sure if this will work for you but it did for me on the Qliksense Business platform.

https://support.qlik.com/articles/000065604

[grangerats]

Thanks, but no.

That's the wrong connector and wrong database platform. But it does, unfortunately, indicate that the poor implementation decision made for Sql Server was also made for Postgresql.  So at least they're consistently screwing things up.

"!EXECUTE_NON_SELECT_QUERY". Worst. Solution. Possible. (well, I'm sure it could get worse.)

[LucLab]

We have the exact same issue with Oracle extraction since the June 2019 Qlik version.

Plz need a fix.

[grangerats]

Does anyone at Qlik listen or even care? This has been unresolved for years now. And I just discovered another gotcha that comes with being stuck on the old SQLOLEDB driver.

How are others even using Qlik for an Enterprise-level ETL, given these unresolved, no-workaround, architecture failures?