Hi
Thomas gave 4 solutions, Brenda sent sql to solution Nr. 4. and solution Nr. 0 could be from first response by Sandeep (leave the old one and create new one for future reports). Thank you all.
So far I picked solution nr. 2) by Thomas. I renamed data source name and did export-import deployment on one folder of reports and they are updated.
But when I run them this is thrown:
XQE-DS-0009 Data source "xy" was not found in Content Manager.
Nevertheless validation of SQLs in reports is successful!
I also tried manually changing data source on SQLs in some existing old reports and it is surprisingly not helping - the same error.
I checked xml report specifications, tried upgrading report specifications, enriching package, restarting Cognos... no help. Somewhere is still information/link to the old data source name.
Package is DQM, using JDBC, I am Sys Adm and we are on 11.1.7 FP1.
But if I try this on a brand new report, it works (create report > create SQL > run, save and close report > rename data source name in Administration
> edit report > manually change data source on SQL > save and run > ok).
I am struggling with this, hence my late response.
However this is I guess my problem and I can say you helped me a lot and a solution to this topic is given.
Thanks!!
------------------------------
Ales
------------------------------
Original Message:
Sent: Tue December 15, 2020 01:43 PM
From: brenda grossnickle
Subject: How to update all SQL based reports when data source is renamed
So i am still not understanding how when you moved to the new server that you could not keep the same datasource name and just change the connection string to be the new server. We deploy our framework manager packages to multiple servers and environments. Our framework manager has the datasource name H123. We do not mention a specific server or database inside of framework manager. then when setting up cognos all of our data sources are called H123 and the connection and signon is customized to each specific server.
I do have some sql that will update report definitions. It just looks for a string like dataSource="OLD" and change to dataSource="NEW".
BACKUP your CM database prior to running any updates.
This should help you get started. This assumes that you know the CMID. BUt you can change the query to find the CMID that have the newfieldname.
declare @oldfieldname as nvarchar(max)
declare @newfieldName as nvarchar (max)
declare @cmid as int
---------------- Change these three values to match the cm_SearchForTextInReports.sql results
set @cmid = 1062
set @oldfieldname = N'[Demand Deposits / Savings].[Current Measures].[Average Collected Balance Current Year]'
set @newfieldname = N'[Demand Deposits / Savings].[Current Measures].[Reg DD Average Collected Balance Current Year]'
----------------
declare @oldfieldnameLike as nvarchar(max)
declare @newfieldNameLike as nvarchar (max)
set @newfieldnameLike = '%' + Replace(@newfieldname, N'[', N'[[]') + '%'
set @oldfieldnameLike = '%' + Replace(@oldfieldname, N'[', N'[[]') + '%'
Update cmobjprops7 Set spec = Cast(Replace(Cast(Spec As nvarchar(max)), @oldfieldname, @newfieldname) As ntext) where cmid = @cmid;
select 'Contains New Field Name', cmid, spec from cmobjprops7 where spec like @newfieldnamelike and cmid = @cmid
select 'Contains Old Field Name', cmid, spec from cmobjprops7 where spec like @oldfieldnamelike and cmid = @cmid
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
Original Message:
Sent: Tue December 15, 2020 09:45 AM
From: Thomas van der Meer
Subject: How to update all SQL based reports when data source is renamed
Hi Ales,
I have made an example report to understand your problem. I created a SQl based report and opened the XML. This XML contains the datasource name which you want to change.
<sqlQuery name="SQL1" dataSource="DWH">
<sqlQuery name="SQL2" dataSource="DWH" type="cogSQL">
So you wil have to update all the report specs containing this datasource. There are several ways to do this:
1) Use a third party tool like Motio with the 'Search and Replace' function. This is safe method.
2) Create a export deployment with the reports to be updated. Open the zip file and the edit only the file 'package1.xml'. Update the specifications in this file with a search and replace. Do not change the encoding of the file. Watch Zip the files in a new archive. Copy the zip zrchive to the deployment folder. Then create a new import and run it. That's it!
3) For Cognos version 8 there was a tool called 'Dynamic ReportSpec Updater', but I don't think that exists anymore.
4) You could perform an update directly on the content store. The report spec is stored in table cmobjprops7 in column spec. I would not advice this, but it is possible.
------------------------------
Thomas van der Meer
Original Message:
Sent: Tue December 15, 2020 08:13 AM
From: Ales
Subject: How to update all SQL based reports when data source is renamed
Yes we have data source name -> data source connection -> signon.
Yes I changed the connection and signon. This is written in "I just changed the settings in data source connection. Everything works."
This is where we are.
Now I want to rename data source name because the name is misleading to others.
Framework manager is not the problem. SQL based reports do not work any more - this is the problem.
All SQLs in such reports are attached to data source name rather to some ID.
------------------------------
Ales
Original Message:
Sent: Tue December 15, 2020 07:27 AM
From: brenda grossnickle
Subject: How to update all SQL based reports when data source is renamed
I believe that you have ... data source name -> data source connection -> signon. In the Admin console you could have the previous data source name and just change the connection and signon. So that you would have the same name but it would point to the new server/database/credentials. That way you would not have to change framework manager or any reports.
------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
Original Message:
Sent: Mon December 14, 2020 10:33 AM
From: Ales
Subject: How to update all SQL based reports when data source is renamed
Hi,
we moved data from one server to another. I just changed the settings in data source connection. Everything works.
But old data source name is still visible when authors pick one from a list and it is confusing. If I rename data source in admin console and re-publish FM packages, it is ok. But we have hundreds of SQL-based reports. These do not work anymore in this case. You have to manually change data source in each SQL in reports.
Is there some script to do this? Or is there another way?
Thank you all for help.
Regards, Ales
------------------------------
Ales
------------------------------
#CognosAnalyticswithWatson