New to merge replication - simple scenario......

I have a product that sits on a main server and wish to implement
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field). Now of course my "Inserts" no longer work, as
they expect a GUID. I would have expected SQL Server to automatically
generate a guid for new inserts (in a similar way to it's TIMESTAMP), but it
appears it doesn't, despite the fact I have "(newid())" as the default for
the column. It always inserts the same value:
{00000000-0000-0000-0000-000000000000}.

So, back to basics, now I have a guid field for each record, how do I manage
inserts?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7n9tc$1uu$1$8302bc10@.news.demon.co.uk...
> I have a product that sits on a main server and wish to implement
> functionality to allow salesmen to come along, pick up a snapshot of the
> database, go away and maybe modify/add to it and then come back and
> "synchronise" their data. I'm reading up on Merge Replication for this
> purpose. But anyway, I created a publisher on my server and it went away
> and generated a "rowguid" column on all of my tables (my tables all have
> an Identity column key field). Now of course my "Inserts" no longer work,
> as they expect a GUID. I would have expected SQL Server to automatically
> generate a guid for new inserts (in a similar way to it's TIMESTAMP), but
> it appears it doesn't, despite the fact I have "(newid())" as the default
> for the column. It always inserts the same value:
> {00000000-0000-0000-0000-000000000000}.
> So, back to basics, now I have a guid field for each record, how do I
> manage inserts?
> Thanks.

Good practice is to explicitly name the target columns in all your INSERTs,
eg:

insert into dbo.MyTable (col1, col2)
select col1, col2
from dbo.MyOtherTable

If you do this, then there's no problem - your INSERT works as usual, and
you can ignore the uniqueidentifier column completely. Since there's a
default on it, there's no need to include it in the INSERT.

Simon|||So am I right in thinking that the unique GUID will be created on the field
when data is pushed or pulled - as I say, the newid() seems to produce a
zero'd GUID. I was thinking I am responsible for maintaining the uniqueness
of this ID, but this can't be right.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:429f41fa$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:d7n9tc$1uu$1$8302bc10@.news.demon.co.uk...
>>
>> I have a product that sits on a main server and wish to implement
>> functionality to allow salesmen to come along, pick up a snapshot of the
>> database, go away and maybe modify/add to it and then come back and
>> "synchronise" their data. I'm reading up on Merge Replication for this
>> purpose. But anyway, I created a publisher on my server and it went
>> away and generated a "rowguid" column on all of my tables (my tables all
>> have an Identity column key field). Now of course my "Inserts" no longer
>> work, as they expect a GUID. I would have expected SQL Server to
>> automatically generate a guid for new inserts (in a similar way to it's
>> TIMESTAMP), but it appears it doesn't, despite the fact I have
>> "(newid())" as the default for the column. It always inserts the same
>> value: {00000000-0000-0000-0000-000000000000}.
>>
>> So, back to basics, now I have a guid field for each record, how do I
>> manage inserts?
>>
>> Thanks.
>>
>>
>>
> Good practice is to explicitly name the target columns in all your
> INSERTs, eg:
> insert into dbo.MyTable (col1, col2)
> select col1, col2
> from dbo.MyOtherTable
> If you do this, then there's no problem - your INSERT works as usual, and
> you can ignore the uniqueidentifier column completely. Since there's a
> default on it, there's no need to include it in the INSERT.
> Simon|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7nge1$1p7$1$8300dec7@.news.demon.co.uk...
> So am I right in thinking that the unique GUID will be created on the
> field when data is pushed or pulled - as I say, the newid() seems to
> produce a zero'd GUID. I was thinking I am responsible for maintaining
> the uniqueness of this ID, but this can't be right.

<snip
As far as I'm aware, in the case of uniqueidentifier columns added to
support merge replication, it's the replication process that maintains these
values. But I'm not at all an expert in replication, so you might want to
consider posting to microsoft.public.sqlserver.replication if you need more
details.

Simon

New table on server not replicating to device

Hi,

I have a SQL 2005 Mobile db merge synching to SQL Server 2005. I've been using this for a few months now and generally it works well (except when adding not null columns with default values, but that's another story...). I just added a new table to the server, went into the publication articles and checked the new table. It told me I would need to generate a new snapshot for the table, so I then generated a new snapshot. Then I synched the mobile db and it worked with no errors, but the new table I added on the server is not on the mobile db (I checked using the Query Analyzer). I have also tried reinitializing subscriptions, but this didn't work either. Any ideas why this table would not be synched to my mobile db (and no error message on mobile device)?

Regards,

Greg

Greg

If you create a new new subscription to the publication (eg a new device, or you could just create a test sdf file and use Sql management studio to sync it) does the table get sync'd? Have you checked the snapshot agent status to confirm that a new snapshot has been generated?

|||

Nick,

Sorry for the late reply, alerts not working for some reason. Yes, a new subscription picks up the new table. I confirmed that the snapshot agent did create a new snapshot. I've actually had more problems since that post, it seems that if I make any changes to the db schema I get replication errors and/or apparently successful replication (replication monitor shows success) without the schema changes. The only fix seems to be to delete the db on the subscriber and resync (basically a new subscription). After that it will sync fine, until I make any change to the schema on the server. Is it possible that there is some corruption occurring in the publisher merge tables? Do you know of any rebuilds/reindexes I can do (other than deleting the publication and setting up again)?

Regards,

Greg

sql

New table on server not replicating to device

Hi,

I have a SQL 2005 Mobile db merge synching to SQL Server 2005. I've been using this for a few months now and generally it works well (except when adding not null columns with default values, but that's another story...). I just added a new table to the server, went into the publication articles and checked the new table. It told me I would need to generate a new snapshot for the table, so I then generated a new snapshot. Then I synched the mobile db and it worked with no errors, but the new table I added on the server is not on the mobile db (I checked using the Query Analyzer). I have also tried reinitializing subscriptions, but this didn't work either. Any ideas why this table would not be synched to my mobile db (and no error message on mobile device)?

