Production & development

[#1]

Does anyone know if it is possible to set a database connection as a variable to allow the switching of data sources? something like my example below but works

SET vConnection = 1;

IF vConnection = 1 THEN
//Dev

SET vDBName = 'CONNECT TO [Provider=SQLOLEDB.1;Integrated Security.............................

ELSE

//Prod

SET vDBName = 'CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=............................
END IF

SQL SELECT *
FROM $(vDBName).dbo.Table1;

This seems like something people would of cracked a long time ago but I can't find much on the subject any ideas?

Cheers

[vgutkovsky]

Yes, you can do that I believe. I think the problem with you syntax is the Connect statement. A Connect statement just goes once in the beginning of the script to connect to the DB Server. You can encode the DB name itself in a variable and use variable expansion to get it. For example:

ODBC CONNECT TO [Provider=SQLOLEDB.1] //etc
SET vDBName = myDatabase;
SQL SELECT *
FROM $(vDBName).dbo.Table1;

Regards,

[#3]

Normal 0 false false false MicrosoftInternetExplorer4

Thank you for your response Vlad.

Are you aware of any method to encode different connect statements to variables or something similar to allow me to switch between data sources on different servers by changing the variable value?

My aim is to have replicated production and development data environment and i want to do something like change the value of a variable to decide which server is used as the source for the reload.

Again thank you.

[nathanfurby · Accepted solution]

You can store the connection strings in an external file and use them like this:

Connections:
LOAD ConnectionNumber,
ConnectionName,
ConnectionString
FROM

(biff, embedded labels, table is Sheet1$);

Let vConnection = Peek('ConnectionString',vConnectionNumber);

ODBC CONNECT TO $(vConnection);

Just create a variable called vConnectionNumber and assign it value of 0 - could then change this via Input Box etc. and even think of a clever way to link the connection number from the Connections table to the variable.

[#5]

Normal 0 false false false MicrosoftInternetExplorer4

Thanks Nathan that's a really good idea, much appreciated.

[nathanfurby]

No problem.

BTW - love the fact you are using IE4