Instead Of Delete In Replication

Jul 23, 2005

I am looking into work-arounds for what seems to be a flaw, or
"undocumented feature" of SQL Server replication and Instead of Delete
triggers not playing together. It seems that if you want to use
replication then you cannot use Instead Of triggers as it prohibits the
replication triggers from firing. My delima is that I need to cascade
delete, but first have the record and all child records inserted into
associated delete tables (<tablename>_del). This worked well using
Instead of delete triggers, but now we are moving to a replication
environment and it does not synchronize deletes to the publisher or
subscriber.

The options I am looking into and the stumbling points they introduce
are:

1. Not using Instead Of triggers, but using After to insert the
deleted record into a <tablename>_del table (where of course
<tablename> is the actual table name). The triggers are designed to
cascade delete all related child records. The problem with this
option is that there are relationship constraints that cause the
delete to raise an error complaining about these constraints. Of
course the child records need to be deleted first, but since this
runs after the delete I cannot perform this cascade delete via
trigger (or can I?).

2. Same as above only using SQL Server 2k's "Cascade Delete"
option. My question on this is, will the cascade delete execute the
After Delete triggers for each child table in the relationship? If
not, is there a way to move these deleted records into
<tablename>_del before they are deleted, keeping in mind that it
must be compatible with merge replication.

3. Not yet found any information on this, but is it possible to
manually include information to be replicated after the "Instead Of"
triggers so that the changes are included in the replication?
I really appreciate the advice.

View 3 Replies


ADVERTISEMENT

Replication Delete Problem

Aug 31, 2001

We use a SQL Server 2000 Publisher and 2 pull subscribers.
Unfortunately we have some data loss from time to time. Some
records are deleted during the replicaton process. Actually
there shouldn't be any conflicts because of the permissions
of the application, so we use standard conflict resolving that
is selected when a publication is created, no extra options.

Any Ideas about that? Is it possible to dissallow deletes for
replication?

Any help would be greatly appreciated!!!

View 1 Replies View Related

Delete Snapshot Replication

Sep 6, 2006

When I try to drop the publication in this order:

exec sp_dropsubscription @publication = @publicName, @subscriber = @servName, @article = N'all'

exec sp_droppublication @publication = @publicName

I am getting the following error after dropping the publication

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271

Could not complete cursor operation because the table schema changed after the cursor was declared.

Could anyone give me a suggestion about how to avoid this error?

Thanks!

View 5 Replies View Related

Replication. Cant Delete Rows

Feb 9, 2007

Hi All.

I have the folling replication issue:

BD1 ------------------------> BD0
Replicate with filter

BD2 ------------------------> BD0
Replicate with filter

BD3

Four data bases BD0,BD1,BD2,BD3
In BD1 y BD2 there are two merged publications subscribed continuous in BD0.
The publications are defined as insert only and allow changes in the subscriber. Everything works perfectly. The records replicate immediately. In BD0 you can insert and modify records, however when I attemept to delete a record I get the following message:


error:

You can't insert a row with a duplicate key in the object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'

Note: This is a translation of the original error message in Spanish below.


No se puede insertar una fila de clave duplicada en el objeto
'dbo.MSmerge_tombstone' con índice único 'uc1MSmerge_tombstone'

Cheers,



View 1 Replies View Related

Can't Delete Db Thas Been Part Of Replication

Feb 7, 2007

I setup replication (merge) for a test db with 11 tables. Deleted the publication and want to delete the database itself. Sql server says it can't be deleted becuase it's has replication setup! Bug??

View 1 Replies View Related

Delete A Merge Replication Publication

Mar 15, 2007

I tried to drop a merge replication publication and it is giving me an invalid object name dbo.ms_mergeagents message. I tried to drop using:

SSMS
sp_dropmergepublication
sp_replicationdboption

I got the last two from Microsoft KB article 324401. The subscription was never created because it gave another error invalid object_name msmerge_subscriptions. I can create new publications but not drop the existing ones.

Please assist...

View 1 Replies View Related

Unable To Delete A Replication Job In Sql Server

Aug 22, 2007

Hi,

I created and scheduled a test snapshot publication and a push subscription using the sql server replication wizard. Tested it and it worked all fine as the data was regularly replicated to the destination server correctly.

After I was satisfied with the test I deleted the replication and subscription without any problem. But I noticed that the jobs created by the replication wizard had not been deleted. I was disappointed as I had also expected these to be deleted because there are no more any local publication or replication on the server.

I, then, tried to delete the job by right click - and then delete. But I am unable to delete the pubilcation and subscription jobs at all. When I try to delete the sql server 2005 the following error message appears:

--start--------------------------------------------------------------------
"Attempt to retrieve data failed for object for server <MYSERVER>" (Microsoft.SqlServer.Smo
)
Addional Information

