5 Replies Latest reply: Apr 13, 2011 7:44 AM by Nathan Furbank RSS

    Production & development

      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

        • Production & development
          Vlad Gutkovsky

          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,

            • Production & development
              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.

            • Production & development
              Nathan Furbank

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

              Connections:
              LOAD ConnectionNumber,
              ConnectionName,
              ConnectionString
              FROM
              [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.