Cannot change rpt using Command line from SQL Server to Oracle

[former_member967096]

We are in the process of changing existing reports that (CR 2011 and 2013) that are connected to a SQL Server DB to connect to the same DB, but an Oracle version. We have many reports which used the Command option in the Database Wizard instead of dragging and dropping tables and linking the icons.

Here’s the catch 22:

If we edit the Command SQL and try to make it Oracle compliant, it fails because the report is connected to a SQL Server database.

If we change the Datasource Location to the Oracle database it fails because the SQL in the Command is T-SQL.

We cannot update the report at all.

Any ideas?

Thanks,

Brad

[abhilash_kumar · Accepted solution]

Hi Brad,

Try these steps:

1) Go to the Database Expert > Right-click the command sql > View.

2) Copy the SQL Statement

3) Modify the SQL so that it uses Oracle syntax wherever applicable.

3) Go to the Set datasource menu > highlight the Command table in the pane on the top > Create a new connection to the Oracle database from the panel at the bottom.

4) Next, expand the oracle connection name > Highlight Add Command > Highlight the Command table on the top > Click 'Update'.

5) Paste the SQL you modified in Step 3 and click OK.

Unfortunately, this is the only workaround available.

There is no solution that can automatically modify the SQL to work with different databases.

-Abhilash

[former_member967096 · Accepted solution]

Thank you, Abhilash. We gave it a shot with a few of our reports and it worked out great.

You made several peoples' weekend! 

Take care,

Brad