topic Re: QVD Generator Non Select Query in Connectivity & Data Prep

QVD Generator Non Select Query

brandonblaine — Tue, 28 Dec 2021 20:07:59 GMT

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.

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.

Re: QVD Generator Non Select Query

treysmithdev — Wed, 14 Aug 2019 02:38:15 GMT

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

Re: QVD Generator Non Select Query

jubarrosor — Wed, 14 Aug 2019 10:08:27 GMT

Hi Brandonblaine:

Has you cheked this points?

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

Best Regards,

Juan P Barroso

Re: QVD Generator Non Select Query

brandonblaine — Wed, 14 Aug 2019 14:32:40 GMT

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

Many thanks

Re: QVD Generator Non Select Query

brandonblaine — Wed, 14 Aug 2019 14:33:28 GMT

I will try that next. Thanks for the suggestion.

Re: QVD Generator Non Select Query

brandonblaine — Wed, 14 Aug 2019 14:57:15 GMT

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.

Re: QVD Generator Non Select Query

treysmithdev — Wed, 14 Aug 2019 15:07:20 GMT

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 * ...

Re: QVD Generator Non Select Query

brandonblaine — Wed, 14 Aug 2019 15:09:50 GMT

Smart. I'll let you know what happens.

Re: QVD Generator Non Select Query

brandonblaine — Thu, 15 Aug 2019 00:27:00 GMT

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.

Re: QVD Generator Non Select Query

brandonblaine — Thu, 15 Aug 2019 00:50:53 GMT

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.