Do Not Replicate DELETE Statements Still Replicates Deletes To Subscribers

Dec 4, 2006

I previously asked What does "Do not replicate DELETE statements" do? to make sure I was correct on my thinking of what "Do not replicate DELETE statements" does. And after finding out it does what I would like it to do, create an archive db, I've tried several testing scenarios to see if it would work. So far I have been unable to not replicate delete statements. I'm not sure if I'm not setting a property right or what and any guidance would be appreciated.

Here is what I've done.

Created a blank db to be used as a subscriber and created a test db with some random data in a table.
Setup a New Publication on the db with the random data as "Transactional Publication"
Selected the following articles and properties in the publication

Tables

DELETE Delivery Format = Do not replicate DELETE statements
Views

Default Values
Stored Procedures

Default Values
User Functions

Default Values
Selected the default options for the rest of the New Publication Wizard steps and clicked finish.
Created a Pull Subscription on the new publication that I just created.
Let it initialize.
Then did a select count(*) query on the test table on the publication (18k+ rows) and subscriber (18k+ rows)
Then did a delete t-sql from the test table on the publication.
Then did a select count(*) query on the test table on the publication (0 rows) and subscriber (0 rows).

Now shouldn't it not delete the records on the subscription db?

View 6 Replies


ADVERTISEMENT

What Does Do Not Replicate DELETE Statements Do?

Dec 1, 2006

I'm trying to find information and/or articles on what exactly "Do not replicate DELETE statements" does in transactional replication. How does this affect the Publisher, Distributor, and Subscriber? Does information deleted on the publisher not replicate to the subscriber? What scenarios would someone use this option on a article?

Sorry noob to replication and SQL 2005....

View 6 Replies View Related

Replication :: Replicate DDL Setting Is Not Working If Multiple Subscribers Are Used

Oct 22, 2015

Replicate ddl setting is not working if multiple subscribers are used...

View 2 Replies View Related

Transact SQL :: Cascade Delete Trigger Only Deletes 2 Levels

Aug 4, 2015

I have a table which has a recursive relationship to its self neither of the columns involved are FK columns