Regards,

Greg

Greg

If you create a new new subscription to the publication (eg a new device, or you could just create a test sdf file and use Sql management studio to sync it) does the table get sync'd? Have you checked the snapshot agent status to confirm that a new snapshot has been generated?

|||

Nick,

Sorry for the late reply, alerts not working for some reason. Yes, a new subscription picks up the new table. I confirmed that the snapshot agent did create a new snapshot. I've actually had more problems since that post, it seems that if I make any changes to the db schema I get replication errors and/or apparently successful replication (replication monitor shows success) without the schema changes. The only fix seems to be to delete the db on the subscriber and resync (basically a new subscription). After that it will sync fine, until I make any change to the schema on the server. Is it possible that there is some corruption occurring in the publisher merge tables? Do you know of any rebuilds/reindexes I can do (other than deleting the publication and setting up again)?

Regards,

Greg

New records were not replicated on publisher and subscriber

I have seen this happen in 2 circumstances:
(1) Firstly when the filter was set to 1=2 and inserts
were made while the merge agent was running
(2) If you bulk insert the rows and choose the defaults,
then FIRE_TRIGGERS is false and consequently the rows are
not added to MSmerge_contents.
In either case, you need to run sp_addtabletocontents to
include the rows then resynchronise. Alternatively you
can use sp_mergedummyupdate for a single row.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
Thanks for the answer, I would give it a try.
Hope it will solve my problem.
Thanks again, and more power.
Roland
"Paul Ibison" wrote:

> I have seen this happen in 2 circumstances:
> (1) Firstly when the filter was set to 1=2 and inserts
> were made while the merge agent was running
> (2) If you bulk insert the rows and choose the defaults,
> then FIRE_TRIGGERS is false and consequently the rows are
> not added to MSmerge_contents.
> In either case, you need to run sp_addtabletocontents to
> include the rows then resynchronise. Alternatively you
> can use sp_mergedummyupdate for a single row.
> rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

New kind of merge repl error floating point exception?

Got a weird one. Hope someone has seen it before. Need some smarts.
Rolled out 150 new laptops, all imaged the same for users with a local msde
sp3a. All installed with subscriptionDB.
Previously, all were synching well on the same Sql 2000 pub/distr, on the
same publications for 18 months.
50% now work just fine, the other half get the message below. I tried
reinits, reinstall and sp4 upgrade (per several bug doc, found 3 close).
Varied results, some success but not consistant. Temporary success with
failures occationally but never clean.
Dies on sp_MSenumchanges....
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
A floating point exception occurred in the user process. Current transaction
is canceled.
(Source: Servername_removed (Data source); Error number: 3628)
------
The process was successfully stopped.
Any experiences with this, ideas of causes are appreciated.
Mike
If data falls in the woods and nobody is there to see it ...... ?
You could try running service pack 3a again on the subscribers although
it doesn't make logical sense why anything should be different. I know
of a similiar error if the clients are use Windows 95/98/ME. Try
running dbcc checkdb just in case there is some corruption in any of
the tables.
|||Thanks for the suggestions. Good ideas.
I tried reinstalling sp3 and a new subscriptionDB too.
Dumped the subscription and recreated it with the wizard too.
dbcc checkdb ran clean but I haven't caught one with a failure in action
yet. Will keep at it.
The randomness makes me wonder if the subscription proc timesout somehow
before the server can return info. The subs history shows it processes
uploads to some random article and then fails.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"seanbell68@.gmail.com" wrote:

