I'm using RMO to connect to a MSSQL 2000 Server to manage replication. I've successfully connected to the server, created the distributor, publication, articles and subscriber. However, when i try to manage the agents (ie. TransPublication.StartSnapshotGenerationAgentJob) it says the method is only supported on MSSQL 2005.
Is there any way I can control the agents from RMO on sqlserver 2000?
Tore BirkelandHi again.
After a bit of searching, i found the assembly Microsoft.SqlServer.Replication.dll.
This contains, among other things, the SnapshotGenerationAgent class, which apparently can create snapshot-jobs on MSSQL 2000.
However, the commands created by this class does not seem to work properly: The distribution agent fails with the error 'Invalid distribution command type.'. The failed command is '[ImmediateExecution]ALTER TABLE [dbo] ADD CONSTRAINT [PK_Artikkel] PRIMARY KEY CLUSTERED'.
using sp_browsereplcmds, i can see that this [ImmediateExecution]-command is executed just before the .idx script, which contains the same sql statement. (?!?)
Using enterprisemanager to start the snapshot agent, and sp_browsereplcmds, i see that there is no '[ImmediateExecution]'-command created, but otherwise it looks very much the same.
Is there any way in which i can control which commands are created by the SnapshotGenerationAgent class?
please help!
Tore|||Hi Tore,
The strange-looking commands that you saw are acutally meant for a SQL2005 version of the distribution agent and the SQL20005 version of sp_MSget_repl_commands is supposed to filter them out when a < SQL2005 version of the distribution agent connects. The reason why we have the strange-looking commands as well as the same statements in the .idx files is for interoperability with both SQL2005 and < SQL2005 distribution agents. (SQL2005 distribution agents will use the strange-looking commands while <SQL2005 distribution agents will use the .idx files)
Based on what I said in the previous paragraph, it would appear that you are using the SnapshotGenerationAgent class on a pure SQL2000 environment (Is your distributor SQL2000 as well? Or, is it running an earlier version of the SQL2005 CTP|BETA?) which is not a supported scenario. I am a bit surprised that you were able to generate the snapshot without errors at all so I guess we should probably put in a more explicit check to prevent this in the future.
Unfortunately, there really is no easy way to start a snapshot agent via RMO in a SQL2000 environment (SQL2000 doesn't have the system procedures required for this functionality hence the reason why you hit the error in your original post.) The best way to go about starting the snapshot agent in a SQL2000 environment is to find out the job_id and job_name of the snapshot agent and then use the SMO Job object or simply call sp_start_job to start it.
HTH
-Raymond|||Hi Raymond, thanks for your answer.
Yes, for now, i'm using Sql2000 both at publisher/distributor and subscriber, but at Michiels talk at TechEd Amsterdam, i became a little excited about Smo and I hope i'll be able to use it in my scenario :). Our product will support Sql2005 when it is released, but we'll have to support Sql2000 for quite a few years still.
I wonder, is there any overview of which features of Smo/Rmo are supported on Sql2000? You say that SnapshotGenerationAgent is not supported on a Sql2000 distributor, however, it works splendidly on merge replication, only not for transactional replication. :)
Another problem i run into, is when i try to access the SynchronizationAgent member of the MergeSubscriber class. When i try to access the property, the following exception occurs:
ex.ToString "Microsoft.SqlServer.Replication.ComErrorException (0x80040154): Class not registered
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent..ctor()
at Microsoft.SqlServer.Replication.MergeSubscription.get_SynchronizationAgent()
Is the class not registered in the sense of a com component not registered, or does this relate to my servers running Sql2000? (on my client computer i have only installed the Sql2005 client tools, not the server itself)
Regards,
Tore|||
Hi Torre, I am glad that you are excited about SMO\RMO but at the same time, I feel really bad that what I am about to say is going to considerably dampen that excitement :(
By unsupported, we (as in folks from MS) typically mean that a particular configuration is not tested and\or key decision makers don't feel that it is worth the effort to make it work. Things may "appear" great as you have discovered in the case of running the SnapshotGenerationAgent class against a SQL2000 distributor for a merge publication (although I have just put in a check to disable that scenario) but we really can't provide any guarantee that you won't get yourself into trouble. In hindsight, we should spell out more clearly what we actually support.
To give you a better idea of why we typically don't support downlevel distributors (i.e distributor version < replication agent version), you need to understand that unlike SMO\DMO, replication (and hence RMO) is geared more towards serving DBAs rather than application developers embedding replication components into their applications. And over the years (and the past couple of releases) of basing our upgrade model on being good enough for serving DBAs, we implicitly built in the assumption that when someone upgrade the distributor, all replication agents running at the distributor are upgraded to the same level and vice versa.
In fact, the reasons you hit the "Class not registered" error when you tried running the *SynchronizationAgent class in your SQL2000 distributor environment are that 1) the *SynchronizationAgent classes are nothing more than thin wrappers on top of the SQLMerge and SQLDistribution ActiveX controls, and 2) the binaries for backing those controls are simply absent since you haven't upgraded the SQL2000 distributor yet.
Perhaps we should really try to support downlevel distributors given that at least one customer would like to see such functionality but unfortunately you probably have to wait until the next release before we get our act together. All is not lost, however, despite what I have just said. To support SQL2000 distributor in your application, you need to use the SQLSnapshot, SQLMerge, and SQLDistribution ActiveX controls instead of the SnapshotGenerationAgent, MergeSynchronizationAgent, and TransSynchronizationAgent managed classes for running the replication agents although I can imagine that you would find special- casing for SQL2000 and SQL2005 distributors to be quite undesirable in your application.
-Raymond
|||Thank you for your swift reply, Raymond.I would really appreciate if you would look into the possibility of supporting downlevel distributors in the future. but for now, I will look into the SQL* ActiveX controls.
And btw, you haven't relly dampened my excitement; i'll just have to push harder for our customers to upgrade to Sql2005 when it ships ;)
again, thank you for your time,
-Tore
rive without a lot of issues? Or would it be easier to remove 2005 and just start from scratch? The sql version is 9.0. Any help or comments would be greatly appreciated.!!!!!