Solved

"Could not find the distributor or the distribution database for the local server"

Posted on 2009-07-08
14
3,906 Views
Last Modified: 2012-05-07
I am unable to set up a publisher for merge replication on an SQLServer 2005 database.
I am using the replication wizard to add a publisher and get the following error message :-
"Could not find the distributor or the distribution database for the local server"
The distributor database is on the same server and is registered.
The server name is not NULL.
I have spent a long time on this problem but o no avail ?
Any suggestions please ?
0
Comment
Question by:alcindor
  • 7
  • 7
14 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 24802179
In SQL Server Management Studio, Right click on 'Replication' and select 'Configure Distributor'

HTH
0
 
LVL 2

Author Comment

by:alcindor
ID: 24802251
There is no 'Configure Distributor' option, there is a 'Distributor Properties' however, is that what you mean ?
If so then I have already looked at the distrubutor properties and it shows the distributor database and I have already checked that area and set sa and password.

Any other suggestions
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24803082
In that case, select 'Disable...' and repeat the setup. That should kick things into life.

HTH
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:alcindor
ID: 24803526
I had already tried that and have tried it again but in both cases I get the following error messages:-

"SQL Server could not disable publishing and distribution on 'server name'

Additional Information :
An Exception Occurred while executing a Transact SQL statement or batch
[Microsoft.Sqlserver.ConnectioIinfo]

The distributor has not been installed correctly. Could not disable database for publishing.
Changed database context to 'master'. [Microsoft SQL server error 20029]
0
 
LVL 2

Author Comment

by:alcindor
ID: 24811287
I have now uninstalled SQL server 2005 and re-installed it from scratch.
I still have the same problem.
I have been through the configure distributor opton from right-clicking on Replication and it completed without error indicating success.
I cannot however disable publishing an distribution and get the same error message as last mentioned.
Any ideas please ??
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24811375
Give me a moment, gonna have a play on a testbox.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24811532
OK,

First off, try this:

EXEC sp_get_distributor - What does it come back with? If 'Distribution Server' is NULL, then create a new database called 'distribution' then run the below statement:

EXEC sp_adddistributiondb 'Distribution'

I'm guessing this will fail with the below message:

"Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication."

Run SQL Setup and try the suggested.

HTH
0
 
LVL 2

Author Comment

by:alcindor
ID: 24813597
I have re-installed SQL Server again and checked that the replication option was selected
I have configured distribution with the default database name of distribution and success was indicated.

When I run exec sp_get-distributor I get the name of the server plus three fields indicating distribution db installed, is distribution publisher, has remote distribution publisher; values of 1,1 and 0 respectively.

when I run exec sp_adddistributiondb 'distribution' ,I get an error message asfollows:-
"Could not add the distribution database 'distribution'. This distribution database already exists.

I still get the same messages if I try to add a publication or disable publishing and distribution as described previously.

I am getting desperate ?
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24813735
The 2nd error I would have expected given the distributor is setup.

Have you enabled the DB for replication?

exec sp_replicationdboption 'DBName','publish','true'


?
0
 
LVL 2

Author Comment

by:alcindor
ID: 24813993
I ran exec sp_replicationdboption 'WQ01','publish','true' and got an error as follows:-

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'WQ01' has been set to false.


Roger
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 500 total points
ID: 24814073
Just to check the basics, What version of SQL Server are you using, what SP level ?

e.g. 2005 Enterprise 9.0.3257

Steve
0
 
LVL 2

Author Comment

by:alcindor
ID: 24814198
SQL Server 2005 Standard Edition 9.00.3042.00
Windows N.T. 5.00.2195   (Windows 2000 server) SP4

Roger
0
 
LVL 2

Author Comment

by:alcindor
ID: 24821301
I have resolved the problem!
I believe that the root cause was due to having imported a database from another server (attach to a copy of the MDF file) whereby the database had been published on another server.
I fixed the problem by using  exec sp_removedbreplication @dbname =  'databasename'
then adding a distributor and everything worked as expected except that the "Disable publishing and Distribution " doesn't work but I don't need to do that.
Thanks for you help,

Roger
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24946468
Excellent. Glad you got this one resolved!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql sp_addlinkedserver possible risks 3 68
Question About Creating Primary Key Constraints on Oracle Tables... 5 44
RDBMS and No sql database 4 57
SQL Error - Query 6 24
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question