topic Problems while trying to execute a SQL procedure to store data from Sense directly into a MS SQL DB in Connectivity & Data Prep

Problems while trying to execute a SQL procedure to store data from Sense directly into a MS SQL DB

Tino — Fri, 10 Dec 2021 21:09:00 GMT

Hi Qlik community,

I´ve found some posts regarding the possibility to write data directly from Sense into a SQL DB and tried to set it up.

The DB admin has created a SQL procedure. When I´m trying to write the data to the DB the following error occurs

By using a ODBC connection:

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 102, ErrorMsg: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '202104'.

Der Fehler ist hier aufgetreten:

SQL exec [dbo].[QLIKSENSEUSERS](202104,'QLIKSENSE',xxx)

By using a SQL connection:

ERROR [42000] Incorrect syntax near '202104'.,

Der Fehler ist hier aufgetreten:

SQL exec [dbo].[QLIKSENSEUSERS]('202104','QLIKSENSE','xxx')

This is the part of the script, which should write directly into the SQL DB:

SQL exec [dbo].[QLIKSENSEUSERS]('$(vT_CalMonth)','QLIKSENSE','$(var1)');
SQL exec [dbo].[QLIKSENSEUSERS]('$(vT_CalMonth)','QLIKVIEW','$(var2)');
SQL exec [dbo].[QLIKSENSEUSERS]('$(vT_CalMonth)','QLIK_MIXED','$(var3)');

When I try to write back with a Select statement (based on a post I`ve found) the following error occurs

ERROR [HY000] Cannot find either column "dbo" or the user-defined function or aggregate "dbo.QLIKSENSEUSERS", or the name is ambiguous.,

Der Fehler ist hier aufgetreten:

SELECT [dbo].[QLIKSENSEUSERS]('202104','QLIKSENSE','xxx')

I did some research but unfortunatelly without any success.

So I have the following questions:

What kind of connection do I need to the MS SQL Server, in order to able to execute procedures?
Did I miss someting in my script?

Do you have any hints/tips oder ideas?

Thanks in advance!

Best
Tino

Re: Problems while trying to execute a SQL procedure to store data from Sense directly into a MS SQL DB

Seyko — Mon, 03 May 2021 19:02:34 GMT

Hello,

I've found a post with a similar subject (on Qlikview, but as you know the script logic is the same). I hope it'll be helpful for you :

https://community.qlik.com/t5/QlikView-App-Dev/Executing-a-stored-procedure/td-p/342032

Regards,
S.B

Re: Problems while trying to execute a SQL procedure to store data from Sense directly into a MS SQL DB

Tino — Tue, 04 May 2021 13:48:03 GMT

Hi all,

I´ve figured out how it works and want to share it with you.

Preconditions:

ODBC connection is needed
Execute rights are needed on the SQL DB (at least for the procedure)

In my case the SQL EXEC did not work when handing over the values as order based parameter (as defined in the SQL procedure). It was necessary to hand over the values metadata based.

It works with this syntax:

SQL exec [SCHEMA].[PROCEDURENAME]
@field1 = '$(value1)',
@field2 = '$(value2)',
@field3 = '$(value3)';

Although in the SQL procedure it is defined in which order the values are expected as parameter, it did not work with this syntax (order based):

SQL exec [schema].[PROCEDURENAME]('$(value1)','$(value2)','$(value3)')

So i hope this will help others in future.

Re: Problems while trying to execute a SQL procedure to store data from Sense directly into a MS SQL DB

Tino — Tue, 04 May 2021 13:16:49 GMT

Thanks Seyko.

Unfortunately it was not helpful but in the meantime I´ve found and shared the way it works.

BR
Tino