> You could try running service pack 3a again on the subscribers although
> it doesn't make logical sense why anything should be different. I know
> of a similiar error if the clients are use Windows 95/98/ME. Try
> running dbcc checkdb just in case there is some corruption in any of
> the tables.
>
|||any commonalities on the machines that do not work? also take in
consideration isp/routers/vpn/encryption etc?
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:0900566A-3A1B-44F4-8118-4427B4FBAD3B@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. Good ideas.
> I tried reinstalling sp3 and a new subscriptionDB too.
> Dumped the subscription and recreated it with the wizard too.
> dbcc checkdb ran clean but I haven't caught one with a failure in action
> yet. Will keep at it.
> The randomness makes me wonder if the subscription proc timesout somehow
> before the server can return info. The subs history shows it processes
> uploads to some random article and then fails.
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "seanbell68@.gmail.com" wrote:
|||Unfortunately no commonalities any found yet. All laptops in the same
shipment, imaged with the same WinXP. All received a new vpn client but half
are synching fine. Soho offices mostly, isp's vary.
I will poll the challenged as to ISP/Wireless/Routers etc to see if
something jumps out.
We are leaning towards a connection problem but can't figure how they all
get the same error and doesn't go into the retry mode via sql agent.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"S c o t t K r a m e r" wrote:

> any commonalities on the machines that do not work? also take in
> consideration isp/routers/vpn/encryption etc?
>
> "Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
> news:0900566A-3A1B-44F4-8118-4427B4FBAD3B@.microsoft.com...
>
>
|||Turned out to be a problem with the newer cisco vpn client 4.6. Runs
errorless with earier client. Checking networking but no longer a repl issue.
Thanks for the input anywho.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Tigermikefl" wrote:
[vbcol=seagreen]
> Unfortunately no commonalities any found yet. All laptops in the same
> shipment, imaged with the same WinXP. All received a new vpn client but half
> are synching fine. Soho offices mostly, isp's vary.
> I will poll the challenged as to ISP/Wireless/Routers etc to see if
> something jumps out.
> We are leaning towards a connection problem but can't figure how they all
> get the same error and doesn't go into the retry mode via sql agent.
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "S c o t t K r a m e r" wrote:

New icon appears after restarting Distribution/Merge agents - mean

Hello,
All of a sudden, Sql Server 2000 Replication has started failing for
Transaction and Merge Replication on our system. I have been restarting the
Distribution and Merge Agents and after Refresh, the Red X goes away (for a
little while). Something obviously is out of synch. But on the last restart
of the agents (which is actually the first time I restarted the Merge agent -
as previously only the Distribution Agent had the red x), now there is this
blue twisting Arrow inside of a small circle icon attached to the Replication
Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
click on the distribution icon, in the Right pane of Enterprise Manager, the
<Multiple Publications> icon also has the little circle with the blue
twisting arrow icon attached to it.
Is there any significance to this icon? What does it mean? It is not red
and is not an X, so it does not look that scary, but I am sure it means
something I need to worry about. Any suggestions greatly appreciated.
Thanks,
Rich
I just did a Refesh on the Replication Monitor and the strange new icon has
disappeard, and there is no Red X icon (what a relief). But I am still
curious what is the meaning of this icon of a little circle with the twisting
blue arrow attached to the Replication Monitor Icon and the others. This
strange icon was attached to the lower right corner of the main icon.
"Rich" wrote:

> Hello,
> All of a sudden, Sql Server 2000 Replication has started failing for
> Transaction and Merge Replication on our system. I have been restarting the
> Distribution and Merge Agents and after Refresh, the Red X goes away (for a
> little while). Something obviously is out of synch. But on the last restart
> of the agents (which is actually the first time I restarted the Merge agent -
> as previously only the Distribution Agent had the red x), now there is this
> blue twisting Arrow inside of a small circle icon attached to the Replication
> Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
> click on the distribution icon, in the Right pane of Enterprise Manager, the
> <Multiple Publications> icon also has the little circle with the blue
> twisting arrow icon attached to it.
> Is there any significance to this icon? What does it mean? It is not red
> and is not an X, so it does not look that scary, but I am sure it means
> something I need to worry about. Any suggestions greatly appreciated.
> Thanks,
> Rich
>
|||That blue arrow means retrying.
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...[vbcol=seagreen]
>I just did a Refesh on the Replication Monitor and the strange new icon has
> disappeard, and there is no Red X icon (what a relief). But I am still
> curious what is the meaning of this icon of a little circle with the
> twisting
> blue arrow attached to the Replication Monitor Icon and the others. This
> strange icon was attached to the lower right corner of the main icon.
>
> "Rich" wrote:
|||Thank you.
"Hilary Cotter" wrote:

> That blue arrow means retrying.
> --
> 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
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...
>
>