SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk fix hard coded server references


Bulk fix hard coded server references

Author
Message
aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7823 Visits: 1034
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].[table]



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.
Neil Burton
Neil Burton
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14049 Visits: 11847
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].[table]



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.


On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7823 Visits: 1034
@BWFC

Yup, It sounds dangerous to me too, hence why I was asking the question :-D
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.
Lowell
Lowell
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224217 Visits: 41617
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Michael L John
Michael L John
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15033 Visits: 9413
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/
Neil Burton
Neil Burton
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14049 Visits: 11847
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.


On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help
aaron.reese
aaron.reese
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7823 Visits: 1034
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?
LightVader
LightVader
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 3200
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.



The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search