Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

I have a qlikview document that I distribute to my customers. This document connect to database with ODBC, with a user and pwd.

The conexion in the script is this:

CONNECT32 TO NAME_ODBC (XUserId is WVDcXUZNULZOGZFMRSVGN, XPassword is EIaYUUZNULZOGZFMOJUMFcB);

My customers may have sql server database or oracle database. Same database that me, same name and same tables. They create the ODBC with their user and password. Is possible that their password is different.

When I send them my qlikview document and they reload directly, then reload doesn´t run. To resolve this, the customer create their self connection in the script with the qlikview destock.

I want send them my qlikview document and that it runs and reload ok.

Is it better use the OLEDB connection? How can I do to send them a qlikview document that runs and reload perfect?

Thank you.

14 Replies
Not applicable

Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Hi Marta,

ODBC is an older standard and is actually not specific to windows. see this:


OLE DB or ODBC? | SQL Server content from SQL Server Pro



best regards

mov
Not applicable

Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Marta,

There is no reason to use ODBC if OLEDB is available.  One of the advantages is that users do not need to create ODBC DSN, and there is no confusion with the passwords.

Regards,

Michael

Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

If I do the OLEDB connection in the script is this:

oledb1.png

CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=integra;Initial Catalog=INTEGRA;Data Source=INTEGRA8;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MARTA-HP;Use Encryption for Data=False;Tag with column collation when possible=False];

In the customers, the name of the server may be different of this. This is a problem.


When I reload I need introduce the password. Attachment file:oledb.png

Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Marta,

when creating the connection check the option: Allow saving password

best regards

mov
Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Marta,

First, we always use variables for the server name and database name.  It will be like this:

CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=integra;Initial Catalog=$(Database);Data Source=$(Server);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MARTA-HP;Use Encryption for Data=False;Tag with column collation when possible=False];

So, they are configured on the front end before reload.  Or even in a separate file that you load before this connection string, and create this variables.

Next password can be part of the CONNECT.  Or, you can use variables for user and password as well.  Even better to use Windows Authentication - no need for user/password.

Regards,

Michael

jaimeaguilar
Not applicable

Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Hi Marta,

Make sure to check the option that says "Allow saving password / Permitir guardar contraseña". This way qlikview won't ask you for the password everytime you want to connect to database,1.png

regards

Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

How I can do it with a separate file? One week ago I tried it with "include" but didn´t run.

In this separate file the password can be encrypted?

Thanks.

Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Martha ,

the password will be encrypt in both cases. you need to put the external file in the same directory of the qvw file and the sintaxe is:

$(Include=filename.qvs);

Not applicable

Re: Re: ODBC or OLEDB for different BD: Oracle or Sql Server- Which is best?

Thanks very much.

It runs without encrypt.

Now, how I can encrypted the user and the password in the external file? Al least the password.

Thanks.