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

[#1]

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.

[#2]

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

[Anonymous]

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

[#4]

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

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:

[#5]

Marta,

when creating the connection check the option: Allow saving password

best regards

[Anonymous]

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]

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,

regards

[#8]

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.

[#9]

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);

[#10]

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.