| Author |
Topic |
|
paulo.tolosa
Starting Member
6 Posts |
 Posted - 2009-08-06 : 14:03:57
|
| Hi everybody.I'm getting a problem that is driving me crazy. I would really appreciate any help from you.I have a BD instance that is named 'SRVBD1\INST01,0001'This is working properly, except when I need to create a LOCAL PUBLICATION on this server.The name of this instance was 'SRVBD1\INST01' in the past, and I don't no for what reason, administrators changed the name of the instance some time ago.First, I tried to create a local publication in the simple mode, right click -> new publication. In the first time it gave me an error (Something that was conflicting between the old name and the new name of the instance). I looked in internet for this error, and found this solution.EXEC sp_dropserver 'SRVBD1\INST01' -- Past nameGOEXEC sp_addserver 'SRVBD1\INST01,001', 'local' -- New nameGOThen I restarted the server and it worked properly. So I finally got in the CREATE LOCAL PUBLICATION WIZARD.I began to set up in the wizard but in the last window, when the wizard starts to create the publication, it gave me another error that looks similar to the first one that I solved before.The message is:"SQL Server could not configure 'SRVBD1\INST01,0001' as a Distributor....(Now the part that have something strange)Could not connect to server 'SRVBD1\INST01' because 'distributor_admin' is not defined as a remote login at the server. Verify that you have specified the correct login name. Changed database context to 'master'"The strange thing that I said, is the name of the server instance that the message show 'SRVBD1\INST01' that is the name of this instance in the past.I looked in the internet again, and I found an article that was talking about this SERVERPROPERTY('servername') that should be the same name of the @@servername variable, but here they are different. The SERVERPROPERTY is set up to the old name of the instance and the @@SERVERNAME is correct.I really think that the error that I am getting while trying to create my LOCAL PUBLISHER has something related with this property.I read something in the internet that was saying that we should not change this value for a lot of reasons. But I really don't know how to change this or if this could solve my issue.I looked in the sysservers table (master), and I found out that the process that I'm trying to do, is creating a register in this table with the column srvname = 'repl_distributor' but with the old instance name in the value of columns DATASOURCE and SRVNETNAME. Even if I change this value manually with the command:sp_setnetname 'repl_distributor', 'SRVBD1\INST01,0001'GOWich is bringing the value into correct value, the process is taking this value back to the old instance name and I get into the that I mentioned before.As I said in the beginning any help will be really appreciated! Thanks in advance!Paulo Tolosa |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
paulo.tolosa
Starting Member
6 Posts |
Posted - 2009-08-06 : 18:37:04
|
| Yeah.... The comma is there. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
paulo.tolosa
Starting Member
6 Posts |
Posted - 2009-08-07 : 08:41:11
|
| First, thank you for your attention.So, I think that this solution can really work for me, but I don't think that my customer will really appreciate this.Anyway, I'm looking for a solution that would not require me to reinstall this instance.If you have any other idea, please post it because I will continue looking for it.Thanks in advance!Paulo Tolosa |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
paulo.tolosa
Starting Member
6 Posts |
Posted - 2009-08-09 : 13:03:14
|
| I was talking with a DBA and he told me that the comma is used here to specify the port that is used to connect to sql server.The name of the service is not with the comma, it is just "INST01".But, if I try to connect just using "INST01" withou the comma it don't connect.I found another article that was saying to create a user "distributor_admin" and set this user as a remote user. I did it, but when the wizard that CONFIGURE DISTRIBUTION try to finish it is giving me another error that is: "SQL Server could not configure 'SRVBD1\INST01,0001' as a Distributor.Login failed for user 'distributor_admin'.Changed database context to 'Master'."Do you have any other sugestion?Thanks in advance! |
|
|
|
paulo.tolosa
Starting Member
6 Posts |
Posted - 2009-08-10 : 10:29:19
|
| Hi everybody.I finally got it!!!! I don't know for what reason, but using the following script it worked.Here goes the script:DECLARE @distributor AS sysname;DECLARE @distributionDB AS sysname;DECLARE @publisher AS sysname;DECLARE @directory AS nvarchar(500);DECLARE @publicationDB AS sysname;-- Specify the Distributor name.SET @distributor = 'SRVBD1\INST01,0001';-- Specify the distribution database.SET @distributionDB = N'distribution';-- Specify the Publisher name.SET @publisher = 'SRVBD1\INST01,0001';-- Specify the replication working directory.SET @directory = N'F:\MSSQL.1\MSSQL\ReplData';-- Specify the publication database.SET @publicationDB = N'DataBaseName';-- Install the server MYDISTPUB as a Distributor using the defaults,-- including autogenerating the distributor password.USE masterEXEC sp_adddistributor @distributor = @distributor, @heartbeat_interval = 10, @password = 'password', @from_scripting = 0;-- Create a new distribution database using the defaults, including-- using Windows Authentication.USE masterEXEC sp_adddistributiondb @database = @distributionDB, @security_mode = 1;GO-- Create a Publisher and enable your database for replication.-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor-- and use Windows Authentication.DECLARE @distributionDB AS sysname;DECLARE @publisher AS sysname;-- Specify the distribution database.SET @distributionDB = N'distribution';-- Specify the Publisher name.SET @publisher = 'SRVBD1\INST01,0001';USE [distribution]EXEC sp_adddistpublisher @publisher=@publisher, @distribution_db=@distributionDB, @security_mode = 1;GO-- ENDAfter this I just created my local publisher with the default wizard and got no errors.Thanks in advance!Best regards.Paulo Tolosa |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Replication - could not configure 'instance_name'