Merge replication UNIQUEIDENTIFIER Column?

Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
inkquote:
If a published table does not have a uniqueidentifier column with the
ROWGUIDCOL property and a unique index, replication adds one
So, check for the rowguidcol property...
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> I am using SQL 2005 sp1 to setup Merge replication for PDA access.
> i created my Primary Key column a GUID using NEWID() as the default value.
> When the snapshot was created it still went and added anothe GUID to all
> my tables called rowguid.
> i thought it was suposed to use the existing Unique GUID column before
> creating a new one?
> thanks for you advice. i hope i have the corect groups here?
> ink
>|||Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>|||Yes, you need RowGuid property. Point is, you can have any number of
uniqueidentifiers in a table, but one of them needs to have this property
set. Since SQL Server doesnt want to guess which one would you like to have
as the 'main' GUID in a table, it adds another with rowguid property set.
Offcourse, if you allready have one it doesnt need to add it.
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:e2jo3%23bWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Sorry my point was that i had created one and SQL2005 still created its
> own.
> Now from what i can tell i think i have figgerd it out.
> The behaviour i was expecting was how SQL 2000 handled the row GUID column
> for snapshots.
> i am using SQL 2005.
> it seems that there is in fact a property of the column called RowGuid
> that must be set to Yes before creating the first snapshot.
> Then SQL2005 will use that column instead of creating it's own. Just
> setting the data type and making it the primary key is not enough.
> Thanks for your input Marko.
>
>
>
> "MC" <marko.culoNOSPAM@.gmail.com> wrote in message
> news:eruu6p$pcf$1@.ss408.t-com.hr...
>

Merge replication UNIQUEIDENTIFIER Column?

Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
ink
Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>

merge replication script problem

Hi all:
i set up merge replication with row and column filter in sql 2000 and
it works fine, so i generate the script and try to run this script in
another computer, i alway got errors like "table name does not exist etc",
those tables definately exist in DB.
i worked it out after i delete all column fiters in script, does any
one know what's the problem?
Cheers
nick
could you post your table schema and your publication script?
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
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:euoHn5JCFHA.936@.TK2MSFTNGP12.phx.gbl...
> Hi all:
> i set up merge replication with row and column filter in sql 2000
and
> it works fine, so i generate the script and try to run this script in
> another computer, i alway got errors like "table name does not exist etc",
> those tables definately exist in DB.
> i worked it out after i delete all column fiters in script, does any
> one know what's the problem?
>
> Cheers
> nick
>

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

Merge replication- How to drop article and than add it again

