Sql Temp Tables in June2019 (13.32.2) release don't work anymore for Microsoft Sql Server
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.