Can't re-submit a deleted conflicted row

Hello,
I have a merge replication which use identity range management (for some
tables).
I have a table that inserted a row on the publisher and tried to transfer it
to subscriber. The subscriber got an error when trying to insert it (by the
way, I use MSDE on subscriber and MSmerge_contents and MSmerge_tombstone are
huge and the DB limit of 2 GB arrive fast - it was nice that the system
tables are not included in the 2 GB limit... I decided to move that 2 tables
in another DB and it works nice). As I said, the subscriber gave an error
(different reasons) and the conflict resolver decided to delete the row from
publisher, because it can't insert in on subscriber !?! Anyway seems to be ok
because the row i have in conflict viewer, that seems that I can re-insert
it. But when I try to re-insert it, it gave me an error because the identity
ranges changed and the old value (of deleted row) is not proper for the new
constraints of identity... I supposed that SQL will deactivate the
constraint, as it does during replication...
Any ideea how to re-insert it? Or I have to "manually" (programatically)
de-activate it, insert it from conflict table and re-activate it?
Thanks for any suugestion.
Catalin
Hello Catalin,
I have something for you. I've tried reaching you through phone or email,
without success. Email me at chris lafrance at h_tm__l dot com.
Cheers
"Catalin NASTAC" wrote:

> Hello,
> I have a merge replication which use identity range management (for some
> tables).
> I have a table that inserted a row on the publisher and tried to transfer it
> to subscriber. The subscriber got an error when trying to insert it (by the
> way, I use MSDE on subscriber and MSmerge_contents and MSmerge_tombstone are
> huge and the DB limit of 2 GB arrive fast - it was nice that the system
> tables are not included in the 2 GB limit... I decided to move that 2 tables
> in another DB and it works nice). As I said, the subscriber gave an error
> (different reasons) and the conflict resolver decided to delete the row from
> publisher, because it can't insert in on subscriber !?! Anyway seems to be ok
> because the row i have in conflict viewer, that seems that I can re-insert
> it. But when I try to re-insert it, it gave me an error because the identity
> ranges changed and the old value (of deleted row) is not proper for the new
> constraints of identity... I supposed that SQL will deactivate the
> constraint, as it does during replication...
> Any ideea how to re-insert it? Or I have to "manually" (programatically)
> de-activate it, insert it from conflict table and re-activate it?
> Thanks for any suugestion.
> Catalin

Cant remove subscription info

Dear All,

I deleted a replication in the PROD server. However, the subscription info still exists in the subscription folder in the DR server.

Can anyone tells me how to remove the subscription info? (When I delete the publication, there's a message saying we need to manually delete the subscription info in the subscriber)

I'd tried restart the SQLAgent service, Disabling Publishing in PROD server but it's useless.

thanks first.

rgds.The subscriber may be not online when u dropped the publication.
Run sp_dropsubscriber at the publisher, see syntax in BOL. If it won't work; manually delete the subscription from the subscriber using Enterprise-Manager.|||Thanks TALAT.

However, the sp_dropsubscriber won't work, i keep hiting error 14048.

Anyway, my real interest is how to 'manually delete the subscription in EM' as mentioned by you...

Details:
At PROD server, publication is drop, there's NO item in Publications & Subscriptions Folder.

At DR server, there's no item in Publications Folder, BUT there's 1 item (subscription) in Subscriptions Folder.

Can you pls provide step-by-step details in how to delete the subscription info using EM?

thanks...|||Simply right click the subscription in the subscriber and choose delete. If u get error; run sp_mergesubscription_cleanup (if u r using merge replication) in the subscription db. It would clear all the subscription info.|||Thanks again TALAT,

Extra info: Previously I'm using Transactional Replication. At the Subscriber, in the Subscriptions Folder, it shows 1 item of subscription with the Type: Push

When I right-click on either the Subscriptions Folder or the Subscription itself, there's no Delete option to choose from, this is what bugging me.

Any idea?

Thanks first.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 to remove replication manually.|||Thanks Satya, the link is useful.

What's really bugging me is why there's no simple way to do this in EM?

rgds|||Sometimes its better to follow system supplied SPs rather than depending upon Lazy GUI tools, as far as my exp. conveys.

Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END

Cant Keep Primary Key Columns when moving db to SQL 2005 from 2000

there are some primary key columns which are autoincrement in some tables.
for exampl page_id, now we have deleted many pages in old server so there
are many gaps between the page_id
even the it doest start with 1 because during the testing many initial pages
were deleted.

but when we move it to the new server with sql2005 it gives them numbers from 1,2,3 and
so on.

I need to keep existing 2000 keys. How can I acomplish this?

it would have worked if it also updates this number in other tables which
refer to this column
for example page_id is autoincreement in tbl_category_pages ( this table
contains the page information)
and it also appears in tbl_page_content ( this table contains the page's
content)

because it changes this number at one place and not the other place it makes
the database incorrect.

i tried many things ( there is even an option to keep the values of such
columns but doesnt seem to work)

Thanks,

WessTo maintain existing relationships, simply select all tables and copy them to a notepad. From there you just copy and paste them in the query anlyzer.|||If you want tables with relationships and data, take a complete back up of the database. Use that backup file and restore in sql 2005.|||ramasp,

Thanks, I appreciate your help.

I have given this information to the developer I have working on this. He tells me he tried this and that it can not work because the two versions are different.

Please let me know if this should work and the proccess to do so.

Thanks,

Wess