Connecting to SAP BW using BEx OLE DB provider?

[quriouss]

I have been using Qlik Sense with SAP BW for a few months now.  It has been working nicely using the OLE DB method, using the provider which is installed with SAP Business Explorer (mdrmsap)

The string I use is;

OLEDB CONNECT32 TO [Provider=MDrmSap;Data Source="SAP.SYSTEM.NAME";Password=password;User ID=user]

(I have also tried using MDrmSAP.2 - no difference)

This will pop up the screen where you can choose the system to log on to, and then a space for username and password (even though these are in the connection string - that's odd, but I can live with it).

Now I am migrating to "proper" Qlik Sense (on a server) and although it has the same SAP Business Explorer installed I cannot get the same connection string to work.

I *think* it's because of something to do with running through a browser interface, so when it triggers the SAPlogon dialogue there is nowhere for the dialogue bx to pop up, so it will just hang.

(ideally I'd like it to work just from within the script, with no user interaction, so i can just schedule it and schedule a refresh every so often.  Is this possible?)

[quriouss · Accepted solution]

The key is the Prompt: variable.

We want/need to use "Prompt:DBPROMPT_NOPROMPT" but you'll find, if you add this to the connection string, it will fail with an error "you do not have access to the data source" (although the "Test Connection" button will work). Saving it as-is and running the load script gave the error "Format of the initialization string does not conform to the OLE DB specification"

Searching for that error message on Google turned up this page, OLE DB Error: "Format of the initialization string does not conform to the OLE DB specification."

In there it says,

Here is what OLE DB help document says:

DBPROP_INIT_PROMPT

Property group: Initialization

Property set: DBPROPSET_DBINIT

Type: VT_I2

Typical R/W: R/W

Description: Prompt

Indicates whether to prompt the user during initialization. One of the following values:

DBPROMPT_PROMPT—Always prompt the user for initialization information.

DBPROMPT_COMPLETE—Prompt the user only if more information is needed.

DBPROMPT_COMPLETEREQUIRED—Prompt the user only if more information is needed. Do not allow the user to enter optional information.

DBPROMPT_NOPROMPT—Do not prompt the user.

This is the enumerated value in oledb.h:

#define DBPROMPT_PROMPT              0x01

#define DBPROMPT_COMPLETE            0x02

#define DBPROMPT_COMPLETEREQUIRED    0x03

#define DBPROMPT_NOPROMPT            0x04

So you shoud say in this way in connection string: "...;Prompt=3;"

So all I had to do was change the final statement to ;Prompt=4" and voila! it works!

So my whole conenction statement (note: I have moved the USER ID and PASSWORD into the Qlik Management Console for 'protection' against prying eyes) is;

OLEDB CONNECT32 TO [Provider=MDrmSap.2;Data Source="SAP_SYSTEM_ID";SFC_CLIENT=100;SFC_LANGUAGE=EN;Prompt=4]