data server switch in SQL query

[Former Member]

Hi guys,

I have a question about data server switch in the sql query template. We have been developing all the sql queris pointing to the customer test db server say "TESTDB", now we have to change the db connection to "PRODUCTDB", do we need to change sql queries one by one or is there a better solution for this? I think this is a common requirement to change back and forth data server connection for a bulk number of queries

Thanks,

Tony.

[Former Member · Accepted solution]

Hi Tony,

usually you define one DB name in your MII-Test and MII-Production server and only change the settings inside the Data Server window of MII. This way you won't have to change the names in the sources and can deploy the files without problems. Same goes for the SAP Server settings.

I don't think there is an easy way to change all the sources at once.

Michael

[Former Member · Accepted solution]

It is a little bit of extra work, but you can accomplish "dynamic switching" with the use of session parameters and/or global parameters. The "Server'" property of any query can be over-ridden at runtime, so within your applets, using IRPT pages, you can use something like: Server="" as an applet parameter. Similarly, you can use a Global parameter in BLS, mapped to the Server parameter of a query action, to achieve comparable results. I've also used this for JCO servers, e-mail servers, and other cases where the requirement is to dynamically "point" to different destinations/sources.

[Former Member · Accepted solution]

thanks rick

[jcgood25 · Accepted solution]

Tony,

From a design perspective it is advisible to name your data servers in a more abstract way, since the query templates themselves only save the Name of the data server in them. Data Servers also have a description field (typically overlooked) which will appear in the list and the workbench where you can be explicit or to even serve as a reminder that you are working with either test or production data. The true 'switch' should happen when you reconfigure your data server settings for server url and user/pwd.

TESTDB and PRODUCTDB might be better served with a meaningful name that provides a correlation to the data stored in the database. In more mature system landscapes having a DEV/QA/PROD arrangement of MII servers, the ability to promote content comes from configuration and projects, not from actually changing the content on the same server.

If you need to rapidly modify your content to adopt this practice then I would simply export the associated project and unzip it to a folder. Then use windows explorer to do a search for files containing your data server name (they will appear in your query templates as Server="TESTDB"). With a text editor that can do search and replaces across multiple files (http://www.editplus.com is my personal favorite) you can easily change the data server name to a better convention. Then zip up the folder content and reimport the project. This will effectively switch your data servers (don't forget to create the new abstract data server in place of the TESTDB and PRODUCTDB).

Regards,

Jeremy

[Former Member · Accepted solution]

Jeremy,

Thanks a lot for the advise, really useful tip.

Regards,

Tony.