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.
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.
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.)
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?