|
|
|
SSC Eights!
 
Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 882,
Visits: 1,648
|
|
On starting a new job I have discovered that a number of databases seem to have been installed before the server was renamed, as both select * from sysservers and select @@servername show a different name to the actual server, I assume that the name being displayed is the original name. I know how to fix this, but what I'm not sure of is the impact of changing this, at the moment the application seems to be connecting OK and as far as I can see the connection to the server is via ODBC and that is using the correct servername. Any help appreciated...
Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 4:33 PM
Points: 21,728,
Visits: 5,982
|
|
The SQL Server name needs to match the Windows server name.
Sp_dropserver
go
sp_addserver (new name), local
will fix the naming. For clients, they'll need to repoint to the new name unless they are using IP (bad idea) or DNS/FQDN name. In that case, the DNS can be changed to point to the new server.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Saturday, September 26, 2009 12:59 PM
Points: 11,
Visits: 23
|
|
Hi,
If you are working on MS SQL SERVER 2000, You can update the master..sysservers table by enableing the sp_configure 'allow updates',1.
update master..sysservers set srvname='New name' ,Datasource='New name' where srvid=0
Regards,
Reddyprasad.A
MCITP
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 3,414,
Visits: 8,760
|
|
addankireddyprasad (10/8/2007)
Hi,
If you are working on MS SQL SERVER 2000, You can update the master..sysservers table by enableing the sp_configure 'allow updates',1.
update master..sysservers set srvname='New name' ,Datasource='New name' where srvid=0
Regards,
Reddyprasad.A
MCITP
Bad Advice. bad, bad,bad.
never update sys tables directly. never. use the provided stored procedures.
just updating the table directly does not take all possibilitites into consideration.. . just do sp_helptext sp_addserver and look at the logic the code goes thru, as well as the multiple updates it does.
look at the validation, and the updating of status that goes on behind the scenes when you do it the approved way. do you know what changing the status does or does not do? wonder if there is a reason the make the name lower case and not whatever you type in?
create procedure sp_addserver
@server sysname, --server name
@local varchar(10) = NULL, -- NULL or 'local'
@duplicate_ok varchar(13) = NULL -- NULL or 'duplicate_ok'
as
-- VARS
declare @retcodeint
-- CHECK IF SERVER ALREADY EXISTS
if exists (select * from master.dbo.sysservers where srvname = @server)
begin
if @duplicate_ok = 'duplicate_ok'
return (0)
raiserror(15028,-1,-1,@server)
return (1)
end
-- VALIDATE @local PARAMETER
if @local is not null
begin
select @local = lower(@local)
if @local <> 'local'
begin
raiserror(15379,-1,-1,@local)
return (1)
end
-- ERROR IF ALREADY HAVE A LOCAL SERVER NAME
if exists (select * from master.dbo.sysservers where srvid = 0)
begin
raiserror(15090,-1,-1)
return (1)
end
end
-- ADD THE SERVER (CHECKS PERMISSIONS, ETC)
execute @retcode = sp_addlinkedserver @server
if @retcode <> 0
return @retcode
-- SET THE SERVER ID IF LOCAL OPTION SPECIFIED
if @local = 'local'
begin
declare @srvid smallint
-- UPDATE DEFAULT MAPPING CREATED BY sp_addlinkedserver
select @srvid = srvid from master.dbo.sysservers where srvname = @server
update master.dbo.sysxlogins set srvid = 0 where srvid = @srvid
update master.dbo.sysservers
set srvid = 0,
schemadate = getdate()
where srvname = @server
end
-- FOR COMPATIBILITY, TURN OFF THE data access SERVER OPTION
execute @retcode = sp_serveroption @server, 'data access', 'off'
if @retcode <> 0
return @retcode
--SET 'local login mapping', 'off' (make rpc-s behave as in 6.5)
update master.dbo.sysservers
set srvstatus = srvstatus & ~32, schemadate = getdate()
where srvname = @server
-- SUCCESS
return (0) -- sp_addserver
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! If you can, avoid pseudocode and show us your actual query...it makes it much better for you to see a good example with your tablenames you'd understand, and be able to copy and paste for immediate testing.
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 4:33 PM
Points: 21,728,
Visits: 5,982
|
|
I agree with Lowell. Don't update the system tables. Especially in this case. These are two simple procs to run to fix this.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, October 20, 2009 10:20 AM
Points: 1,038,
Visits: 271
|
|
I would second Steve on both of his advise.
sopheap
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 6:46 AM
Points: 882,
Visits: 1,648
|
|
I'm fine with fixing this with sp_dropserver sp_addserver what I'm not a hundred percent sure of is whether the 100's of users connected will have any problems if I fix this. I think it should be transparent but would like some confirmation.
Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Yesterday @ 4:33 PM
Points: 21,728,
Visits: 5,982
|
|
Are clients connecting now? They shouldn't be if they're using server name. If they're using IP, it should work.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 5,987,
Visits: 7,927
|
|
Actually if they're using IP, and DNS hasn't been updated (or rather - DNS still has both names pointing to the IP address) - BOTH aliases should work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 3,074,
Visits: 2,184
|
|
Also, keep in mined that these changes will not take effect until you stop/start SQL Server.
|
|
|
|
