May 23, 2016 at 5:37 am
Hi Guys,
We are about to move one of our operational servers to a new VM. As part of the checking I have discovered that I have in the region of 170 SSRS reports where at least one dataset query is hard coded to look at the specific server
SELECT
Something
FROM
[server].[database].[dbo].
So even if I change the connection parameters in the shared data connection, these reports will still try to link to the old server.
I know that the rdl is stored as an image, am I safe to update the data by converting it back to NVARCHAR, Replace the hard coded path with just the table schema and name and convert the result back to an image.
I would rather not have to download all the reports to BIDS, edit them and load them back up again.
May 23, 2016 at 5:51 am
aaron.reese (5/23/2016)
Hi Guys,We are about to move one of our operational servers to a new VM. As part of the checking I have discovered that I have in the region of 170 SSRS reports where at least one dataset query is hard coded to look at the specific server
SELECT
Something
FROM
[server].[database].[dbo].
So even if I change the connection parameters in the shared data connection, these reports will still try to link to the old server.
I know that the rdl is stored as an image, am I safe to update the data by converting it back to NVARCHAR, Replace the hard coded path with just the table schema and name and convert the result back to an image.
I would rather not have to download all the reports to BIDS, edit them and load them back up again.
While this is possible, I'd certainly counsel against it. When I was looking in to doing something similar I didn't find a single resource that advised it was a good idea. In the end, we got an ops bod, trained him in what to do, gave him cake and left him to it.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
May 23, 2016 at 7:16 am
@BWFC
Yup, It sounds dangerous to me too, hence why I was asking the question 😀
I am inheriting these reports from someone else, and we are effectively using the ReportServer database as our source repository (which I am trying to change to use a proper source code control and release process)
Methinks a call to Freelancer or Odesk may be in order.
May 23, 2016 at 1:51 pm
i did this once, but it's been a while, and it actually worked.
same issue, hardcoded code to three part naming conventions.
it's been a while, but i obviously made sure i could convert text-->varbinary-->varchar and then back again first.
since all i had to do was spin off a backup of the ReportServer database, i was not to scared to make potentially devistating changes.
in my case, i selected all the converted content first, and found i had to make multiple updates, due to patterns not being 100% consistent...sometimes things were quotenamed, sometimes they were not. i modified just one report as a proof of concept, and after i confirmed it modified with no issues, i did it against all the reports that matched the pattern/problem.
SELECT
convert(varchar(max),
convert(varbinary(max),cat.Content)) As Verted,*
FROM ReportServer.dbo.Catalog cat
where cat.Content is not null
Lowell
May 23, 2016 at 2:04 pm
Lowell (5/23/2016)
i did this once, but it's been a while, and it actually worked.same issue, hardcoded code to three part naming conventions.
it's been a while, but i obviously made sure i could convert text-->varbinary-->varchar and then back again first.
since all i had to do was spin off a backup of the ReportServer database, i was not to scared to make potentially devistating changes.
in my case, i selected all the converted content first, and found i had to make multiple updates, due to patterns not being 100% consistent...sometimes things were quotenamed, sometimes they were not. i modified just one report as a proof of concept, and after i confirmed it modified with no issues, i did it against all the reports that matched the pattern/problem.
SELECT
convert(varchar(max),
convert(varbinary(max),cat.Content)) As Verted,*
FROM ReportServer.dbo.Catalog cat
where cat.Content is not null
I was successfully able to do the same thing, although it's also been quite a while for me also.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 24, 2016 at 1:04 am
In the interests of full disclosure I should probably say that I tried it and it worked. It was the strength of the feeling that just because we could, it didn't mean we should, that caused the cold feet. Tinkering with the Catalog is not widely recommended.
I can't shake the sense that doing it is one of the reasons why we can't preview things in Report Builder any more.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
May 26, 2016 at 3:41 pm
in the end I narrowed it down to just 25 reports that needed fixing (urgently) so I have done it the long way round. These fell into two categories. The first were just 'badly' coded and didn't need the 4 part name. The others were using the DW as the main data source but the query reached back into the OLTP database for some stuff that was not in the DW and was therefore a linked server and these still need the linked server but the server name has changed. I guess the 'proper' way to fix these would be to use synonyms and map the synonyms to the linked server tables (or put the stuff in the DW in the first place!)
at least by downloading them from the report server and editing in BIDS, I have copies of the before and after images. One problem we have is that at least three different developers have had a go at some of these reports and so there is no guarantee that the copy on your local machine matches that on the server. How do others do source code control for reports. Do you have one project that contains all the reports one project per report / report area, or do you just source code control the .rdl files?
May 27, 2016 at 6:30 am
aaron.reese (5/26/2016)
How do others do source code control for reports. Do you have one project that contains all the reports one project per report / report area, or do you just source code control the .rdl files?
We use one project for all reports related to a specific database and lock that up in our source control. We don't have a lot of cross-database reporting so this method works fine for us.
The only problem that we consistently run into is that we use SQL authentication for the DataSources and our source control (not sure if it's universal) will clear out the password in the project. This caused a number of issues deploying "quick changes" until we found the setting that prevented DataSources from being overwritten on the report server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply