May 26, 2009 at 11:56 am
One of our client had to change the data server name with sql server 2005 enterprise edition installed.
I checked the server name using
SELECT @@SERVERNAME,SERVERPROPERTY('ServerName')
I got two different server names @@SERVERNAME gave me the old server name where as SERVERPROPERTY gave me the new/current server name.
I used sp_dropserver 'old server' which said that the server name does not exist. I cross checked this in sys.servers table and didn't found an entry for old server.
However, the application is working fine and also am able to connect to sql server using the new server name but am still getting 2 different server names through @@SERVERNAME and SERVERPROPERTY even i restarted the several times; also reporting service is not working.
when am configuring the reporting services thru am able to connect to the reporting services thru new server name but when configuring "Windows service identity" the dialog box is showing old server name and the text box is disabeled.
DO I have aby alternate way to get things started apart from re installation.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 26, 2009 at 12:04 pm
You still need to add the new server in using sp_addserver 'new server name', 'local';
You should also take a look at fixing the Windows groups that SQL Server creates. You will need to drop the SQL Logins, modify the group names in Users & Groups on the server, then re-add the groups and reset the appropriate permissions.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 12:33 pm
Jeffrey Williams (5/26/2009)
You still need to add the new server in using sp_addserver 'new server name', 'local';You should also take a look at fixing the Windows groups that SQL Server creates. You will need to drop the SQL Logins, modify the group names in Users & Groups on the server, then re-add the groups and reset the appropriate permissions.
Sry I forgot to mention that the new server name is already taken up by the sql server as it was reflected in the sys.servers table...also am not worried about the logins am worried about reporting services....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 26, 2009 at 12:59 pm
Not sure about reporting services - but, you can try changing the windows identity in SQL Server Configuration Manager.
When the system was renamed, you probably have the old IUSR accounts setup in IIS. Try changing those to the new systems IUSR accounts.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 3:20 am
Jeffrey Williams (5/26/2009)
Not sure about reporting services - but, you can try changing the windows identity in SQL Server Configuration Manager.When the system was renamed, you probably have the old IUSR accounts setup in IIS. Try changing those to the new systems IUSR accounts.
Well....u can't configure report server services from sql server configuration manager; u should use report server configuration manager....which in my case is not reflecting the new server name....
Also the IUSR account name has not changed, thus there isn't any need to reconfigure it........
However, thanks for ur efforts...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 27, 2009 at 8:37 am
Well...I have a solution....I hv to re install reporting services....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 28, 2009 at 7:48 am
I also have a problem with My SQL server name.
In this case, the SQL server host name was renamed, without renaming the SQL instance. When retrieving information from remote hosts, I got the old SQL name back in some reports.
A Select @@ServerName still gave me the old name, so I used the following commands:
sp_dropserver 'oldname'
sp_addserver 'newname’
After restarting the SQL services, a Select @@Servername now gives me NULL as output. Anyone knows why he isn't picking up the new name ? A new sp_addserver tells me that the entry already exists.
Can this have something to to with srvid ? sp_helpserver gives me id=0 for my server and select * from master..sysservers gives me srvid=1 ?
Thanks for your help
Grtz,
Tom
May 28, 2009 at 9:55 am
tom.dewit (5/28/2009)
I also have a problem with My SQL server name.In this case, the SQL server host name was renamed, without renaming the SQL instance. When retrieving information from remote hosts, I got the old SQL name back in some reports.
A Select @@ServerName still gave me the old name, so I used the following commands:
sp_dropserver 'oldname'
sp_addserver 'newname’
After restarting the SQL services, a Select @@Servername now gives me NULL as output. Anyone knows why he isn't picking up the new name ? A new sp_addserver tells me that the entry already exists.
Can this have something to to with srvid ? sp_helpserver gives me id=0 for my server and select * from master..sysservers gives me srvid=1 ?
Thanks for your help
Grtz,
Tom
it will not work only by restarting sql services...u should restart the system...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 28, 2009 at 11:38 am
Taken from BOL:
The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in
each server.
And, not sure if the 'local' is needed or not, but just in case,
sp_addserver 'newname’,'local'
May 28, 2009 at 12:37 pm
Richard M (5/28/2009)
Taken from BOL:The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in
each server.
And, not sure if the 'local' is needed or not, but just in case,
sp_addserver 'newname’,'local'
By "server" u refer to sql server or the machine that host sql server....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 28, 2009 at 12:41 pm
May 28, 2009 at 12:55 pm
Richard M (5/28/2009)
... in this case server refers to the machine that hosts the SQL Server instance....
bingo...u just proved my point...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
May 29, 2009 at 2:24 am
Thanks guys,
I added local to the sp_addserver command and will restart the server this evening to see if it works.
Grtz,
Tom
May 29, 2009 at 6:23 am
Don't forget to check over any DTS/SSIS packages you might have. It is very easy to hardcode a server name in those and not even realize it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply