SQL Server ODBC Connector Hassle

[#1]

When I move my document from my workstation to my QlikView server I have to rebuild the SQL Server ODBC connection string in order to connect to my data source as shown below. I would think that the same connection string would work on both machines provided the data sources are named and configured the same. This is a bit of a hassle and a source or reload errors when we forget to flip the comment tokens. Can anyone suggest what I'm mmissing missing here?

ODBC

CONNECT TO VMSProduction (XUserId is LGEJFJVMQbbEHaRIBDdCGIJOPDZUD, XPassword is MbGeFIVOMbcCGZBGRRPB); // Al's Workstation

//ODBC CONNECT TO [VMS Production] (XUserId is CHfQFJVMQbbEHaRIBDdCGIJOPDZCE, XPassword is YTbCJIVOMbcCGZBGRZTA); // QlikView Server

[#2 · Accepted solution]

I guess I missed the obvious fact that "VMSProduction" is not the same data source name as "VMS Production". Oops ...

[MichaelRobertshaw]

If the ODBC Connection String has been recreated with exactly the same name then you should be fine. ODBC may be useful when your Dev & Prod environments access a different physical database. If using ODBC then always use SYSTEM datasources, not USER datasources. Generally we recommend using OLEDB whenever possible because it does not require configuration outside the QlikView document and is often faster than ODBC. The OLEDB connection string would always connect to the same physical database though, so might not be appropriate in some restrictive change-control environments.

You could also set a Variable that indicated which environment you are in (Dev/Prod/etc) and use an IF statement to control the CONNECT statement.

[#4]

Hi,

Could you please explain how to use a variable to determine the environment (DEV or PROD) in order to be able to use different connection strings?

Thanks,

Sourabh

[rwunderlich]

One easy way to use conditional connection strings is to test the computername:

IF computername() = 'MYQVSERVER' THEN
SET str = x;
ELSE
SET str = y;
END IF

-Rob