The job <jobname> does not exist on the server. (Microsoft.SqlServer.Smo)
--End--------------------------------------------------------------------

Can anyone help me on how to delete all replication jobs.

View 1 Replies View Related

Delete Entries In Replication Monitor

Oct 2, 2007

Hi everybody,

when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used.

I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used.

The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone.

So, is there a way to solve my problem?

We are using sql 2005 server as publisher and are using merge replication.

Thanks for help in advanced

Garfield

View 10 Replies View Related

Can't Delete Database That Has Been Part Of Replication

Feb 7, 2007

I had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug??

View 1 Replies View Related

Selective Replication Of DELETE Transactions

Jul 19, 2006

I am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.

I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.

Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.

My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?

View 6 Replies View Related

Transactional Replication Delete Data

Feb 15, 2007

In SQL Server 2005 SP1 we have a Transactional Replication with only this property in the articles:

Action: Delete data...

Ant this property in the publication:

Don't send schema changes

We have in the foreign keys NOT FOR REPLICATION = NO (Exigir para replicacion = "Si" in Spanish).

We have one problem and one doubt:

The problem is that when the sincronization goes wrong the table in the suscriptor is empty !!!!!. It seems that goes wrong because the foreign keys. The question is = Is necessary that NOT FOR REPLICATION = YES in all the tables?, we don't want any check in the sincronization.

Why the destiny tables appears empty?. Is very dangerous.

Thanks.

View 2 Replies View Related

Transactional Replication - UPDATE (Delete/Insert)

Feb 14, 2001

Hi,

Any updates on tables at publisher are being converted to Delete/Insert pair
at subscriber end with transactional replication. Our business requirement is not to delete data from few tables and also to get rid of Foreing Key
Constraint error.

How to avoid this Deletes during Update? Any ideas about writing custom stored procs?

Thanks

View 1 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Copy And Delete Table With Foreign Key References(...,...) On Delete Cascade?

Oct 23, 2004

Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View 1 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh


View 6 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?

Nov 26, 2007

this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
 
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
 
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you

View 1 Replies View Related

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question) 

View 2 Replies View Related

Allow Single Row Delete From A Table But Not Bulk Delete

Sep 16, 2013

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS

[code]...

When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

View 3 Replies View Related

Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did

Aug 20, 2007

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?

View 3 Replies View Related

Cannot Amend Or Delete Subscription And Cannot Delete Report.

Nov 20, 2007



Hi,

I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help







For more information about this error navigate to the report server on the local server machine, or enable remote errors


Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:





System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---



What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?

I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.

Any help would be much appreciated.

Thanks in advance,

Paul

View 3 Replies View Related

Delete From Vs Delete With Subquery In Transaction

Feb 23, 2006

First, this is not my code.

This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
FROM ClaimsReceivedPayors
WHERE SubmitterTranID = @SubmitterTranID)

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID
FROM ClaimsReceived
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO


I applied a couple of indices and got ride of the uncorrelated subqueries


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION

DELETE INVOICECLAIMMAPPING
FROM INVOICECLAIMMAPPING
JOIN CLAIMSRECEIVED
ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment
WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsreceivedPayorServices
FROM ClaimsreceivedPayorServices
JOIN ClaimsReceivedPayors
ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsReceivedPayors
FROM ClaimsReceivedPayors
JOIN ClaimsReceived
ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid
WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

GO


Suddenly this constraint was being violated with the change


ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYOR S] FOREIGN KEY
(
[ClmRecdPyID]
) REFERENCES [CLAIMSRECEIVEDPAYORS] (
[CLMRECPYID]
)


Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.

View 2 Replies View Related

To Delete Or Not To Delete - DESIGN ISSUE!

Apr 3, 2008

Hi all

Need some advice solving a little problem I have with my database!

Current setup:

I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!

The problem:

My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.

My thoughts:

I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?

Any design suggestions would be welcome!

View 3 Replies View Related

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Soft Delete In Table, Why Merge Agent Report Hards Delete On Table ?

Feb 1, 2007

Hi seniors

there are two tables involve in replication let say table1 and replicated table is also rep.table1.

we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.

plz let me know where am i wrong and how i put it into right way.

there is no triggers on published tables and noother trigger is created on published table.

regards

Ahmad Drshen

View 6 Replies View Related

SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.

Jun 15, 2007

I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.

View 3 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

Jan 17, 2002

Hi,

In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don

View 1 Replies View Related

DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication

Aug 22, 2007

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

View 4 Replies View Related

Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.

Mar 6, 2007

Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm

View 1 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

SQL Express Replication And Problems Accessing Data After Replication

Jul 28, 2006

Hi,

I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows

- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem

- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine

however.......

- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error

- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.

I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.

I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc

Any advice appreciated !

Thanks
Ronan





View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved