My technical skills
My favorites
My picture album
Shortcut keys
My code library
VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources
Search my site
Sign my guestbook
Contact information
SQL Server Articles New
Receive & Send money over the net easily!
| Multiple
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 synchronization simultaneously? No, you should be able to run the program from multiple workstations simultaneously. 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. |
| Unable
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 didn’t 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 query. 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 command. Eg: Kill 21 |
| A
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 merge replication. 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. -SubscriberLogin Destination_owner_name –SubscriberPassword Password –SubscriberSecurityMode 0 Run the merge agent. This should create the destination tables with the specified user as the owner. |
| When an
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. |
| Unable
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. |
| How to
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 agents programmatically: Method #1: 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 EXEC msdb..sp_start_job <Agent Name> GO 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 starts. Method #2 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 respectively. 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 Distrib.exe <Parameters> Logread.exe <Parameters> Sanpshot.exe <Parameters> Replmerg.exe <parameters> 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 windows. Method #3 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 EXEC master..xp_cmdshell "distrib.exe <Command list>" Method #4 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 Method #5 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. |
| Unable
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 order: - 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 'your_database_name' 2. Execute the following procedure on the problematic database and pass the table name as the parameter: 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 go reconfigure with override go use your_database_name go update sysobjects set replinfo = 0 where name = 'your_table_name' go EXEC sp_configure 'allow',0 go reconfigure with override go 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 procedures) |