Hi All,
I need to drop an article from live merge publication and than add it back
again with different schema (column, PK etc’) . I can’t use
sp_dropsubscription for every subscriber and sp_droparticle because it is not
transactional / snapshot publication.
Do I have any other alternative other than reinitialize the whole
publication? .
Thanks,
Eyal
Unfortunately not in the case of merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There is more info here
http://msdn.microsoft.com/library/de...limpl_22pf.asp
about managing schema changes with merge replication.
Thanks
Nabila Lacey
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:05D80E20-9EC0-4A00-BF56-210C868F3617@.microsoft.com...
> Hi All,
> I need to drop an article from live merge publication and than add it
back
> again with different schema (column, PK etc') . I can't use
> sp_dropsubscription for every subscriber and sp_droparticle because it is
not
> transactional / snapshot publication.
> Do I have any other alternative other than reinitialize the whole
> publication? .
>
> Thanks,
> Eyal

Merge replication error - invalid column name 'rowguidcol'

Hi,

I'm having dramas with a merge replication between two servers. They have been replicating information between three databases happily for about 18 months. Recently I had to do an upgrade on the databases, so I deleted the subscriptions and the snapshots, applied the upgrade script and recreated the snapshots and subscriptions.

Now when they go to replicate, the merge agent for each subscription gives the error "invalid column name 'rowguidcol'".

From some investigation, I think this has something to do with the fact that as they used to replicate, the subscriber has the schema information from the old snapshot, and doesn't recognise the new one. I can't reinitialise the schema from the publisher as not all the data in the databases is replicated, so I can't have the publisher overwrite the subscriber.

What can I do in this situation?

Regards,
James

Can you explain what "upgrade on the databases" is? What columns/rows were updated? And how did you recreate the publication, snapshot and subscriptions, via scripts or UI? Did you reinitialize the subscription, regenerate the snapshot, dynamic snapshot, and synchronize?

|||Hi,

Sorry. The databases are part of a SQL-based application called Amlib. It's a library database. I work at a school and we use Amlib for our library cataloging. The application was running at verion 4.2 and the "upgrade" was to 5.1. The three main databases each had extra tables added to them as part of the upgrade. I can give you a run-down of what actually changed if you think it will help.

I recreated the publication, snapshot and subscriptions via Enterprise Manager. Same way in which I created them the first time. I had to delete the snapshots and subscriptions to perform the update on the databases.

I didn't reinitialise the subscription, as I understood that would regenerate the schema and push the data out to the subscriber. I can't go down that path as there is data which is unique to the databases on each server and therefore is not replicated. Also, there's quite a lot of data (4GB) and the WAN link between the two servers wouldn't cope with the transfer.

I'm afraid I don't know what dynamic snapshot is.

Regards,
James
|||

Did the upgrade touch the existing tables that were part of replication? I would assume so since you had to remove replication. do you know if this upgrade removed, dropped or added any objects, columns, stored procedures etc. that might interfere with replication? How about any rows?

Is this SQL 2000 or SQL 2005? Did you use the wizards to set up a no-sync subscription? If this is SQL 2005, you can follow the steps in topic "Initializing a Merge Subscription Without a Snapshot".

|||Actually I don't know. I don't believe that any data was changed - I just know that extra tables were added to the databases. I think there were some extra rows added to existing tables, yes.

Although to be honest, I thought I had to break the replication regardless, because you can't modify an existing snapshot without re-creating the snapshot? That's what I believed to be true anyway.

As you can probably tell, I'm not much of a SQL guru..... :-)

It's running on SQL 2000 unfortunately, not 2005. And yes, I set up the snapshots and subscriptions using the wizard in Enterprise Manager
|||Since you added new data to existing tables that are published, I assume you want those changes at the subscriber as well. What I'd do is just recreate the publication, subscriptions, regenerate the snapshot and apply the new snapshot to the subscriber. Basically, start from scratch.|||
I did start from scratch! The the snapshot, publications and subscriptions are all brand new. The problem is that there is existing data at the other end which can't be overwritten, so I can't simply re-initialise the schema/data. I just need to find a way of getting the databases replicating again....
|||

hey ,

simply check if all ur rowguid columns (in published tables) have their default set to newid() , set them up if not and hopefully ur problem will be solved..

|||

i had hear someone got such problem as well and

finally solved the problem.

pls try to build a "remote scripting" tool into your software.

This allowed you to manually recreate the new tables at the subscribers.

hope that will help you to solve your problem.

that Dynamic snapshot is not reliable when it comes
to table changes or additions...

|||

If you do not wish to lose your subscriber's data, I would say backup your tables/database. Re-setup the subscription and let the data get overwritten. Then do a diff between the current data and the back and do an insert/update/delete of the relevant rows and they should then propagate to the publisher. Agreed that this may not be an elegant solution, but it will achieve what you want.

Another thing I can think of is that the subscriber propbably does not have the rowguid column. You are doing a no-sync subscription (which means the subscriber already has schema and data). If that is the case, you would need to create this new rowguid column at the subscriber and populate the values to be equal to those on the publisher for each row for each table and then do a synchronize. Hopefully that maybe another option for you.

But both of them are not pretty.

Merge replication error - invalid column name 'rowguidcol'

Hi,

I'm having dramas with a merge replication between two servers. They have been replicating information between three databases happily for about 18 months. Recently I had to do an upgrade on the databases, so I deleted the subscriptions and the snapshots, applied the upgrade script and recreated the snapshots and subscriptions.

Now when they go to replicate, the merge agent for each subscription gives the error "invalid column name 'rowguidcol'".

From some investigation, I think this has something to do with the fact that as they used to replicate, the subscriber has the schema information from the old snapshot, and doesn't recognise the new one. I can't reinitialise the schema from the publisher as not all the data in the databases is replicated, so I can't have the publisher overwrite the subscriber.

What can I do in this situation?

Regards,
James

Can you explain what "upgrade on the databases" is? What columns/rows were updated? And how did you recreate the publication, snapshot and subscriptions, via scripts or UI? Did you reinitialize the subscription, regenerate the snapshot, dynamic snapshot, and synchronize?

|||Hi,

Sorry. The databases are part of a SQL-based application called Amlib. It's a library database. I work at a school and we use Amlib for our library cataloging. The application was running at verion 4.2 and the "upgrade" was to 5.1. The three main databases each had extra tables added to them as part of the upgrade. I can give you a run-down of what actually changed if you think it will help.

I recreated the publication, snapshot and subscriptions via Enterprise Manager. Same way in which I created them the first time. I had to delete the snapshots and subscriptions to perform the update on the databases.

I didn't reinitialise the subscription, as I understood that would regenerate the schema and push the data out to the subscriber. I can't go down that path as there is data which is unique to the databases on each server and therefore is not replicated. Also, there's quite a lot of data (4GB) and the WAN link between the two servers wouldn't cope with the transfer.

I'm afraid I don't know what dynamic snapshot is.

Regards,
James
|||

Did the upgrade touch the existing tables that were part of replication? I would assume so since you had to remove replication. do you know if this upgrade removed, dropped or added any objects, columns, stored procedures etc. that might interfere with replication? How about any rows?

Is this SQL 2000 or SQL 2005? Did you use the wizards to set up a no-sync subscription? If this is SQL 2005, you can follow the steps in topic "Initializing a Merge Subscription Without a Snapshot".

|||Actually I don't know. I don't believe that any data was changed - I just know that extra tables were added to the databases. I think there were some extra rows added to existing tables, yes.

Although to be honest, I thought I had to break the replication regardless, because you can't modify an existing snapshot without re-creating the snapshot? That's what I believed to be true anyway.

As you can probably tell, I'm not much of a SQL guru..... :-)

It's running on SQL 2000 unfortunately, not 2005. And yes, I set up the snapshots and subscriptions using the wizard in Enterprise Manager
|||Since you added new data to existing tables that are published, I assume you want those changes at the subscriber as well. What I'd do is just recreate the publication, subscriptions, regenerate the snapshot and apply the new snapshot to the subscriber. Basically, start from scratch.|||
I did start from scratch! The the snapshot, publications and subscriptions are all brand new. The problem is that there is existing data at the other end which can't be overwritten, so I can't simply re-initialise the schema/data. I just need to find a way of getting the databases replicating again....
|||

hey ,

simply check if all ur rowguid columns (in published tables) have their default set to newid() , set them up if not and hopefully ur problem will be solved..

|||

i had hear someone got such problem as well and

finally solved the problem.

pls try to build a "remote scripting" tool into your software.

This allowed you to manually recreate the new tables at the subscribers.

hope that will help you to solve your problem.

that Dynamic snapshot is not reliable when it comes
to table changes or additions...

|||

If you do not wish to lose your subscriber's data, I would say backup your tables/database. Re-setup the subscription and let the data get overwritten. Then do a diff between the current data and the back and do an insert/update/delete of the relevant rows and they should then propagate to the publisher. Agreed that this may not be an elegant solution, but it will achieve what you want.

Another thing I can think of is that the subscriber propbably does not have the rowguid column. You are doing a no-sync subscription (which means the subscriber already has schema and data). If that is the case, you would need to create this new rowguid column at the subscriber and populate the values to be equal to those on the publisher for each row for each table and then do a synchronize. Hopefully that maybe another option for you.

But both of them are not pretty.

Merge replication error - invalid column name 'rowguidcol'

Hi,

I'm having dramas with a merge replication between two servers. They have been replicating information between three databases happily for about 18 months. Recently I had to do an upgrade on the databases, so I deleted the subscriptions and the snapshots, applied the upgrade script and recreated the snapshots and subscriptions.

Now when they go to replicate, the merge agent for each subscription gives the error "invalid column name 'rowguidcol'".

From some investigation, I think this has something to do with the fact that as they used to replicate, the subscriber has the schema information from the old snapshot, and doesn't recognise the new one. I can't reinitialise the schema from the publisher as not all the data in the databases is replicated, so I can't have the publisher overwrite the subscriber.

What can I do in this situation?

Regards,
James

Can you explain what "upgrade on the databases" is? What columns/rows were updated? And how did you recreate the publication, snapshot and subscriptions, via scripts or UI? Did you reinitialize the subscription, regenerate the snapshot, dynamic snapshot, and synchronize?

|||Hi,

Sorry. The databases are part of a SQL-based application called Amlib. It's a library database. I work at a school and we use Amlib for our library cataloging. The application was running at verion 4.2 and the "upgrade" was to 5.1. The three main databases each had extra tables added to them as part of the upgrade. I can give you a run-down of what actually changed if you think it will help.

I recreated the publication, snapshot and subscriptions via Enterprise Manager. Same way in which I created them the first time. I had to delete the snapshots and subscriptions to perform the update on the databases.

I didn't reinitialise the subscription, as I understood that would regenerate the schema and push the data out to the subscriber. I can't go down that path as there is data which is unique to the databases on each server and therefore is not replicated. Also, there's quite a lot of data (4GB) and the WAN link between the two servers wouldn't cope with the transfer.

I'm afraid I don't know what dynamic snapshot is.

Regards,
James
|||

Did the upgrade touch the existing tables that were part of replication? I would assume so since you had to remove replication. do you know if this upgrade removed, dropped or added any objects, columns, stored procedures etc. that might interfere with replication? How about any rows?

Is this SQL 2000 or SQL 2005? Did you use the wizards to set up a no-sync subscription? If this is SQL 2005, you can follow the steps in topic "Initializing a Merge Subscription Without a Snapshot".

|||Actually I don't know. I don't believe that any data was changed - I just know that extra tables were added to the databases. I think there were some extra rows added to existing tables, yes.

Although to be honest, I thought I had to break the replication regardless, because you can't modify an existing snapshot without re-creating the snapshot? That's what I believed to be true anyway.

As you can probably tell, I'm not much of a SQL guru..... :-)

It's running on SQL 2000 unfortunately, not 2005. And yes, I set up the snapshots and subscriptions using the wizard in Enterprise Manager
|||Since you added new data to existing tables that are published, I assume you want those changes at the subscriber as well. What I'd do is just recreate the publication, subscriptions, regenerate the snapshot and apply the new snapshot to the subscriber. Basically, start from scratch.|||
I did start from scratch! The the snapshot, publications and subscriptions are all brand new. The problem is that there is existing data at the other end which can't be overwritten, so I can't simply re-initialise the schema/data. I just need to find a way of getting the databases replicating again....
|||

hey ,

simply check if all ur rowguid columns (in published tables) have their default set to newid() , set them up if not and hopefully ur problem will be solved..

|||

i had hear someone got such problem as well and

finally solved the problem.

pls try to build a "remote scripting" tool into your software.

This allowed you to manually recreate the new tables at the subscribers.

hope that will help you to solve your problem.

that Dynamic snapshot is not reliable when it comes
to table changes or additions...

|||

If you do not wish to lose your subscriber's data, I would say backup your tables/database. Re-setup the subscription and let the data get overwritten. Then do a diff between the current data and the back and do an insert/update/delete of the relevant rows and they should then propagate to the publisher. Agreed that this may not be an elegant solution, but it will achieve what you want.

Another thing I can think of is that the subscriber propbably does not have the rowguid column. You are doing a no-sync subscription (which means the subscriber already has schema and data). If that is the case, you would need to create this new rowguid column at the subscriber and populate the values to be equal to those on the publisher for each row for each table and then do a synchronize. Hopefully that maybe another option for you.

But both of them are not pretty.

Merge Replication Conflict Problem

exec sp_helpmergeconflictrows @.conflict_table = 'CMCustomer'

Msg 207, Level 16, State 1, Line 1

Invalid column name 'origin_datasource_id'.

When I run the "Microsoft Replication Conflict View" I am prompted to pick a table. Here are the details of the error message I receive.

===================================

CMCustomer is neither a DataColumn nor a DataRelation for table summary. (System.Data)

Program Location:

at System.Data.DataRowView.get_Item(String property)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetWinnerSQL(DataRow loserRow, Boolean blockFetch)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetSourceRow()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetailData()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetail()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.summaryInfoGrid_SelectionChanged(Object sender, SelectionChangedEventArgs args)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.OnSelectionChanged(BlockOfCellsCollection selectedCells)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.SelectedCellsInternal(BlockOfCellsCollection col, Boolean bSet)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.set_SelectedCells(BlockOfCellsCollection value)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.ResetSummaryGrid()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.LoadConflict()

When I try to "Remove" then conflict (Conflict type - "4(Update/Delete, Update wins)") I receive the following...

===================================

Column 'CMCustomer' does not belong to table summary. (System.Data)

Program Location:

at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.get_Item(String columnName)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.SetLogInfo(DataRow logRow, String sql)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.btnClearUpper_Click(Object sender, EventArgs eg)

Grant,

Please let me know if my assumptions are incorrect:

I believe CMCustomer is a rowguid column.

This column was added by the user and snapshot did not add this.

This happens on delete conflicts.

If these are correct, then it is a known issue and should be fixed in SP2.

|||

Yes, CMCustomer is the rowguidcol of the CMCustomer table.

"This column was added by the user and snapshot did not add this." Are you saying the column metadata changed at some point?

|||No, what I am saying is when you created the table with this column and it was of type uniqueidentifier and it was designated as the rowguidcol. In case such a column does not exist in the table, snapshot agent will add one and name it as a rowguid. The bug happens because the name is not rowguid and UI was trying to find a column with this name.|||Ok. Thanks.

Merge Replication and publisher deleting subscriber inserts.

The row was inserted at 'Subscriber Laptop' but could not be inserted at
'Publisher server'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK1'. The conflict occurred in database 'Tst', table 'Report',
column 'ReportNo'.
http://support.microsoft.com/default...b;en-us;307356
We are using Merge Replication. Sql Server 2000 SP4 on the server and MSDE
SP4 on the laptops.
We've been testing for a few months and just starting to trickle into
production. This is the first time we've seen this error. From what I could
google, the KB I refer to above says we should have NOT FOR REPLICATION on
all FK constraints.
Now, all of my hits on this subject seem to be 2003 and earlier. So before I
go ahead and make this huge change, I want to confirm that yes, infact, I
should be doing this.
Should NFR be put on Primary Keys as well?
Can this NFR be specified through VISIO as that is where we maintain the
data model. If not, is there an alter statement we can apply to constraints
to turn this option on?
Buzz.
NFR can't be put onto PKs but definitely you should have it on the FKs (see
http://www.replicationanswers.com/Me...derArticle.asp)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and Filtering by date

With SQL Server 2005 adding a merge filter that used a column with a date did not work very well. I have a table that has an expiration date. Ideally, I would want to set a filter that says send to the subscriber if getdate() < expirationdate. The problem in 2005 is that since the data in the row does not change, the row will not be deleted from the subscription when the date passes the expiration date. Microsoft has recommended that another column called something like IsActive should be added and updated every day. This is sometimes not possible. Has this issue been addressed in SQL Server 2008?

Merge replication is trigger-based, you have to explicitly make a change for merge replication to track it, this has always been the design, we use the metadata tables to determine what changes need to be uploaded/downloaded. The recommendation for an "IsExpired" column is a common and easy workaround, you can create a job that runs daily/weekly/hourly to update this column based on the expiration date.

Can you explain in more detail why this workaround is not possible for you? Maybe we can offer another solution/workaround.

|||

I don't really have a particular problem in mind. I was just wondering if SQL 2008 has changed in such a way that the workaround is not needed any longer. If it isn't, then I can just continue to do what I have been, but if it has changed I just wanted to be aware of it. Thanks for your help.

Merge Replication Alther Column in Place

Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew
You first have to drop default contraint which is causing the problem
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"andrew bourne" <andrewbourne@.vardells.com> wrote in message news:eZuBH8UVFHA.3584@.TK2MSFTNGP14.phx.gbl...
Hi all i have a merge replication am im trying to alter a column in place,
ive got an article from sql server cental, that describes creating a temp
column copy the data over drop and recrete the column, copy the data over
and drop the temp column,
Step 1
exec sp_repladdcolumn @.source_object = 'authors'
,@.column = 'TempTest'
,@.typetext = 'varchar(100) NULL'
,@.publication_to_add = 'pubs'
This works ok
Step 2
update authors set TempTest = Test
This works ok
Step 3
exec sp_repldropcolumn @.source_object = 'authors'
,@.column = 'Test'
But this gives an error
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'pubs' because schema replication is performed.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__authors__test__186C9245' is dependent on column 'Test'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN Test failed because one or more objects access this
column.
Can anyone help please, how can i drop this object, to let me drop the
column.
Thanks Andrew