Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
brandonblaine
Contributor III
Contributor III

QVD Generator Non Select Query

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.

Brandon Blaine
Labels (2)
1 Solution

Accepted Solutions
treysmithdev
Partner Ambassador
Partner Ambassador

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

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

9 Replies
treysmithdev
Partner Ambassador
Partner Ambassador

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

Blog: WhereClause   Twitter: @treysmithdev
jubarrosor
Partner Ambassador
Partner Ambassador

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
Contributor III
Contributor III
Author

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

Many thanks

Brandon Blaine
brandonblaine
Contributor III
Contributor III
Author

I will try that next. Thanks for the suggestion.

Brandon Blaine
brandonblaine
Contributor III
Contributor III
Author

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.

Brandon Blaine
treysmithdev
Partner Ambassador
Partner Ambassador

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

 

Blog: WhereClause   Twitter: @treysmithdev
brandonblaine
Contributor III
Contributor III
Author

Smart. I'll let you know what happens.

Brandon Blaine
brandonblaine
Contributor III
Contributor III
Author

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.
Brandon Blaine
brandonblaine
Contributor III
Contributor III
Author

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.

Brandon Blaine