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
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
Oct 22, 2015
Replicate ddl setting is not working if multiple subscribers are used...
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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