https://www.nogalis.com/wp-content/uploads/2016/05/A-More-Efficient-way-to-Update-Report-Data-Sources.jpg 454 500 Angeli Menta https://www.nogalis.com/wp-content/uploads/2013/04/logo-with-slogan-good.png Angeli Menta2016-05-06 07:39:342016-06-02 15:21:33A More Efficient way to Update Report Data Sources
A More Efficient way to Update Report Data Sources
During an upgrade, re-pointing all your LBI reports to your new database may seem like a daunting task. Never fear! You don’t have to do it manually (at least not all of them). Here are the steps to overwrite your data sources using a SQL update query:
- Create an ODBC connection on your LBI server that connects to your NEW database server.
- Don’t forget that you have to use the 32-bit ODBC tool for LBI data sources!
- Create your new data source in LBI
- Open SQL Server Management Studio (or whatever database management tool you use)
- Log-in to your LBI database server
- Navigate to the LawsonRS database
- BACK UP YOUR ERS_REPORTDATASOURCES TABLE! (You should always back up the table or the database if you are making changes directly)
- Run a query to view all the reports for which you plan to change the data source:
- When you are ready, run your update query using the same WHERE clause that you used when you viewed your reports in step 7
- NOTE: you are updating RSDATASOURCE. This is so that the report data source will be overridden.
- If you have a new username & password for this data source, you will also need to update the DEFAULTUSER and DEFAULTPASSWORD fields as well.
- Now your reports should run against the new data source. There are a few cases in which you will need to manually reset the data source in Crystal and republish the report. They are:
- If your report mixes schemas (i.e. it uses tables owned by dbo, and views owned by xyz)
- If your report has subreports
- If your report has a command that explicitly defines the database name (and the database name has changed)
- There may be more, but these are the instances that I discovered on a recent upgrade!