6.5 problem synchronising identity values

6.5 problem synchronising identity values

Post by Chortle » Wed, 08 Sep 1999 04:00:00



I need to replicate identity values between version 6.5 publishers and
subscribers. Native mode synchronisation creates new identity values in the
subscribing table, i.e. not what I want. I've tried character mode sync and
this tries to insert the publishing table identity but fails because it
doesn't switch insert_identity on. How can I get synchronisation to copy
the identity values from the publisher to the subscriber?

Thanks.....

 
 
 

6.5 problem synchronising identity values

Post by Ernie DeVo » Wed, 08 Sep 1999 04:00:00


On Tue, 07 Sep 1999 02:40:33 -0700, "Chortler"


>I need to replicate identity values between version 6.5 publishers and
>subscribers. Native mode synchronisation creates new identity values in the
>subscribing table, i.e. not what I want. I've tried character mode sync and
>this tries to insert the publishing table identity but fails because it
>doesn't switch insert_identity on. How can I get synchronisation to copy
>the identity values from the publisher to the subscriber?

Timestamps and identity fields don't replicate well. Something I've
gotten to work is turn that field into just plain text on the
subscriber. This would work unless for some freaky reason your
applications NEED the subscriber to have a real identity. It all
depends on your topology.

I fought with our application developers for a month on this issue.
They swore that field on the subscriber HAD to be an identity column,
but the permissions for users didn't even have update or insert rights
to that table (on the subscriber). If it's a read-only situation, who
cares? Was a case of what I call 'developer myopia', otherwise known
as 'Let's-make-everything-as-complex-as-possible' syndrome.

Ernie DeVore

 
 
 

6.5 problem synchronising identity values

Post by ghuang.. » Wed, 08 Sep 1999 04:00:00


There is a KB article for SQL Server 6.5 there--"How to set up
replication on tables with an identity column". Check it out
http://support.microsoft.com/support/kb/articles/q190/6/90.asp

Hank



Quote:

> I need to replicate identity values between version 6.5 publishers and
> subscribers. Native mode synchronisation creates new identity values
in the
> subscribing table, i.e. not what I want. I've tried character mode
sync and
> this tries to insert the publishing table identity but fails because
it
> doesn't switch insert_identity on. How can I get synchronisation to
copy
> the identity values from the publisher to the subscriber?

> Thanks.....

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
 
 
 

6.5 problem synchronising identity values

Post by Steve Robinso » Wed, 08 Sep 1999 04:00:00


Ernie,

Why turn it to text ?  You could just leave it as an int / smallint or
tinyint or in the case of a timestamp binary (8)

That way you could keep the underlying select clauses the same - no need to
convert or rewrite

I hope this was of use.

Steve Robinson SQL Server MVP.


>On Tue, 07 Sep 1999 02:40:33 -0700, "Chortler"

>>I need to replicate identity values between version 6.5 publishers and
>>subscribers. Native mode synchronisation creates new identity values in
the
>>subscribing table, i.e. not what I want. I've tried character mode sync
and
>>this tries to insert the publishing table identity but fails because it
>>doesn't switch insert_identity on. How can I get synchronisation to copy
>>the identity values from the publisher to the subscriber?

>Timestamps and identity fields don't replicate well. Something I've
>gotten to work is turn that field into just plain text on the
>subscriber. This would work unless for some freaky reason your
>applications NEED the subscriber to have a real identity. It all
>depends on your topology.

>I fought with our application developers for a month on this issue.
>They swore that field on the subscriber HAD to be an identity column,
>but the permissions for users didn't even have update or insert rights
>to that table (on the subscriber). If it's a read-only situation, who
>cares? Was a case of what I call 'developer myopia', otherwise known
>as 'Let's-make-everything-as-complex-as-possible' syndrome.

>Ernie DeVore

 
 
 

6.5 problem synchronising identity values

Post by Ernie DeVo » Wed, 08 Sep 1999 04:00:00


On Tue, 7 Sep 1999 16:25:16 +0100, "Steve Robinson"


>Ernie,

>Why turn it to text ?  You could just leave it as an int / smallint or
>tinyint or in the case of a timestamp binary (8)

>That way you could keep the underlying select clauses the same - no need to
>convert or rewrite

Text was just what I used to fix my problem with the timestamp. You're
exactly right. Changing an identity to int (or similar) WOULD be much
easier. Either way, problem solved! Does anyone know if 7.0 replicates
these fields properly?

Ernie DeVore

 
 
 

6.5 problem synchronising identity values

Post by Charles » Fri, 17 Sep 1999 04:00:00


Nope, 7 does not replicate identities well.
I'm running repl across 4 sites, and ditched identities in favor of using my
own keys.
Quote:>Text was just what I used to fix my problem with the timestamp. You're
>exactly right. Changing an identity to int (or similar) WOULD be much
>easier. Either way, problem solved! Does anyone know if 7.0 replicates
>these fields properly?

 
 
 

6.5 problem synchronising identity values

Post by Matthew Duklet » Thu, 16 Dec 1999 04:00:00


Charles,

How are you currently generating your indexes across your 4 sites?  I also have
the same problem with Identity columns, I resorted to using auto-generated GUID
values for some indexes.  I would like a stored procedure to auto generate a
site-unique value, but I don't know how to approach it.  Perhaps you could share
your method, thanks.

Matthew


> Nope, 7 does not replicate identities well.
> I'm running repl across 4 sites, and ditched identities in favor of using my
> own keys.

> >Text was just what I used to fix my problem with the timestamp. You're
> >exactly right. Changing an identity to int (or similar) WOULD be much
> >easier. Either way, problem solved! Does anyone know if 7.0 replicates
> >these fields properly?

 
 
 

1. SQL 6.5 Inserts duplicate identity value !?

Can anybody tell my why SQLServer trys to insert duplicat primary key which
is identity value and raises error?

Help me please, what am i doing wrong?

--
Andrew Morozov, MCP         Web-master

+7 8312 384255           Nizhny Novgorod
+7 8312 362522(fax)           Russia

2. What is ODBC?

3. Bulkcopy and identity values (SQL srv 6.5 sp5a)

4. Quick Reports Question

5. Return Identity Value (MS SQL Server 6.5)

6. MVS mainframe databases downsizing newsgroup

7. SQL 6.5 Inserts duplicate identity value !?

8. Transfer data from a database to another

9. @@IDENTITY problem with VB432/SQL Server 6.5

10. SQL 6.5 Identity Col. Problem

11. Problems with identity columns in SQL Server 6.5

12. IDENTITY PROBLEMS in SQL 6.5

13. Problems with identity columns in SQL Server 6.5