Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Saturday, January 28, 2017 5:08 PM
We have a customer's server that is setup and running SQL Server 2014 with no issues.
They then changed the server name (from SERVER1 to SERVER1-OH) and i ran the commands to get SQL Server setup correctly.
Now, SELECT @@servername returns SERVER1-OH as it should.
but, when trying to setup SQL Merge Replication:
exec sp_adddistributor @distributor='SERVER1-OH'
it returns an error:
When executing sp_adddistributor for a remote Distributor, you must use a password. The password specified for the @password parameter must be the same when the procedure is executed at the Publisher and at the Distributor.
I have also tried:
exec sp_adddistributor @distributor=@@servername
And that also fails with the same message.
I also tried:
exec sp_adddistributor @distributor='SERVER1'
that returned could not connect to server 'SERVER1' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name.
I honestly can't remember, but i might have tried to setup replication before the server name was changed (I didn't request the server name change, their IT people did it themselves).
So, where in the internals of SQL could the old name still be in there so it is thinking that its name is SERVER1 instead of SERVER1-OH, that the @@servername is returning.
No idea where to go.
Thanks in advance.
darin
All replies (13)
Saturday, January 28, 2017 6:43 PM
Hi, the error indicates a password issue, you may need to look at using sp_changedistributor_password to resolve https://msdn.microsoft.com/en-us/library/ms178617.aspx additional info https://msdn.microsoft.com/en-us/library/ms151735.aspx
'Do not change the password for the distributor_admin manually. Always use the sp_changedistributor_password stored procedure, or the Distributor Properties or Update Replication Passwords dialog boxes in SQL Server Management Studio, because password changes are then applied to local publications automatically.'
Saturday, January 28, 2017 6:55 PM
I can agree with that IF this is a remote distributor - but it isn't - the publisher and the distributor are the same machine.
i even tried to create the replication publication using the SSMS wizard, it also fails showing the server name as SERVER1 and not SERVER1-OH.
THe error is "could not connect to server 'SERVER1' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name."
So there is something wrong inside SQL somewhere.
I told SSMS to create the publication, but just script it. The script that SSMS created with the create publication wizard has:
exec sp_adddistributor @distributor = N'SERVER1', @password = N''
So even SSMS wizard thinks the server name is not -OH
darin
Saturday, January 28, 2017 7:08 PM
Strange, typically the standard drop/add resolves a rename
sp_dropserver 'SERVER1';
GO
sp_addserver 'SERVER1-OH', local;
GO
Saturday, January 28, 2017 7:10 PM
Right. i did all of that and the
select @@servername
returns SERVER1-OH as it should.
And i have restarted the SQL service multiple times and that hasn't helped either.
darin
Saturday, January 28, 2017 7:23 PM
Wonder if replication has an issue with the dash in the name possibly [SERVER1-OH] may work.
Saturday, January 28, 2017 7:38 PM
I think you problem is with the name stored in sys.servers
select datasource from sys.sysservers where srvname='repl_distributor'
You will need to drop and add the repl_distributor to get this to work again.
You will need to assign your own password to the distributor_admin account.
This should be done using the sp_changedistributor_password stored procedure.
This is a little tricky to get configured you may need to change the replication password for the sever as well using the distribution database dialogs. Right click on the replication folder and select update replication passwords.
Saturday, January 28, 2017 7:48 PM
select datasource from sys.sysservers where srvname='repl_distributor'
returned SERVER1
So that was what i was expecting, the incorrect name
exec sp_dropdistributor @no_checks=1,@ignore_distributor=1
and that returned
The Distributor is not installed.
So we now know where the invalid name is.
I did exec sp_removedbreplication 'db1' (no erros, but the replication wasn't setup anyway) then the dropdistributor and that still failed.
The replication distribution database isn't setup either.
darin
Saturday, January 28, 2017 7:51 PM
It is trying to locate your remote distributor not your local one.
Right now you are in a bad situation. You can move forward but not without considerable hacking. You will need to create a new distribution database local on your machine and then configure your publications to use it.
How many subscribers do you have and how difficult will it be to re-deploy them?
Saturday, January 28, 2017 8:15 PM
1 distributor
7 publications
1 subscriber
The select * FROM sys.sysservers
shows 2 server: the SERVER1-OH and the repl_distributor as SERVER1.
Can i do something like a SQL update to change the server name associated with the repl_distributor entry?
darin
Friday, March 3, 2017 7:52 PM
A continuation on this:
I ended up creating the replication the other way - from what i was going make the subscriber, i made that one the publisher, so replication is working.
But, another problem now found on SERVER1-OH because of the name. We create overnight jobs to run. They are not being created because of the same server name issue:
ERROR: Index #0
Message: The specified @server_name ('SERVER1-OH') does not exist.
LineNumber: 88
Source: .Net SqlClient Data Provider
Statement: USE msdb;
EXEC msdb.dbo.sp_add_jobserver @job_name='OvernightJob', @server_name = N'SERVER1-OH'
So it appears that somewhere deep within the SQL system there is a place that says SERVER1 is the server name, and it has to do with the SQL agent.
In addition, i am looking at the SQL Server Agent log history, and that shows:
Local computer is SERVER1 running windows NT 6.2 (9200)
In addition, there are entries in the SQL Server log that say:
Server name is "SERVER1". this is informational.
So i DO NOT know what to do. The select @@servername returns the correct name: SERVER1-OH.
darin
Friday, March 3, 2017 8:03 PM
More info:
SSMS i connect using SERVER1-OH
Looking in SSMS at the server properties, it shows:
Server Properties - SERVER1-OH (in title line)
Name: SERVER1
I can ping SERVER1-OH and it returns only the IP6 info
I can also ping SERVER1 and it returns only IP4 info.
ping -a IP4address returns SERVER1-OH
The control panel / system shows computer name: SERVER1-OH
Arg. i see nowhere the SERVER1, yet it is being shown many different places.
darin
Friday, March 3, 2017 8:12 PM
Getting back to your early problem sp_dropserver - sp_addserver should have corrected this so that your server name is correct in sys.servers.
Right now your SQL Server Agent thinks it is part of a mutli server administrative unit. You have to defect from this server.
IIRC you do it like this:
exec sp_msx_defect
You may need to script out your jobs before doing this.
Friday, March 3, 2017 8:16 PM
I have noticed when i tried to manually create a job and then looked at the connection properties, that shows under the server environment, the computer name is SERVER1.
I am really leaning to this being a windows issue, that somewhere in windows it still has the SERVER1 name. Could that be?
darin