Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert

Controling Names of Agents

Hello there
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>

continuous replication

Hi,
I have managed to set up continuous replication. However , i found out that
when there error such as "general network error" the replication stopped.
In such cases , won't it automatically re-do the synchronization ? and is
there any way to allow auto-synchronization if the network becomes "alive"
again ?
btw i have set up replication alerts if the agent failed
appreciate ur advice
tks & rdgs
Message posted via http://www.droptable.com
Either have your agent run continuously and restart every 5 minutes, or have
the agent on failure loop back to step 1.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a91e9a42b79b@.uwe...
> Hi,
> I have managed to set up continuous replication. However , i found out
> that
> when there error such as "general network error" the replication stopped.
> In such cases , won't it automatically re-do the synchronization ? and is
> there any way to allow auto-synchronization if the network becomes "alive"
> again ?
> btw i have set up replication alerts if the agent failed
>
> appreciate ur advice
> tks & rdgs
> --
> Message posted via http://www.droptable.com
|||Hi ,
Curently this is how the flow for the continuous replications works
1)Distribution Agent startup mesage , on success --> go to next step , on
failure --> go to next step
2)Run agent , on success -->
quit with success , on failure --> go to next step ( but have specified to
retry 10 times btw an interval of 1 mins)
3)Detect nonlogged agent shutdown, on success --> Quit with failure , on
failure --> quite with failure
from ur suggesstion of "having the agent loop back" at which step shld the
loopback occur and MUST all the steps ( 1 - 3) be performed ?
i am thinking of if step 2 fails , then i'll loop back to step1 but i am not
sure what step3 does or upon success from step2 , i can go to step3 ?
need ur advice
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>Either have your agent run continuously and restart every 5 minutes, or have
>the agent on failure loop back to step 1.
>[quoted text clipped - 11 lines]
Message posted via http://www.droptable.com
|||step 3 on failure should loop back to step 1
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:5a929eb6fdf53@.uwe...
> Hi ,
> Curently this is how the flow for the continuous replications works
> 1)Distribution Agent startup mesage , on success --> go to next step , on
> failure --> go to next step
> 2)Run agent , on success -->
> quit with success , on failure --> go to next step ( but have specified to
> retry 10 times btw an interval of 1 mins)
> 3)Detect nonlogged agent shutdown, on success --> Quit with failure , on
> failure --> quite with failure
> from ur suggesstion of "having the agent loop back" at which step shld
> the
> loopback occur and MUST all the steps ( 1 - 3) be performed ?
> i am thinking of if step 2 fails , then i'll loop back to step1 but i am
> not
> sure what step3 does or upon success from step2 , i can go to step3 ?
> need ur advice
> tks & rdgs
> Hilary Cotter wrote:
> --
> Message posted via http://www.droptable.com
|||tks for ur advice i'll try it out
Hilary Cotter wrote:[vbcol=seagreen]
>step 3 on failure should loop back to step 1
>[quoted text clipped - 29 lines]
Message posted via http://www.droptable.com

Continuous Merge Replication questions

Ok excuse my lack of knowledge in this department and hopefully someone
can help easily.
When using Merge replication with continuous updating subscribers, it
appears when there is a network outage (as everywhere will get sooner or
later) that it will not try to update until I manually push synchronize.
So that sort of makes it appear useless in my eyes.
Can someone tell me how I can avoid this problem. The only way I have
been able to get around is by setting the subscriber to update every
minute but this is not ideal.
Can the calls to publish and synchronize be coded? if so please help.
And the other query I have is what is the difference between continuous
updating subscribers and transactional replication? Am I missing something?
Cheers,
Tim
You could hardcode the jobsteps to work in a loop (step 3 -> step 2) to
have it automatically restart.
For subscriber updates, merge is similar to transactional replication with
queued updating subscribers. A few differences...
TR works on a transaction basis while merge works on changed data ie 1000
updates to a row will be 1000 stored proc calls for tr while 1 updated row
in merge.
Text and Image changes are treated differently.
Requirement for PK in TR.
More conflict resolvers in merge than queued updating subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

