(Continued from previous part...)

Unable to specify a remote distributor for a Desktop/MSDE publisher. Why?

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 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 with the one you copied from Agent properties dialog box.

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 with the text you copied from 'Run agent' step.

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 with the text you copied from 'Run agent' step.

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.

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.

Unable to drop a table even after removing replication. Getting the following errors, when 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)

(Continued on next part...)