CREATE TABLE [dbo].[ix_Trace](
[Trace_Id] [uniqueidentifier] NOT NULL,
[Trace_BubbledTraceEnter_Id] [uniqueidentifier] NULL,
[Trace_EnterId] [uniqueidentifier] NULL,
[Trace_Application_Id] [uniqueidentifier] NULL,
[Trace_Session_Id] [uniqueidentifier] NULL,
....

Where Trace_EnterId is the parent ID and BubbledTraceEnter_Id is the child ID.  My test case has nested trace lines that go down 5 levels, but when I delete the top trace line, only the child directly under it is deleted leaving the other 3 as orphans.  Here's the trigger:

ALTER TRIGGER [dbo].[DTrig_xTrace]
ON [dbo].[ix_Trace]
FOR Delete
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM ix_Trace
FROM ix_Trace INNER JOIN
deleted as dt ON ix_Trace.Trace_BubbledTraceEnter_Id = dt.Trace_EnterId

How can I make this cascade all the way down?

View 7 Replies View Related

Do Not Replicate DELETE Statement Feature Problem

Sep 17, 2006

I am testing SQL Server 2005.
I need a replication where "DELETE delivery format" field in Table Articles Properties is set to "Do not replicate DELETE statement". Unfortunately nothing I set in this dialog is saved and used. Doesn matter what I change the replication behaves the same and the next time I open "Table Articles Properties", every value is still default.

Am I doing something wrong or is it an issue (bug) in 2005 replication?


I googled about the issue and also searched in the SQL Server Replication threads on this site, both with no success. The thread named "Selective replication of DELETE transactions" doesn't provide enough information about SWL Server 2005 replication configuration.


Use Case:
I have a small "operational" database with live data. I need to keep a complete history for all records that ever appeared within the "operational" DB. It came to my mind that I can "easily" achieve this if I set up a replication that does not replicate a delete statement ever. I strongly prefer not to mess with the subscription stored procedures but to configer my publication properly instead.

View 1 Replies View Related

Cascading Deletes - Which Is Better - A Trigger Or Foreign Key Cascading Delete?

Aug 17, 2005

I need to implement my cascading deletes on a SQL database.  Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best.

View 2 Replies View Related

Several Delete Statements In One SP

Mar 11, 2008

I would like to run more than 100 delete statements from a database.

is it possible to use all delete statements in one single stored procedure.

Thanks.

View 1 Replies View Related

Delete Statements Without Log Filling Up

Aug 13, 2002

I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.

This database is very large. The problem is when I run the delete statements
it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?

Has anyone ever done this and so how did it work. Or better yet is it possible?

Thanks,
Dianne

View 2 Replies View Related

Audit Delete Statements

Sep 21, 2007

Hi

I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

Any help will be greatly appreciated!

Thanks,
Alla

CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @StatusMsg varchar
declare @ServerTraceFile varchar
set @ServerTraceFile = 'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info'
set @maxfilesize = 1024

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1

exec @rc = sp_trace_create @TraceID OUTPUT, 0, N'\hostnamedbauditlogmy_dir', @maxfilesize, NULL
print @TraceID

if (@rc != 0) goto error
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
--SELECT @StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @TraceID
-- display trace id for future references
select TraceID=@TraceID

goto noCursor

error:
select ErrorCode=@rc

noCursor:
return


GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GO

View 3 Replies View Related

Delete Statements In Partitioned Views

Dec 10, 2002

Hi, I am using sql2000 ent edition. I have a partitioned view based on 8 tables. My selects and inserts are fine. But, when I run a delete on the view based on a query on the paritioned column, I get a "Transaction (Process ID 149) was deadlocked and has been chosen as a victim".
I looked at the query plan and it was showing a parallel query on all the underlying tables. So, I put the Option(maxdop 1), using only one processor and the delete worked fine.

Does anybody know why? is parallel query create deadlocks? is there any known problems with deletes on partitioned views?
same question for updates. I think I have the same problem for updates.

Any help will be useful.
thanks!!

View 1 Replies View Related

Combining DELETE And JOIN Statements

Nov 20, 2006

In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:

DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL

SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?

Gerrit

View 3 Replies View Related

Additional INSERT, UPDATE, And DELETE Statements Disabled.

Apr 21, 2007

Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one.  So I created a user here on www.asp.net just for making this post.
Before I continue I just want to apologies if there is another post where this question is already answered.
 

 
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “
As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason.
The MDF file is located in the App_data folder and is called ASPNETDB.
And when I try to add custom SQL statements, it gives me Syntax error near “=â€?. Something like that.  I bought the Total Training Set1 package and it works perfect in their examples.
I just want to thank you for reading my post and I hope that you got some useful information for me.
By the way, I’, from Sweden so you have to excuse me if my English is rusty.
Thanks!
PS: Can it be that I’m running windows Vista?
 

View 4 Replies View Related

INSERT, UPDATE And DELETE Statements Checkbox Inactive

Mar 26, 2008

I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance

the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG 

View 7 Replies View Related

Is It Possible To Audit Failed Insert, Update And Delete Statements?

Oct 25, 2004

Auditors want us to track when Insert, Update and Delete failures occur. Is this possible in SQL 2000?

They also want us to track schema changes. Is this possible?

Thanks, Dave

View 5 Replies View Related

Transact SQL :: Not Allowed To Write Delete Statements In A Function?

Jun 25, 2015

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

View 8 Replies View Related

Transact SQL :: Reducing Deadlocks From Conflicting Delete Statements

Jun 15, 2015

I am working on an app that getting quite a few deadlocks due to delete statements.  I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks.  I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.

What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.  

Open this url in new tab [URL] ....

View 7 Replies View Related

SqlDataSource: Can't Gen INSERT/UPDATE/DELETE Statements; Using 2.0 Membership Aspnetdb

Jul 20, 2006

Hi,I'm new to ASP.NET and having a problem configuring the SqlDataSource control.  I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts.  The problem is this:When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out.   I've searched this forum and found that this can be a symptom of no primary keys in the tables.  However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe).  When I use the wizard, I make the following choices:How would you like to retrieve data from your database?-> Select "Specify columns from a table or view"-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list-> Select "UserId", "Email", "UserName"  from "Columns:"After this, still no option to auto-generate I/U/D statements.  Any thoughts on why this isn't working??? Thanks,Leah.

View 1 Replies View Related

Data Access :: Delete Statement Blocking Insert Statements

Jul 17, 2015

We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs? 

View 3 Replies View Related

SQL Data Source Control...Generate Insert, Update && Delete Statements...

Apr 23, 2008

I have an SQL data source on my page and I select "Table".  On the next screen I pick the fields I want to show.  Then I click the "Advanced" button because I want to allow Inserts, updates and deletes.  But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too.  Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success

View 2 Replies View Related

Trigger-assigned Value Changes When Row Replicates - Why?

Feb 14, 2008

Greetings,

We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.

We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.

The code used to grab the identity and time of the last update is shown below.


LastEditedDate = GETUTCDATE(),

LastEditedBy = SUSER_SNAME()

What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.

For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?

Thanks,
BCB

View 5 Replies View Related

DDL Still Replicates Even With DDL Replication Off (@replicate_ddl = 0).

Mar 29, 2007

There are times when we need to make DDL changes and have them NOT replicate to the subscription database. The documented approach is to disable DDL replication using sp_changepublication (replicate_ddl = 0). See http://msdn2.microsoft.com/en-us/ms147331.aspx



However, our DDL still replicates even when following those steps. I've even tried the very simple steps below to reproduce the error.

Set up transactional replication of one small test table. Replication is working fine.
Set @replicate_ddl = 0 with sp_changepublication. I manually verify the setting (using the GUI) before continuing.
Run a simple alter on the publication DB; change one column from varchar(25) to varchar(30). "ALTER TABLE <tablename> ALTER COLUMN <colname> VARCHAR(30) NULL".
The DDL still replicates. The subscription table still has the column altered to the new definition.

I've even tried manually setting the option in the GUI and also stopping and restarting the synchronization after changing the setting; nothing works.



