Using ‘Set Datasource Location’ with a SQL command

Wednesday 20 June 2012 @ 10:23 pm

If your report uses tables, it is usually straight forward to point the report from one data source to another.  You use the menu item:

Database > Set DataSource Location.

Once in that window, you highlight the existing connection in the top half of the window.  Then you highlight the new connection in the bottom half of the window.  When you click “Update” the properties of the current connection (top window) should change to reflect the new connection.

However, this is not as simple when the report is based on a command, because with a command there is no existing table called ‘command’ for CR to find in the new connection.  The command only exists in CR.   So here are two workarounds that you can try:

Option A:

  1. Highlight the command in the top half of the window and highlight the “Add Command” option under the new connection in the bottom half of the window.
  2. Click “Update” and an empty command box should open.
  3. Paste the command into this new box and click ‘OK’ to save the command.
  4. Check the properties of the command in the top window to confirm that is is using the new connection.

Option B only works when both old and new connections use ODBC:

  1. Make sure CR is closed and that the rpt. file is not in use by someone else.
  2. Go into your ODBC Administrator and rename the existing DSN temporarily (I add an X to the end of the name).
  3. Exit the ODBC Administrator and open the report.
  4. Click the menu items:  ‘Database > Show SQL Query’
  5. Since CR can’t find the original DSN it will open a window listing all the other ODBC connections you have available.
  6. Select the new connection.
  7. Click ‘Next’ to log into the connection, then click ‘Finish’.
  8. Check the properties of the command in the top window to confirm that is is using the new connection.

I am pretty sure that there are environments where these won’t work, although I don’t have any specific examples that come to mind.  If some of you have an environment where these methods don’t work and you can send me the specifics of your connection, I will post a follow-up article.









Leave a Reply

Recrystallize Pro