QVD Generator Non Select Query

[brandonblaine]

Hello,

I have a simple QVD generator app that works perfectly while in the Qlik Desktop Client but when I run it within the QMC, it errors. Here are the details.

This is on the April 2019 release.

There are two parts to the load script. The first is to run an ETL stored procedure in SQL Server:

LIB Connect To DB;

SQL EXECUTE sp_RevRecLoadInvoices
!EXECUTE_NON_SELECT_QUERY;

The second part loads the results from a table into QVD:

Invoices:
Load *;
SQL SELECT *
FROM dbo.Invoices;
STORE Syringes INTO [lib://FileSystem/Invoices.qvd] (qvd);
DROP TABLE Invoices;

This works perfectly in the Desktop Client. When I load it into the QMC and try to run it, I get the following error:

2019-08-07 23:19:27 0026 !EXECUTE_NON_SELECT_QUERY
2019-08-07 23:19:27 Error: Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 102, ErrorMsg: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '!'.
2019-08-07 23:19:27 Execution Failed
2019-08-07 23:19:27 Execution finished.

On both the desktop and server I made sure that the value for running non select queries was set within the QvOdbcConnectorPackage.exe.config file.

<appSettings>
<add key="allow-nonselect-queries" value="True" />

On existing forum posts and online documentation, I did not ready anything that the syntax would be different on the server.

Your help is greatly appreciated.

[treysmithdev]

Have you tried restarting the Qlik services since changing the settings file?

[jubarrosor]

Hi Brandonblaine:

Has you cheked this points? 

https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/How-to...

Best Regards,

Juan P Barroso

[brandonblaine]

Hi yes. I mentioned in my original post  that I had made sure the config file was setup.

Many thanks

[brandonblaine]

I will try that next. Thanks for the suggestion.

[brandonblaine]

I restarted the server and there is some progress. The new error is:

22019-08-14 14:52:53 0030 SQL EXECUTE Qlikdb.ais.sp_LoadPerDiemPayors
2019-08-14 14:53:29 1 fields found: *,
2019-08-14 14:53:29 Error: QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are used in the query:
2019-08-14 14:53:29 Object reference not set to an instance of an object.
2019-08-14 14:53:29 Execution Failed
2019-08-14 14:53:29 Execution finished.

So it successfully ran the procedure but then failed when it thought it needed a result set.

[treysmithdev · Accepted solution]

Does the stored procedure return anything? I wonder if it returning something it is forgoing the '_skip_loading (string __empty_field)'.

In the meantime you could do two things.

1. Update the stored proc to return the table at the end.

2. Though it is kind of hacky, you could skip the error.

Set ErrorMode = 0;

SQL EXECUTE Qlikdb.ais.sp_LoadPerDiemPayors !EXECUTE_NON_SELECT_QUERY;

Set ErrorMode = 1;

Invoices:

Load * ...

[brandonblaine]

Smart. I'll let you know what happens.

[brandonblaine]

LIB Connect To CPRSQL_Live;
SQL EXECUTE Qlikdb.ais.sp_LoadPerDiemPayors;

And, it worked! Makes no sense. I need to test a few more times with other apps before I call it good. I'm pretty sure that won't work in the desktop client. We'll see.

[brandonblaine]

This is really strange...

Within the same app, 

    LIB Connect To CPRSQL_Live;

    SQL EXECUTE ais.sp_RevRecLoadInvoiceFirstPayment;
    //!EXECUTE_NON_SELECT_QUERY;

    LIB Connect To SQLProd1_Live;

    SQL EXECUTE Qlikdb.ais.sp_LoadPerDiemPayors;
   //!EXECUTE_NON_SELECT_QUERY;

The first call finishes just fine and the second gets the error:

    2019-08-15 00:38:08 1 fields found: *,
    2019-08-15 00:38:08 Error: QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are     used in the query:
   2019-08-15 00:38:08 Object reference not set to an instance of an object.
    2019-08-15 00:38:08 Execution Failed
    2019-08-15 00:38:08 Execution finished.

I made sure I had SET NOCOUNT ON; and when I run it in SSDT, it doesn't return anything.

So I tried this. At the end of the stored procedure I added:

     SELECT 'Finished' AS Field

And then changed the load script to be:

   TempPerDiemPayers:
   Load *;
   SQL EXECUTE Qlikdb.ais.sp_LoadPerDiemPayors;
   Drop Table TempPerDiemPayers;

And then it worked. It is two different database servers that this script is using but I would think that should't matter.

Frustrating to spend a lot of time figuring out the glitches. Is the latest release better?

I'll make do until we get our new ETL tool stood up. We are looking at MuleSoft. Then the challenge will be to set the dependencies between MuleSoft and the QMC.

Thanks everyone who has chimed in. I appreciate it.