Inherited Connection Strings

[#1]

Hi All,

I manage qvw document through a development, UAT and Live environment. Quite often there is a lot of changes made between the files when working on enhancements and I simply save over the next environment, updated (uncomment) the required connection string and then reload it.

I noticed recently when creating a new customer qvw file off the base template that the connection string when using the select function in the edit script dialog can not be updated. Even with a reload, resave etc with a new one defined in the script itself.

How do I get this updated? Is there a better way of managing changes in QlikView without having to keep a change record and do them one-by-one?

Thanks

Lee

[#2]

Hi Lee

I'm not entirely sure what you are asking, but I'm guessing that you are making changes to the connection strings when you move the file from one environment to another, if this is wrong then you need to be a bit more specific about the problem.

If I'm right then hopefully this might help, we have QVWs that are moved through DEV, TEST, UAT, Pre-Prod & Finally into Production, and the connection strings are not changed. Previously we managed this through Computer Names, so we had a simple IF....THEN....ELSE block that changed the connection string according to the file server that the refresh was running on. We now manage this through a Work Environment string that comes out of Active Directory, but the principles are the same, sample code follows:

IF upper(vComputer) = 'D-NWEST' THEN
 CONNECT TO [Provider = SQLNCLI.1 ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = . ;
 Workstation ID = localhost ;
 ];
ELSEIF upper(vWorkEnv) = '_DEVELOPMENT' THEN
 CONNECT TO [Provider = SQLNCLI.1 ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = . ;
 Workstation ID = localhost ;
 ];
ELSEIF upper(vWorkEnv) = '_LIVE' THEN
 CONNECT TO [Provider = SQLOLEDB ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = AKA-REPORTS-SQL ;
 ];
ELSEIF upper(vWorkEnv) = '_TEST' AND WorkingEnv=1 THEN
 CONNECT TO [Provider = SQLOLEDB ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = AKA-REPORTS-SQL\STATIC ;
 ];
ELSEIF upper(vWorkEnv) = '_TEST' AND WorkingEnv=0 THEN
 CONNECT TO [Provider = SQLOLEDB ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = TESQL07-51 ;
 ];
ELSEIF upper(vWorkEnv) = '_UAT' THEN
 CONNECT TO [Provider = SQLOLEDB ;
 Integrated Security = SSPI ;
 Persist Security Info = False ;
 Initial Catalog = CCDW ;
 Data Source = UTSQL04-43 ;
 ];
ENDIF

Hope it helps, but if it doesn't, please add a bit more detail on what you are looking for.

Regards,

[#3]

Hi Nigel,

Thanks for the reply, that is really slick! Not quite what I was trying to describe. Basically, after I have saved the base file under a new extension, lets say for a new client. I update the connection string in the edit script to connect to a new database.

If I then choose select, in the edit script dialog, to insert a new table, field etc. the file retains the old connection string in the dialog, despite reloads, resaves etc. Which mean I can not select a new table for example that exists specifically for that database.

I hope that makes more sense.

Thanks
Lee

[biester]

If it's environment dependant, perhaps environment variables could help, e.g. you could make a system or user environment variable CONN_STRING and then in the script you read it and use it with CONNECT TO:

let CONN_STRING=GetRegistryString('HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment\','CONN_STRING');

CONNECT TO $(CONN_STRING);

I'm using such constructions in similar contexts successfully.

Rgds,
Joachim