SQL connection variables dynamic

[chetan_surana]

Hi ,

Our database is SQL server 2008 .  QVw reports point to this database.  I want to make the servername, username and pw dynamic so that in future if the database changes i dont have to go in each document and change the credentials.  I created an ODBC connection and named a datasource giving all the connection details of SQL server 2008.  But when i connect to ODBC in qvw it still prompts me username and pw along with data source name. Can i avoid that and only give data sourcename in the report ?

Thanks in advance !

[Michiel_QV_Fan · Accepted solution]

Copy - paste the connection string to a text file.

Read this text file with the include statement in Qlikview.

Now you only have to change the contents of the text file and all QVW's will read from the same data source.

[chetan_surana]

Hi Michiel,

i replaced the connection string with text file and read that file with include statement in qvw. It worked ! however if i need to change the password of the server in the text file , it gives in an encrypted format :

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=genius;Initial Catalog=testdb;Data Source=dashboard.com;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ps1111;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is dCHLMRFEPLMETZQGVZLB);  How do i change this encrypted one ??

when replacing this pw with the actual one it fails ..any inputs on the same?

[Michiel_QV_Fan]

As far as I know this should work. I used this myself.

I think the best way is to rebuild the connection in a qlikview after a password change is:

Copy the new connection string from Qlikview to the text file (you can comment the old string if you want to save the previous string).

Can you try that?

[chetan_surana]

Yep that would work..was thinking of a way where i can skip that step..but thats okie need to change only in one place now Thanks for the help!