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 *
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.
You can store the connection strings in an external file and use them like this:
[C:\Documents and Settings\nfurbank\Desktop\Connection String Test.xls]
(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.