continuous merge replication - event log

Hi

I have set up merge replication and it works nicely.

I have set it up to work continuously, because I thought that if it can't find the subscriber or is offline then that's fine it will just sync again when it's back on line.

This is true

BUT it keeps throwing lots of messages into the event log to tell me the merge has failed.

SO

a. Can i just turn off the error reporting

or

b. How can I get it to sync this way automatically on connection without the error messages

thanks

ICW

Unfortunately there's nothing built in to suppress these messages. Maybe instead of running continuously, you can schedule it to run frequently, like every 10 minutes?

Continuing on error...

Is it possible to continue on with the newer replication operations even when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
Yes. See the page titled 'distribution agent utility' in SQL Server Books
Online. Distribution agent has a parameter caleld -SkipErrors, that can be
used for this purpose.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||The correct page title is "Replication Distribution Agent Utility"
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||there is also the continue on data consistency errors, which you can access
by right clicking on your distirbution agent, selecting agent profiles, and
then selecting this profile.
After changing your profile you need to stop and start your distribution
agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
> Is it possible to continue on with the newer replication operations even
when
> an error occurs ? I have set up transaction replication between two SQL
> servers. When an error occurs, all the subsequent changes to the database
are
> not replicated, even if they might be error - free. Can this be done ?
> Prakash.

constraints implemented in Triggers and replication

Hi Friends,
I have some problems with my merge replication because i have the
constraints created with Erwin implemented in triggers not like foreign
keys, i'm looking for a tool or strategy for converting this constraints to
FK's.
Please help.
Hi Paul,
My problem is in my merge replication. I have'nt Foreign Keys in then BD,
when the replication run, the insert or update or delete statment cause error
because dont have a order of insertion or update or delete because in our
application we have a order.
think you.
Please help.
"Paul Ibison" wrote:

> What problems are you seeing from the triggers? If you
> don't want them to fire as a result of the replication
> process, you can specify NOT FOR REPLICATION on the
> trigger definition. If the problem is that the triggers
> are causing recursion, you can investigate
> sp_check_for_sync_trigger.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Chouaib,
using NOT FOR REPLICATION in the trigger definition will
mean that the triggers which you have to check
Referential Integrity will not fire during the
replication process. This would seem to fix your issue?
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thank you paul,
i try that but i have more that one thousands of triggers.
regards.
"Paul Ibison" wrote:

> Chouaib,
> using NOT FOR REPLICATION in the trigger definition will
> mean that the triggers which you have to check
> Referential Integrity will not fire during the
> replication process. This would seem to fix your issue?
> Rgds,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you script them out into a single file using EM, you
might be asble to replace 'AS' with 'NOT FOR REPLICATION
AS' then run the script.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU PAUL'
I DO THAT.
"Paul Ibison" wrote:

> If you script them out into a single file using EM, you
> might be asble to replace 'AS' with 'NOT FOR REPLICATION
> AS' then run the script.
> Rgds,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

CONSTRAINTS are gone at subscriber.

If all the articles are in the same publication, you can
gfet the replication setup to create the FK constraints
automatically. If not, you can use a post-snapshot
script. This'll have to be created manually and referred
to on the snapshot tab of the merge publication. If you
have already initialized, you can apply the script using
sp_addscriptexec.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
First I want to replicate all the lookup tables in a snapshot replication.
This will be done once (hopefully)
Second I want to replicate a merge publication of the three transaction
tables. This will be replicated continually.
I assume the post replication script on the merge publication will include
all the ALTER TABLE stmts, correct?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:421801c523c2$527b7f60$a501280a@.phx.gbl...
> If all the articles are in the same publication, you can
> gfet the replication setup to create the FK constraints
> automatically. If not, you can use a post-snapshot
> script. This'll have to be created manually and referred
> to on the snapshot tab of the merge publication. If you
> have already initialized, you can apply the script using
> sp_addscriptexec.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Exactly!
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag