One DELETE sql statement to delete from two tables

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

I think Triggers may help for this scenario...

I am not sure about one delete statement to delete from two tables.

Thanks

Sreekanth

|||

If both tables has the relation (child-master with foreign key), then you can use the ON DELETE CASCADE on your Primary Key (Master) constraint.

Code Snippet

Create MasterTable

(

Id int Primary Key On Delete Cascade,

..

..

)

Create Childtable

(

Master_TableId int References MasterTable(Id)

..

..

..

)

If the tables doesn’t have any relation (logically bound), then you can use the Trigger to delete the values from the tables – using DELETED special table.

Code Snippet

Create trigger trigger_name On FirstTable For Delete

As

Begin

Delete From SecondTable Where ID in (Select ID from DELETED)

End;

Go

Delete From FirstTable

If you use SQL Server 2005, then you can use the OUTPUT clause, to get the deleted values from the current table, and supply those deleted values to remove the records from other table.

Code Snippet

Declare @.DeletedIds Table

(

Id int

)

Delete FirstTable OUTPUT DELETED.ID INTO @.DeletedIds Where <condition>;

Delete From SecondTable Where ID in (Select Id from @.DeletedIds);

|||

More information may be required for answering this. The simple answer is, no, you cannot "technically" do this. Any method that you can simulate this will technically be multiple SQL operations. Triggers, ON CASCADE constraints, etc.

There is really no need for one statement to delete from multiple tables, the key for your needs is probably that you want to make sure that if one statement completes, then the other statement completes. This is referred to as an atomic operation, and is managed by Transactions. So, simplistically (you need error handling/messaging for sure) in the following:

BEGIN TRY

BEGIN TRANSACTION

DELETE 1

DELETE 2

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

If DELETE 2 fails, but DELETE 1 succeeds, DELETE 1 will be undone.

sql

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On delete trigger for a view

Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview]
FOR DELETE
AS
Declare @.user_name sysname, @.msg varchar(3000)
select @.user_name = name
from deleted
set @.msg = 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur)
values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur) values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m" wrote in message news:a388fd=78.0309300241.4462ca59@.posting.google.com...Hi!I have a view defined in MANAGE database as:CREATE VIEW dbo.sysdatabasesview ASSELECT *FROM master.dbo.sysdatabases WITH (nolock)GOTrying to place a =trigger on it:CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name =sysname, @.msg varchar(3000) select @.user_name =3D name from deleted =set @.msg =3D 'Delete database ' + @.user_name + ' on server ' =+@.@.servername + ' from host ' + host_name()insert into MANAGE..MAIL (recipient, =subject, message, occur) values ('myemail@.domain.local', ='Delete datadase', @.msg, getdate()) Get an error:Error =208: Invalid object name 'dbo.sysdatabasesview'What is wrong?Thanks.

---

--=_NextPart_000_0012_01C3872D.EA0155E0--sql

On DELETE On UPDATE Cascade syntax error

Hello

I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.

I have already created the tables with primary keys on each table and foreign keys linking each table to the next.

I tried to delete a row from the parent table and was given this error:

DELETE FROM [dbo].[DomNam]
WHERE [DomNam]=N' football '

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.

I tried to insert an alter table query:

ALTER TABLE dbo.DomNam
ADD CONSTRAINT FK_Dom_ID

REFERENCES dbo.Dom_CatA (Dom_ID)

ON DELETE CASCADE ON UPDATE CASCADE

But on Execute I saw this error:

Error]
Incorrect syntax for definition of the 'TABLE' constraint

What is wrong with the above syntax?

Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?
If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.

I would be grateful for any advice. Thanks.

Hi

Have you missed something like this:

ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_IDFOREIGN KEY (Dom_ID)REFERENCES dbo.Dom_CatA (Dom_ID)ON DELETE CASCADE ON UPDATE CASCADE

Trigger could result such an error( DELETE statement conflicted with COLUMN REFERENCE constraint ..... ) either.

|||

(I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.)

The above means what you are looking for require a trigger because Cascade On DELETE and UPDATE is DRI(declarative referential integrity), that means if A references B then B must exist, it is very clean simple relational Algebra, anything outside that you need a trigger. But you may have some columns that qualify for it, try the threads below for how to enable it. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

|||

Hi

Thanks guys for your reply and help.

I think that my problem is that I have created a many to many relationship without foreign key restraints.

On the parent table I have used the primary key from the first child table "A" as the foreign key.

The child tables are from A to Z.

With the first child table "A" I have used the Primary key from the parent as the foreign key.

With the child table "B" I have used the Primary key from the previous "A" table as the foreign key in "B" table. I have used the primary key as the foreign key in all subsequent tables without declaring referential integrity.

I know that in the very near future I will need to update and delete much of the records in these tables.

How do I remove the foreign keys so that I might be able to create a junction table, which I have since found that I need for a many to many table relationship. Or is there another easier way to do this?

Thanks

Lynn

|||

Hi guys

I have since learned how to delete the foreign keys.

I am viewing my SQL 2000 database using Aqua Data,

* Click on the last sibbling table.

* Click Alter table

* Select constrains tab

* Select FK + delete + OK

I have kept my Primary Keys in tact.

Now I need to relink my tables and ensure that I am able to cascade update or delete. All tables contain similar content, which will require updating or deleting often. Tables are from A to Z.. What is the best method to do this?

Thanks

Lynn

|||I have told you that all you can do is one to one referencing for DRI(declarative referential integrity) to work so if you have figured out the tables that qualify for if A references B then B must exist at the top of Enterprise Manager you will see enable relationship. And those that did not qualify you need a trigger. I hope I am very clear it is not DRI for A to Z but A to B. Hope this helps.|||

I just remembered I had a conversation with a database person who did not understand DRI(declarative referential integrity) so let me explain you have tables A to Z meaning 26 tables it means you can have only 13 DRIs that is 13 Cascade DELETE and Cascade UPDATE because it is a location based way to delete and update related data. So although foreign key is required to enable DRI not all foreign keys can be DRI.

You said the data is related that is DDL(data definition language) where relationship is determined by Upper and Lower bound Cardinality while DRI is DML(data manipulation language) keeping track of child data through one to one mapping if A references B then B must exist. I hope this makes it clear. You have to be careful with the ALTER TABLE ADD or DROP CONSTRAINT in 2000 because most ALTER must be done with Enterprise Manager or SQL Server 2000 will tell you it is wrong. One more thing you can use trigger to do it the way you want but triggers are resource intensive and may not fire all the time. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

|||

Hi Caddre

Thanks for the reply, as you have gathered I am not a sql database expert, I am trying to understand DRI.

The whole think would be easier if I had everything in the one table (but it would be too wide to manage) because the same column of data is repeated on every table A to Z., therefore data row of "John Smith" exists in all tables. But in the child tables "A" table for example "John Smith" has extra columns e.g. art, apples, auto, etc.,

Consequently if I remove or insert a row in parent table, the children need the same edit.

Would it be just as complicated if I created a new table "juntion table" and include all the primary key's from the parent and children tables as foreign keys in the junction table. Or would that not work?

Thanks

Lynn

|||

I am sorry I did not come to this thread to confuse you but what you are saying now is not relational, so I have found you two solutions you can add to valid DRI. I also think it is Microsoft documentation that confused you because in 2005 multiple Cascade is allowed but one table cannot be repeated twice that is the same thing I am saying. Hope this helps.

http://support.microsoft.com/kb/142480

http://msdn2.microsoft.com/en-us/library/aa224818(sql.80).aspx

ON DELETE CASCADE problem for same table column constraint

Hello,
I would like to have table with foreign key referencing to the column of the
same table. And I want to specify ON DELETE CASCADE to this column, e.g.:
create table category (
ID INTEGER IDENTITY(1,1) NOT NULL,
PARENT_ID INTEGER NULL,
NAME VARCHAR(100) NOT NULL,
CONSTRAINT CAT_PK PRIMARY KEY (ID),
CONSTRAINT CAT_FK FOREIGN KEY (PARENT_ID)
REFERENCES CAT(ID) ON DELETE CASCADE
)
But I receive error:
--
Error: java.sql.SQLException: Introducing FOREIGN KEY constraint 'CAT_FK'
on table 'category' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints., SQL State: S1000, Error Code: 1785

It means I cannot specify ON DELETE CASCADE clause.
HOW CAN I ENSURE CASCADE DELETING FOR ALL RECORDS (ALL CATEGORIES WITH BELONGED SUBCATEGORIES)?
IS THERE POSSIBILITY TO USE STORED PROCEDURE FOR THIS?
CAN YOU POST AN EXAMPLE PLEASE?

Thank you in advance
best regards,
Julian LegenyThis is a restriction in all versions of SQL Server that supports cascading constraints. You will have to implement the cascade action using triggers or in your SPs that perform the data modifications.

on delete cascade on the same table - is it possible?

Hi !
as u should know this code doesn't work ..
CREATE TABLE tempo (
cid int,
parent_id int,
PRIMARY KEY (cid),
FOREIGN KEY (parent_id) REFERENCES tempo ON DELETE CASCADE
)
any suggestion to achieve this ?
thx!Create a trigger to delete all the records with the same criteria. When a record is deleted in that Table.

This Code may help

create trigger delcascadetrig
on tempo
for delete
as
delete parent_id
from tempo, deleted
where tempo.cid = deleted.cid

on delete cascade & hierarchical table

for MS SQL 2000
I am trying to do a hierarchical table and i want to add a ON DELETE CASCADE

CREATE TABLE [dbo].[Users](
[id_Users] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_UsersSup] [int] NULL,
[Users] [nvarchar] (100) NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [FK_Users_Sup] FOREIGN KEY
(
[id_UsersSup]
) REFERENCES [Users] (
[id_Users]
)
ON DELETE CASCADE

but MS SQL refuse to create the foreign key
even if there is 4 levels under the deleted id_Users I want to delete all the rows on all levels under

thank you for helpingI tried running your code on SQL Server 2005, and got the following message:

Introducing FOREIGN KEY constraint 'FK_Users_Sup' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

In my opinion, this error message tells pretty well why you are not allowed to use on delete cascade on a self-join.|||You will need to implement this cascading referential integrity using a trigger.

ON DELETE CASCADE

hello guys!

Well im using MS SQL 2000 and i have over 250 Tables in one of my Database the problem is all the Foreign Key in the Table is not turn ON in ON DELETE CASCADE.

The question is, is there anyway to write a script to turn the ON DELETE CASCADE on all the Tables?

Thanks for the reply!

Novelle

Nope, not really. You will just have to script your keys out, then change to ON DELETE CASCADE manually. Unless there is some tool to do it, but I don't know of it.

A trick I would use is to take your database scripts (or if you don't have any, use a comparison tool, compare my full database to an empty one) and just replace all ON DELETE NO ACTION values with ON DELETE CASCADE. Then use your script to build a database that matched your database, except for the CASCADE constraints.

Then just do the comparison again, and let the tool do the work. (I use RedGate personally, but there are others)

sql

On delete cascade

Hi,
1. To my knowledge from sql 2k, there is an option "On delete cascade" and
"On update cascade". But in almost all databases which I have seen till date
,
user defined SP's have been created for deleting table content instead of
checking this "On delete cascade" option. Is there any specific reason for
this? i.e., will there be any performance or anyother reason behind this?
2. Lets assume that I have enabled "On delete cascade" in my DB. If I delete
a record in master table it should delete corresponding details in 4 to 5
tables. What would happen if it fails for some reasons while deleting the 3r
d
table content. Will it rollback?
Regards
Pradeep1. "will there be any performance or anyother reason behind this?"
-No, some guys really want to have their own hand on the deleting of
the data, due to extra issues with logging business rules checking etc.
2. "Will it rollback?"
Sure, thats what a transactional database is for.
HTH, Jens Suessmeyer.|||If can be a safety issue too.
For example, if you use Enterprise Manager to delete a parent row,
referential integrity will prevent you from deleting it if there are child
rows. With cascade delete turned on, the parent and all related children ar
e
deleted. If it was deleted by mistake, it can be quite a complex task to
restore the parent and all the children.
Joe
"Jens" wrote:

> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>|||There are technical reasons behind this as well. Cascading referential
actions complicate deadlock minimization because there isn't any way to
determine with certainty the order in which locks will be obtained. If you
issue the DELETE or UPDATE statements in a procedure, you have control over
the order in which locks are obtained and thus can prevent most deadlocks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133783057.518482.200520@.g47g2000cwa.googlegroups.com...
> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>

OLEDB command gets compile errors but works in Query analyzer.

The following statement is valid in query analyzer but will not compile as a prepared statement in an OLEDB command in DTS 2005.

delete
from
purEncumbrance_Fct
where
AgreementId = ?
and FundId = ?
and AccountId = ?
and coalesce(PODistributionId,0) = coalesce(?,0)
and coalesce(VoucherDistributionId,0) = coalesce(?,0)

Why does this statement not compile?

KenTry putting square brackets around the table and column names. I have a vague recollection of this working for me in the dim and distant past.

-Jamie|||Jamie,

I tried your suggestion with great hopes, but it did not work.

I wonder if this is a bug or a limitation with prepared statements. I know this command works in query analyzer, so maybe if I place it in a stored procedure it will work. I don't want to have to manage another piece of code, but if that is what it takes, I will.|||Not tested, but I know the statement prepare stuff and OLE-DB parameters can be rather fussy. Try loosing the coalesce(?, 0) and just use ?. Assuming that works, handle the coalesce values through a derived column, e.g.

ISNULL(Column) ? 0 : Column

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

Old data from mdf deleting problem again!

Hello Guys!

Thanks for your previous posts to me, but I'm still in trouble.
I have card.mdf file and I need to delete data older than 3
months from the table called Events.
I tried the following:

use card.mdf
delete from Events where datediff(m,Field Time,getdate()) >= 3

"Field date" is the name of the colums where dates are logged.
But in query analyser it says that there is an error near "Time"
What am I doing wrong.

Please help me,
JessicaI suspect that your problem is caused because of the space in the "Field Time" column name. I think you will need to put quotes around this column and try again.|||Originally posted by dbabren
I suspect that your problem is caused because of the space in the "Field Time" column name. I think you will need to put quotes around this column and try again.

Sorry, I meant square brackets (doh!)

ie
delete from Events where datediff(m,[Field Time],getdate()) >= 3|||Thanks a lot dbabren, It now worked.
But do you know How I could run this
from dos command line?

Jessica|||Jessica

I assume that you mean it works via osql. I don't know is the short answer! I can only assume that the Syntax checker is not as strict in osql as it is in Query Analyser.|||Try this

use card.mdf
delete from Events where datediff(m,[Field Time],getdate()) >= 3

add [ ] around your column name.

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in you
r
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 a
s
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in y
our
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
>

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIA
Wait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:

> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA
|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
[vbcol=seagreen]
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:

Old backups are not deleted

Hi guys,
I find out that if you use DMP and change the backup path from default and
set the settings to delete old backup , say after 3 days, it doesnt really
delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
well as in SQL2005
TIAWait one more day, you need to check the clock it will delte it on the 4th
day only. We are also using DMP and specified "Use This Directory",
Remove files older that 3 days , and provided "backup file extension" as
"BAK" same as i provided for backup's... You have not mentioned that in your
configuration list. Try "backup file extension" and see. Let me know if it
works? Check MSSQL/ErrorLog also and see if there is something unusal.
Thanks,
Sree
"rupart" wrote:
> Hi guys,
> I find out that if you use DMP and change the backup path from default and
> set the settings to delete old backup , say after 3 days, it doesnt really
> delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> well as in SQL2005
>
> TIA|||hi Sreejit,
Yes i have specified .bak or .trn. But i saw there are files more than 4
days. SOme are even 1 mth back. CLose to the day we started to backup.
"Sreejith G" wrote:
> Wait one more day, you need to check the clock it will delte it on the 4th
> day only. We are also using DMP and specified "Use This Directory",
> Remove files older that 3 days , and provided "backup file extension" as
> "BAK" same as i provided for backup's... You have not mentioned that in your
> configuration list. Try "backup file extension" and see. Let me know if it
> works? Check MSSQL/ErrorLog also and see if there is something unusal.
> Thanks,
> Sree
>
> "rupart" wrote:
> > Hi guys,
> > I find out that if you use DMP and change the backup path from default and
> > set the settings to delete old backup , say after 3 days, it doesnt really
> > delete them. IS it a bug? Is there any fix for it? It happens in SQL2000 as
> > well as in SQL2005
> >
> >
> > TIA

Old backup are not delete

I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running perfectly.
I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
But if I check in the directory were my backups reside, I still see the last
5 daily backup?
Is there something that I forgot?
Thanks
JPJP
Have you checked that an account under SQL Server Agent runs has appropriate
permissions to delete these files?
"JP Breton" <jpbreton@.videotron.ca> wrote in message
news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
> backups are running perfectly.
> I also checked in the database maintenance plan to the "Remove files older
> than: 1 day"
> But if I check in the directory were my backups reside, I still see the
last
> 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.ph
x.gbl...
>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running
>perfectly.
> I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
> But if I check in the directory were my backups reside, I still see the la
st 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>|||Thanks Uri, thanks Tibor,
The SQL agent is ruuning with domain admin rights (I know it should not be
that way)
But I will still verify to make sure that the account can delete files.
Regards
JP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23fxRENZSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Below KB might help:
> l]
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from [url]www.sysinternals.com." target="_blank">http://support.microsoft.com/defaul...sinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JP Breton" <jpbreton@.videotron.ca> wrote in message
> news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>

Old backup are not delete

I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
backups are running perfectly.
I also checked in the database maintenance plan to the "Remove files older
than: 1 day"
But if I check in the directory were my backups reside, I still see the last
5 daily backup?
Is there something that I forgot?
Thanks
JP
JP
Have you checked that an account under SQL Server Agent runs has appropriate
permissions to delete these files?
"JP Breton" <jpbreton@.videotron.ca> wrote in message
news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
> I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The
> backups are running perfectly.
> I also checked in the database maintenance plan to the "Remove files older
> than: 1 day"
> But if I check in the directory were my backups reside, I still see the
last
> 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JP Breton" <jpbreton@.videotron.ca> wrote in message news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>I have setup a maintenance plan to do a daily backup of my 10 SQL BD. The backups are running
>perfectly.
> I also checked in the database maintenance plan to the "Remove files older than: 1 day"
> But if I check in the directory were my backups reside, I still see the last 5 daily backup?
> Is there something that I forgot?
> Thanks
> JP
>
>
|||Thanks Uri, thanks Tibor,
The SQL agent is ruuning with domain admin rights (I know it should not be
that way)
But I will still verify to make sure that the account can delete files.
Regards
JP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23fxRENZSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
> to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
> consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
> would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
> to
> local drive), then you will need to modify the above (with respect to
> where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JP Breton" <jpbreton@.videotron.ca> wrote in message
> news:%23CcnHHZSFHA.3088@.TK2MSFTNGP15.phx.gbl...
>