Prev: log reader status error
Next: SQL 2008 - Merge Replication - Filtered Publication - Updating Par
From: Stefan on 13 Mar 2010 09:45
Hi NG!

We just upgraded a SQL Server 2000 to 2005 and have a problem with
replication. The SQL Server 2005 should replicate its data to a SQL Server
2000. We created all publications and subscripitions but it fails.

We have some user defined data types in our tables and it seems that those
crash the replication. The agend reports that it cannot execute the CREATE
TYPE command on the SQL Server 2000. It seems that CREATE TYPE is only
available in SQL Server 2005 and not 2000.

We created the publication with the compatibility to replicate to a SQL
Server 2000 Server. The SQL Server 2005 Database uses combatibility level 90
and the SQL Server 2000 Database uses compatibility level 80 (max value).

hopefully someone can help us?

regards
Stefan

From: Ben Thul on 13 Mar 2010 10:31
Do you have the invocation of sp_addarticle that you used? If so,
check out the @schema_option parameter. Specifically, if bit 0x20 is
set. According to BOL, this will tell replication to convert user-
defined types to their base types at the subscriber. That should do
the trick.
--
HTH,
Ben

On Mar 13, 8:45 am, Stefan <Ste...(a)discussions.microsoft.com> wrote:
> Hi NG!
>
> We just upgraded a SQL Server 2000 to 2005 and have a problem with
> replication. The SQL Server 2005 should replicate its data to a SQL Server
> 2000. We created all publications and subscripitions but it fails.
>
> We have some user defined data types in our tables and it seems that those
> crash the replication. The agend reports that it cannot execute the CREATE
> TYPE command on the SQL Server 2000. It seems that CREATE TYPE is only
> available in SQL Server 2005 and not 2000.
>
> We created the publication with the compatibility to replicate to a SQL
> Server 2000 Server. The SQL Server 2005 Database uses combatibility level 90
> and the SQL Server 2000 Database uses compatibility level 80 (max value).
>
> hopefully someone can help us?
>
> regards
> Stefan

From: Ben Thul on 18 Mar 2010 21:59
Indeed, 0x20 was set (To verify, you can do "select convert(int,
0x0000000030034FF1) & convert(int, 0x20)". If it's non-zero, the bit
is set). What I do note from the BOL entry for sp_addarticle is that
there are certain conditions under which replication won't be able to
honor your request (I won't repeat them here; feel free to look it
up). Finally, I seem to remember that it's MS's recommendation that
you upgrade downstream components in replication before upstream (so
subscribers before distributor before publisher) as later versions
"understand" commands sent by earlier versions.
--
Ben
On Mar 18, 7:49 pm, Stefan <terraust...(a)gmail.com> wrote:
> Hi Ben!
>
> I created the publication with the assistant, however we saved all the
> scripts the assistant used and the @schema_option parameter was
> "0x0000000030034FF1".
> 0x20 was set I guess, am I wrong?
>
> Because of some other problems we decided to upgrade the subscriber to
> SQL Server 2005, so everything is fine now.
>
> Thank you very much for your help!
>
> Stefan


 | 
Pages: 1
Prev: log reader status error
Next: SQL 2008 - Merge Replication - Filtered Publication - Updating Par