RMO On Sql Server 2000

Hi.

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

Right join not working when joining 3 tables

Have 2 tables that are joined by a 3rd. I want to get all the date in both
tables whether they are connected or not.
There can be many policies per role. RolePolicies is the table that
connects the two.
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Roles] ADD
CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
CREATE TABLE [dbo].[Policies] (
[PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Policies] ADD
CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
(
[PolicyID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RolePolicies] (
[RoleID] [int] NULL ,
[PolicyID] [int] NULL
) ON [PRIMARY]
GO
INSERT Roles (Description) VALUES ('Manager')
INSERT Roles (Description) VALUES ('Director')
INSERT Roles (Description) VALUES ('User')
INSERT Policies (Description) VALUES ('Add')
INSERT Policies (Description) VALUES ('Edit')
INSERT Policies (Description) VALUES ('Delete')
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
Here the last RIGHT JOIN works fine and we get the Delete, even though it
isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT JOIN?
If I do it this way:
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
I get the extra line ("User").
RoleID PolicyID RoleID Description
-- -- -- --
1 1 1 Manager
1 2 1 Manager
2 1 2 Director
2 2 2 Director
NULL NULL 3 User
How do I get both the "User" as well as the "Add"?
Thanks,
TomTshad,
Is it a LEFT JOIN that you want instead?
If not what should the resultset look like?
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
> Have 2 tables that are joined by a 3rd. I want to get all the date in
> both tables whether they are connected or not.
> There can be many policies per role. RolePolicies is the table that
> connects the two.
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Roles] ADD
> CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
> (
> [RoleID]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Policies] (
> [PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Policies] ADD
> CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
> (
> [PolicyID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[RolePolicies] (
> [RoleID] [int] NULL ,
> [PolicyID] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT Roles (Description) VALUES ('Manager')
> INSERT Roles (Description) VALUES ('Director')
> INSERT Roles (Description) VALUES ('User')
> INSERT Policies (Description) VALUES ('Add')
> INSERT Policies (Description) VALUES ('Edit')
> INSERT Policies (Description) VALUES ('Delete')
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> Here the last RIGHT JOIN works fine and we get the Delete, even though it
> isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT
> JOIN?
> If I do it this way:
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> I get the extra line ("User").
> RoleID PolicyID RoleID Description
> -- -- -- --
> 1 1 1 Manager
> 1 2 1 Manager
> 2 1 2 Director
> 2 2 2 Director
> NULL NULL 3 User
> How do I get both the "User" as well as the "Add"?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> Is it a LEFT JOIN that you want instead?
>
No.
That is why I showed the 2nd statement which just takes away the Join that
worked. When it was gone it worked correctly and showed the role "User".

> If not what should the resultset look like?
It should look the same as the 1st result set with one more row showing the
role "User":
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
>|||tshad,
Try using FULL JOIN:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
ON RP.POLICYID = P.POLICYID
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> No.
> That is why I showed the 2nd statement which just takes away the Join that
> worked. When it was gone it worked correctly and showed the role "User".
>
> It should look the same as the 1st result set with one more row showing
> the role "User":
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> NULL NULL 3 User NULL
> NULL
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
That worked.
I also could do it with my statement (which really is the same as yours) by
replacing both "RIGHT JOIN"s with "FULL JOIN"s, as you suggested.
Not sure why the outside RIGHT JOIN would work and not the inside one.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
This works well, but how do I tell it not to display rows that have Nulls in
the result set in certain columns.
I actually used this with 3 tables and it works fine, but I didn't want to
display rows that Nulls in 2 of the columns of the result set.
This is a little confusing, I know.
But if you take the statement and change it to:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL
ON RP.POLICYID = P.POLICYID
You will get no results.
This makes sense, because all the PolicyID records in the actual table have
something in them.
But in the full join, there can be a Null in the PolicyID if the there is a
record in Policies, but not in RolePolicies.
This would be a ridiculous statement in this example, but I would like it to
display all the rows that have no nulls.
For example,
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
I want to test for either PolicyID or RoleID, after the result set is
created.
In my other, Select - I want to test for both being NULL.
The statement would be something like:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL AND R.ROLEID <> NULL
ON RP.POLICYID = P.POLICYID
In my example, I should get the same result set, but I actually get no
results.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
that
"User".
>
-- -- -- -- -- --
--
,
>
--
>