My picture album
My code library
Search my site
SQL Server Articles New
Receive & Send money over the net easily!
users are not able to run the replication ActiveX
control based programs simultaneously to
synchronize Jet 4.0 databases. The program is
throwing out the following errors:
21 036: Another merge agent for the
subscription(s) is running.
Does this mean multiple users can not do
No, you should be able to run the
program from multiple workstations
You are seeing this error, because in all your
programs you are using the same Subscriber
Database name. So, before distributing your
application to all your users, make sure you set
the .SubscriberDatabase property to a different
name in all your programs.
to start distribution agent. It is failing with
the following error:
21036: Another distribution agent for the
subscription(s) is running.
But nothing is getting replicated. Stopping and
starting SQL Agent didnt help.
Sometimes it is possible that an
orphan process could be running with the same
name as the distribution agent, and restarting
SQL Agent may not terminate it. This will prevent
the user from starting the distribution agent.
You can verify this orphan process by querying
sysprocesses. Under Replication Monitor, Agents,
Distribution Agents, right click on the
distribution agent select Agent Properties. From
the properties dialog box, copy the name of the
distribution agent and execute the following
select * from master..sysprocesses where
program_name = Name_of_the_agent
From the output verify the waittype.
If you restart the SQL Server, this problem will
be solved. If you can not restart SQL Server,
then copy the spid of the distribution agent from
the above agent and kill it using the Kill
Eg: Kill 21
non-dbo owned table when merge replicated to the
subscriber, is being owned by dbo. Is there a way
to maintain table ownership in merge replication?
GUI permits to specify a
destination table owner in case of transactional
and snapshot publications. Through GUI we can not
specify the destination table owner in case of
Here is a workaround:
Create the preferred login and user on the
subscribing SQL Server.
Before the merge agent runs for the first time,
right click on the merge agent, select
Agent properties. Go to
Steps tab. Double click on the
Run Agent step. In this dialog box
you will see a Command text box.
Append the following to the command string.
Run the merge agent. This should create the
destination tables with the specified user as the
UPDATE on the publisher gets replicated as a
DELETE/INSERT pair instead of direct UPDATE?
An UPDATE gets replicated as a
DELETE/INSERT pair when a user updates:
- any column which is a part of the primary key
- any column on which there is a filter clause
- any column on which there is a clustered index.
to specify a remote distributor for a
Desktop/MSDE publisher. Why?
For Desktop edition or Microsoft
Data Engine, we can not specify a remote
distributor. This is a restriction imposed on
these editions of SQL Server.
start replication agents programmatically?
Replication agents (Snapshot,
Distribution, Logreader and Merge agents) run
according to the schedules you have set while
setting up the replication scenario. But you can
always override those schedules and start the
agents from your applications programmatically!
Here I will discuss a couple of ways to start the
Use sp_start_job: In the Enterprise Manager of
your Distributor, select the Agent under
'Replication Monitor' -> Agents. In the right
hand side pane, right click on the agent and
select 'Agent properties' from the popup menu.
From the Agent properties dialog box, copy the
name of the agent, as we need to pass it to
sp_start_job. Now, open ISQLW, connect to your
Distributor, and execute the following command.
Replcae the with the one you copied from Agent
properties dialog box.
EXEC msdb..sp_start_job <Agent Name>
Note that sp_start_job results in an asynchronous
operation, and the control returns to the next
statement of your code, as soon as the agent
Run the agents from the command prompt as EXEs:
In SQL Server 7.0 all the replication agents are
implemented as EXEs (Executable files). So, you
can run them directly from the command prompt, or
schedule them with the NT's AT command. Use
logread.exe, distrib.exe, snapshot.exe and
replmerg.exe to run logreader agent, distribution
agent, snapshot agent and merge agent
Go to the same place in the Enterprise Manager.
In the Agent properties dialog box, go to Steps
tab. Double click on 'Run Agent' step. Copy the
text available in the Command text box, as we
need to pass it to the EXE file. Open Command
Prompt and run any of the following commands,
based on your requirement. Replace with the text
you copied from 'Run agent' step.
Note that, these are ActiveX EXEs and they open
up a new console window whenever you run them.
You have to press CTRL+C to close these console
Use xp_cmdshell: Go to the same place in the
Enterprise Manager. In the Agent properties
dialog box, go to Steps tab. Double click on 'Run
Agent' step. Copy the text available in the
Command text box, as we need to pass it to the
xp_cmdshell command. Now, open ISQLW, connect to
your Distributor, and execute the following
command. Replace the with the text you copied
from 'Run agent' step.
EXEC master..xp_cmdshell "distrib.exe
Use the replication ActiveX controls: SQL Server
7.0 comes with replication ActiveX controls which
can be used via programming like Visual Basic,
Visual C++ etc. For more information see SQL
Server Books Online
Use the SQL DMO objects: You can manipulate
replication agents using SQL DMO objects also,
again these objects can be used in programming
languages like VB, VC++ etc. For more information
see SQL Server Books Online.
to drop a table even after removing replication.
I'm getting the following errors, when I try to
drop or alter the table:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 't1' because it is
published for replication.
Server: Msg 4931, Level 16, State 1, Line 1
Cannot add columns to table 't1' because it is
being published for merge replication.
Is there a way out?
To avoid this situation, you
should always remove replication in the following
- Delete the subscriptions
- Delete the publications
- Disable publishing
Deleting publications without unsubscribing or
disabling publishing without deleting
publications results in this situation.
Anyways, if you are seeing the above mentioned
errors, try any or all of the below workarounds
one after another in the order described below.
1. EXEC sp_removedbreplication
2. Execute the following procedure on the
problematic database and pass the table name as
EXEC sp_msunmarkreplinfo 'your_table_name'
3. You may not need this method, but take this as
a last resort.
EXEC sp_configure 'allow',1
reconfigure with override
update sysobjects set replinfo = 0 where name =
EXEC sp_configure 'allow',0
reconfigure with override
NOTE: If you don't follow the proper replication
removal order, you may see the same problem with
replicated stored procedures!
(Same problem can occur with replicated stored