We are using SQL Server 2005 and just upgraded to SP2 a few days ago.



I have not found any other articles/postings on the topic so it appears we are the only ones having this problem. If anyone has any advice or input, it is greatly appreciated.



Regards,

Adam

View 4 Replies View Related

What Replicates?? Whole Row Or Just Updated Columns??

Oct 12, 2007

I was asked this question due to performance issues..

If you update a 1 or two columns in table, what gets replicated?? Is it the whole row or just those columns that were updated??

I am trying to find this answer cause it will have an effect on the way we do some updates in the future.
Thanks...

also if you have any links to information about this that would be great.

View 1 Replies View Related

Only Small Subset Of Rows Replicates To MySQL

May 1, 2007

I have been messing around with trying to replicate sql server 2000 tables to postgres as well as mySQL. I'm starting with just one table and trying to get that working, it has around 9000 rows, all of which copy to postgres (with some datatype issues).

However, when I try to replicate to a mySQL database however, only the same 152 rows get copied every time.

I compared rows that were copied to ones that were not to see if there was any obvious differences between the two and couldn't find any.

View 1 Replies View Related

Replicate Schema/Replicate Data Only

Sep 7, 1999

Using SQL 7.0 I'd like to replicate just schema from DB on server A to DB on server B, then be able to replicate data only form DB on server B to DB on server A. I need help!!

Thanks for ANY information you can give me...
~Jepadria

View 2 Replies View Related

Replicating UDT's To Subscribers

May 21, 2007

I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.



would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?

View 5 Replies View Related

What Is The Influence To Immediate Updating Subscribers?????

Jun 29, 2004

if there is something wrong with the network link between publishing server and subscribing server,and will not be repaired in a few days ,what is the influence to immediate updating subscribers?????

View 1 Replies View Related

Slow Performance On Subscribers

Dec 12, 2005

Hello all!

View 1 Replies View Related

Distinguish Between The Publisher And The Subscribers

Nov 10, 2006

I have a database on SQL Sever 2005 SP1 against which a Publication has been defined and to which many servers (both Workgroup and Express editions - SP1) Subscribe to.

I would like to be able to distinguish between the Publisher and the Subscribers programmatically via T-SQL.

From reading BoL and various forums it appears that the IsPublished, IsMergePublished and IsSubscribed options of the DatabasePropertyEx function should give me this information.

However within all our tested environments, whilst the IsMergePublished option returns expected values. IsPublished and IsSubscribed both return 0 on all servers (the Publisher and Subscribers).

Is this a know issue and how can I rectify the problem or alternatively does anyone know of another method to distinguishing between the Publishers and Subscribers.

Thanks

View 3 Replies View Related

Replication :: One Publisher 2 Subscribers

Nov 9, 2015

Is it possible to replicate data from one publishers to a multiple subscribers in transaction replication? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 10 articles from A to B but only 1 from A to C. In other words I want to use the same publisher but select one article from that publisher instead of all 10 to Server C/database C.

If it is possible what will be the drawbacks of such implementation? Will performance be a hit?

View 5 Replies View Related

No. Of Subscribers In Transactional Replication...

Jan 25, 2007

Hi,

I am Using Transactional Replication with Updatable Subscriptions. Is there any limitations in the number of subscribers (Servers) used for this type of replication?



I have a scenario of configuring this replication in 60 Subscribers(Servers). And the replication should be in the Continuous running mode. Will Transactional Replication with Updatable Subscriptions work in this scenario??? Or is it meant to work for less than 10 subscribers?



Please reply asap.



Thanks in advance.



Regards,

Swapna.B.

View 3 Replies View Related

Compressed Snapshot With Push Subscribers

Oct 16, 2007

Hi,

I have a server in our central location which is a compressed snapshot publisher. I have 2 push subscribers in remote locations on very slow WAN links. I would like the snapshot cabinet file to be uncompressed at the subscribers location rather than the publisher location. Is this possible with push subscribers? I want to manage the pushing of data to the remote subscribers from the publisher location.

I understand the default with push subscriptions is to uncompress the cabinet file at the publisher location.

Thanks,
Mark.

View 3 Replies View Related

Transactional Replication With Updateable Subscribers

Nov 28, 2005

Hi,

View 7 Replies View Related

Schema Changes No Longer Replicated To Subscribers

Apr 21, 2006

Hi to all,

I have a merge (pull) replication between SQL Server 2005 and SQL Server Express clients.

Data synchronisation is ok, and I already made some schema changes (like adding new columns) at the publisher database which were applied to the subscriber as they should.

Now this doesn't work anymore. New columns added at the publisher (with ALTER TABLE or Management Studio) are no longer replicated to the subscribers.

I don't get any error messages, and I can't find any hints in the logs why this has stopped working.

The "Replicate_Schema_Changes" is still set to TRUE (in general, none of the publication options had been changed).

I tried sp_enumeratependingschemachanges to find out any "bad changes" put the sp returns nothing.

I remember that the last thing I did was adding a default constraint, which was replicated successfully to the subscribers.

Can anybody help?

Michael

















View 8 Replies View Related







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