Deleting Legacy maintenance plans

Hi,
I am not able to delete the Legacy maintenance plans created:
for example I created a maintenance plan: "MyPlan" with:
master.dbo.xp_sqlmaint
and the plan created at:
Management\Legacy\Database Maintenance Plans\MyPlan

After this I tryed to delete this using SQL Server Management Studio, it is
deleted (right click on the plan and select delete menu item)
But when I refresh the server, the "MyPlan" is back again.

Any idea how to delete Legacy Maintenance plans?

Thanks,
Venkat

What service pack level are you on? This is something that could potentially have been resolved in SP2. Or even SP1 if you are on RTM. At any rate here are a couple

At any rate here are a couple links that might point you in the right direction... http://mssqltips.com/tip.asp?tip=1137 that article explains where these plans are stored (you can also run profiler while you drill into that section - note it gets populated before you click on maintenance plans, so start your trace before you even click the plus before Legacy.. It may stay cached so maybe just start Management Studio from scracth. That trace should confirm above and show a query towards the tables indicated. This will not be supported and should be tested thoroughly first but you should be able to delete from the tables referenced once you understand the information for the plan you are discussing.

Here is another forum article of someone who also had issues and there results..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=454044&SiteID=1

|||

Hi,

i am using the following SQL 2005 software:

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

I resolved the problem by explicitly depeting the records from the maintenance plan tables.Looks like the deletion from UI has some serious problem, it says deleted, but when i reopne the SQL Server management studio, i can still see my plans.

Any way the problem was solved, hopefully this will get fixed by MS in later service packs.

Thanks,

Venkat

|||

So it sounds like the links to the tables helped you out then.

Just a quick point, you say you hope this will be fixed in a later service pack.. You are not currently on the latest version of SQL Server 2005. You might want to try and upgrade first and see if it is fixed in a "later" service pack.

Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from

---

SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/

quote:

---

>.
>

---

|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila

quote:

---

>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of

---

select..group by

quote:

---

>statement.
> If you want to use group by statement, the content you

---

select should in

quote:

---

>the group by or have a function tell the machine how to

---

modify the data.

quote:

---

>Best Wishes
>Wei Ci Zhou
>
>.
>

---

|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy

quote:

---

>--Original Message--
>Wei,
>What should I do to delete these records' Can you

---

help

quote:

---

>with a better script?
>Thanks.
>Laila
>
>
>select..group by
you[QUOTE]
>select should in
>modify the data.
>.
>

---

|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?

Deleting duplicate records

I have a table with 25000 records from which to delete 600
duplicate records. for example, col2 has some values with
2 or more entries. When I execute the below script, I
keep getting errors as indicated below. What is the best
way to delete these 600 duplicates.
script file:
SELECT DISTINCT *
INTO test
FROM livetable
GROUP BY col2
HAVING COUNT(col2) > 1
DELETE livetable
WHERE col2
IN (SELECT col2
FROM test)
INSERT Documents_Convert
SELECT *
FROM test
DROP TABLE test
ERROR MESSAGE:
-- when I run the first part of the script, i get the
bellow error:
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col1' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
Column 'livetable.col2' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.
--Server: Msg 8120, Level 16, State 1, Line 1
column 'livetable.col3' is invalid in the select list
because it is not contained in either an aggregate
function or the GROUP BY clause.Hi:
I think the error is becasue the misunderstand of select..group by
statement.
If you want to use group by statement, the content you select should in
the group by or have a function tell the machine how to modify the data.
Best Wishes
Wei Ci Zhou|||I believe that you are using group by uneccessarily.
the distinct should give you a unique subset
try omitting the group by clause .
Scott J Davis
Ruprect@.satx.rr.com
--
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/|||Scott,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>I believe that you are using group by uneccessarily.
>the distinct should give you a unique subset
>try omitting the group by clause .
>Scott J Davis
>Ruprect@.satx.rr.com
>--
>Posted using Wimdows.net NntpNews Component - Posted from
SQL Servers Largest Community Website:
http://www.sqlJunkies.com/newsgroups/
>.
>|||Wei,
What should I do to delete these records' Can you help
with a better script?
Thanks.
Laila
>--Original Message--
>Hi:
> I think the error is becasue the misunderstand of
select..group by
>statement.
> If you want to use group by statement, the content you
select should in
>the group by or have a function tell the machine how to
modify the data.
>Best Wishes
>Wei Ci Zhou
>
>.
>|||Hi Laila,
This might be helpful..
http://www.developerfusion.com/show/1976/
Regards
Thirumal Reddy
>--Original Message--
>Wei,
>What should I do to delete these records' Can you
help
>with a better script?
>Thanks.
>Laila
>
>>--Original Message--
>>Hi:
>> I think the error is becasue the misunderstand of
>select..group by
>>statement.
>> If you want to use group by statement, the content
you
>select should in
>>the group by or have a function tell the machine how to
>modify the data.
>>Best Wishes
>>Wei Ci Zhou
>>
>>.
>.
>|||It seems that the sample does not work on my machine when I use (field,
field) in (....)
And After I read the BOL, it seems that we can use in statement in one
field, do you have any suggestion?

deleting all rows in table which references itself

Using SQL Server 2000.
The example code below shows a
table which references itself.
i.e. column "parent" references column "pk" :
==
create table table1 (pk int not null primary key, parent int null,
aname varchar(50) null)
alter table table1 add constraint fk_table1_table1
foreign key (parent) references table1 (pk)
go
insert table1 values (1, null, 'one')
insert table1 values (11, 1, 'one-one')
go
delete from table1 -- XX
==
Say I want to delete all rows from the table,
as at the line marked at "XX". Is the
SQL server processing of the delete
statement, such that the delete statement
will always succeed, or can I actually
get a constraint violation in the above example ?
I'm not seeing a constraint violation, but I'm not
sure if this is by luck, or by design.
TIA,
StephenStephen
You can get an error only if you will provide WHERE condition such as
delete from table1 where pk=1
If you can explain what are you trying to do , so it will be more easier to
came up with a solution.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:OLFHs3YNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Using SQL Server 2000.
> The example code below shows a
> table which references itself.
> i.e. column "parent" references column "pk" :
> ==
> create table table1 (pk int not null primary key, parent int null,
> aname varchar(50) null)
> alter table table1 add constraint fk_table1_table1
> foreign key (parent) references table1 (pk)
> go
> insert table1 values (1, null, 'one')
> insert table1 values (11, 1, 'one-one')
> go
> delete from table1 -- XX
> ==
>
> Say I want to delete all rows from the table,
> as at the line marked at "XX". Is the
> SQL server processing of the delete
> statement, such that the delete statement
> will always succeed, or can I actually
> get a constraint violation in the above example ?
> I'm not seeing a constraint violation, but I'm not
> sure if this is by luck, or by design.
>
> TIA,
> Stephen
>|||Uri,
What I was trying to work out was a safe way to delete all records in
tables which reference themselves, as in the example.
ie.
1) is it 100% safe to do : delete from table1,
("safe", as in : no constraint violation will ever be raised
in cases similar to the example, and all records will get
properly deleted). The table could also have many records
in reality.
OR
2) should I write a stored proc etc which deletes records
one by one, deleting child records first, then eventally
deleting the root nodes.
From your reply, it sounds like 1) is true.
Thanks,
Stephen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Stephen
> You can get an error only if you will provide WHERE condition such as
> delete from table1 where pk=1
> If you can explain what are you trying to do , so it will be more easier
> to
> came up with a solution.
>|||Stephen
Again, you are asking to different questions
Do you really want NO WHERE condition in your query?
I think the option 2 is right way to do that.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:euTJhZbNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Uri,
> What I was trying to work out was a safe way to delete all records in
> tables which reference themselves, as in the example.
> ie.
> 1) is it 100% safe to do : delete from table1,
> ("safe", as in : no constraint violation will ever be raised
> in cases similar to the example, and all records will get
> properly deleted). The table could also have many records
> in reality.
> OR
> 2) should I write a stored proc etc which deletes records
> one by one, deleting child records first, then eventally
> deleting the root nodes.
> From your reply, it sounds like 1) is true.
> Thanks,
> Stephen
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%230IbsOcNFHA.3772@.TK2MSFTNGP15.phx.gbl...
> Do you really want NO WHERE condition in your query?
Yes, this is a special case where we actually want to clean out the table
completely.
Thanks,
Stephen|||DELETE FROM Table1
is "safe" in the sense that it will not cause any violations of the
self-referencing foreign key. Constraints are validated against the
final result of a DML operation (except in the special case where you
use user-defined functions in a constraint and the function references
other rows in the same table).
David Portas
SQL Server MVP
--

delete trigger

Hi how can i use delete trigger?

For example i've a two table like emp and emp_personal and now what i want to do is

i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?

Ex:

emp emp_personal

emp_id emp_name emp_basic emp_id emp_address

101 Nagu 32,000 101 India

102 Vijay 35,000 102 South Africa

103 Ritesh 30,000 103 U.S

I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?

Is it possible with triggers?

Thanx - Nagu

hi Nagu,

you can implement a trigger on DELETE action but you can even implement a foreign key constraint with the desired action, that's to say ON UPDATE CASCADE and ON DELETE CASCADE to maintain referential integrity as

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.Emp (

emp_id int NOT NULL PRIMARY KEY,

emp_name varchar(10) NOT NULL,

emp_basic decimal (18,4) NULL

);

CREATE TABLE dbo.Emp_Personal (

emp_id int NOT NULL PRIMARY KEY,

emp_address varchar(15) NOT NULL

);

GO

INSERT INTO dbo.Emp VALUES ( 101 , 'Nagu', 32 );

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 101, 'India' );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

GO

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

CREATE TRIGGER tr_D_Emp ON dbo.Emp

FOR DELETE

AS BEGIN

IF @.@.ROWCOUNT = 0 RETURN;

DELETE FROM dbo.Emp_Personal

WHERE emp_id IN (SELECT emp_id FROM deleted);

END;

GO

DELETE FROM dbo.Emp WHERE emp_id > 101;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TRIGGER tr_D_Emp ;

GO

ALTER TABLE dbo.Emp_Personal

ADD CONSTRAINT fk_Emp$have$Emp_Personal

FOREIGN KEY (emp_id)

REFERENCES dbo.Emp (emp_id)

ON UPDATE CASCADE

ON DELETE CASCADE;

GO

INSERT INTO dbo.Emp VALUES ( 102 , 'Vijay', 35 );

INSERT INTO dbo.Emp VALUES ( 103 , 'Rithesh', 30 );

INSERT INTO dbo.Emp_Personal VALUES ( 102, 'South Africa' );

INSERT INTO dbo.Emp_Personal VALUES ( 103, 'US' );

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

DELETE FROM dbo.Emp WHERE emp_id < 103;

SELECT e.emp_id, e.emp_name, e.emp_basic,

p.emp_address

FROM dbo.Emp e

LEFT JOIN dbo.Emp_Personal p ON e.emp_id = p.emp_id

ORDER BY e.emp_id;

GO

DROP TABLE dbo.Emp_Personal, dbo.Emp;

--<--

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

emp_id emp_name emp_basic emp_address

-- -

101 Nagu 32.0000 India

102 Vijay 35.0000 South Africa

103 Rithesh 30.0000 US

emp_id emp_name emp_basic emp_address

-- -

103 Rithesh 30.0000 US

regards

|||

Thank you Andrea

Nagu

Delete Trigger

I'm trying to get a record added to clsrecord_accessed
when deleting (as an example) notes from the LONGNOTES
field. This isn't appearing to happen though with the
below code. What am I missing?
CREATE TRIGGER Prescott_Delete_ACTIVITY
ON sysdba.ACTIVITY
FOR DELETE
AS
IF UPDATE (LONGNOTES)
or UPDATE (DESCRIPTION)
or UPDATE (STARTDATE)
BEGIN
IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
= 'ABENJ0000022')
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Delete' FROM
Deleted D WHERE AccountID = 'ABENJ0000022'
ENDCheck out BOL for "create trigger". IF UPDATE() is not useful in a delete
trigger since columns are "mentioned" in the delete statement.
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||> columns are "mentioned" in the delete statement.
Did you mean "not mentioned"?|||so I change it to this, and still nothing:
CREATE TRIGGER Prescott_Update_ACTIVITY
ON sysdba.ACTIVITY
FOR Insert, Update, Delete
AS
IF NOT EXISTS (SELECT * FROM Inserted I WHERE I.AccountID
= 'ABENJ0000022'
IF EXISTS (SELECT * FROM Deleted I WHERE I.AccountID
= 'ABENJ0000022'
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Inserted I on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change' FROM
Inserted I
ELSE
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted' FROM
Deleted D
GO
>--Original Message--
>Check out BOL for "create trigger". IF UPDATE() is not
useful in a delete
>trigger since columns are "mentioned" in the delete
statement.
>"CalTab" <anonymous@.discussions.microsoft.com> wrote in
message
>news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>> = 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>> END
>
>.
>|||CalTab (anonymous@.discussions.microsoft.com) writes:
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>= 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
I took the liberty to reformat your trigger body, to make it more
readable:
1) IF NOT EXISTS (SELECT * FROM Inserted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
2) IF EXISTS (SELECT * FROM Deleted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
3) IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Inserted I
on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
BEGIN
A) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, REATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change'
FROM Inserted I
END
ELSE IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Deleted D
on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
BEGIN
B) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, CREATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted'
FROM Deleted D
END
END
END
So if this code is trigger by a DELETE, we will pass the first IF
statement, because Inserted is empty in a DELETE trigger. If the account
is the right one, we will pass the second IF statement too. And we
will always pass the third IF statement, because Inserted is empty.
This means that we will execute INSERT statement A, but since Inserted
is empty, nothing will happen.
The test in the ELSE IF statement is never executed, and neither is
INSERT statement B, when trigger is fired by DELETE.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uh... why do you have hard-coded conditionals in your trigger? Wouldn't it
make more sense to put these values somewhere instead of hard-coding values?
Why are you using the inserted table (it is not populated by a DELETE)?
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||so, is this code useable?
>--Original Message--
>CalTab (anonymous@.discussions.microsoft.com) writes:
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>>= 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA
JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>I took the liberty to reformat your trigger body, to
make it more
>readable:
>1) IF NOT EXISTS (SELECT * FROM Inserted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>2) IF EXISTS (SELECT * FROM Deleted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>3) IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED CA
> JOIN Inserted I
> on CA.CLSRECORD_ACCESSEDID =I.ACTIVITYID)
> BEGIN
>A) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, REATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE
(), I.CONTACTID,
> I.ACCOUNTID, 'Activity Change'
> FROM Inserted I
> END
> ELSE IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED
CA
> JOIN Deleted D
> on
CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> BEGIN
>B) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, CREATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE
(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Deleted'
> FROM Deleted D
> END
> END
> END
>
>So if this code is trigger by a DELETE, we will pass the
first IF
>statement, because Inserted is empty in a DELETE
trigger. If the account
>is the right one, we will pass the second IF statement
too. And we
>will always pass the third IF statement, because
Inserted is empty.
>This means that we will execute INSERT statement A, but
since Inserted
>is empty, nothing will happen.
>The test in the ELSE IF statement is never executed, and
neither is
>INSERT statement B, when trigger is fired by DELETE.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/boo
ks.asp
>.
>|||AnonymousWheel (anonymous@.discussions.microsoft.com) writes:
> so, is this code useable?
I don't if you are the person who asked the original question, or someone
else.
Obviously, the trigger code in its current form does not seem to be
extremely useful, since it does not perform the intended task.
But I can't tell how from the mark it is, since I don't know the
business requirements.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Oops - yes, exactly.
"Foo Man Chew" <foo@.man.chew> wrote in message
news:uFsfwZX0DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > columns are "mentioned" in the delete statement.
> Did you mean "not mentioned"?
>|||all we're trying to do is whenever there is an
Insert,update or delete in the activity table, we'd like a
row inserted in clsrecord_accessed to produce an "audit"
of the activity.
>--Original Message--
>AnonymousWheel (anonymous@.discussions.microsoft.com)
writes:
>> so, is this code useable?
>I don't if you are the person who asked the original
question, or someone
>else.
>Obviously, the trigger code in its current form does not
seem to be
>extremely useful, since it does not perform the intended
task.
>But I can't tell how from the mark it is, since I don't
know the
>business requirements.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>

delete text character

hi,

for example ;

select price from table

query results:195dollar

I want to see query result only numeric:195

how can I do?

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

|||

Allen White wrote:

You could try this:

select convert(int, price) as price from table

That would round the value to the nearest integer value.

Don't working :(

Delete records by latest date

Hello,
Can anyone tell me how to delete records from a table
based on dates. For example I have a table of property
sales data. Each property could sell multiple times over
several years. So, the ID # of the property would be the
same each time it sold, but the sale date would be
different. I only want to keep one sale record for each
property ID # and I want it to be the latest sale date.
Here is a sample:
PIN SALEDATE TYPE
6842-564-299 2/12/01 A
6842-564-299 4/17/03 A
6724-312-765 9/5/02 B
6993-411-772 7/21/03 A
The first 2 records above have a duplicate PIN. So I want
to delete the one with the earliest date and keep the
other. Can anyone tell me how to do this in SQL Server
2000. Any help would be greatly appreciatedSo many choices :)
Assuming your table is called YourTable: Also assuming the sales date is a
string (if not you can remove the convert function.
delete YourTable
from YourTable
join
(select PIN, max(SALESDATE) as SALESDATE from YourTable group by PIN having
count(*) > 1) foo
on YourTable.PIN = foo.PIN and convert(smalldatetime,YourTable.SALESDATE) <
convert(smalldatetime,foo.SALESDATE)
Mark
"Shane Sprouse" <shane@.co.greenwood.sc.us> wrote in message
news:0b5301c3aa2f$ddad68e0$a001280a@.phx.gbl...
> Hello,
> Can anyone tell me how to delete records from a table
> based on dates. For example I have a table of property
> sales data. Each property could sell multiple times over
> several years. So, the ID # of the property would be the
> same each time it sold, but the sale date would be
> different. I only want to keep one sale record for each
> property ID # and I want it to be the latest sale date.
> Here is a sample:
> PIN SALEDATE TYPE
> 6842-564-299 2/12/01 A
> 6842-564-299 4/17/03 A
> 6724-312-765 9/5/02 B
> 6993-411-772 7/21/03 A
>
> The first 2 records above have a duplicate PIN. So I want
> to delete the one with the earliest date and keep the
> other. Can anyone tell me how to do this in SQL Server
> 2000. Any help would be greatly appreciated
>

Delete Query Using Join

I need to delete records from one table base on criteria from another table. The example below from the Northwind database shows exactly what I want to do. I want to delete the records from the employee table who a terrorityID of 30346 in the EmployeeTerritories table.

Can someone tell me how to write a delete statement that will delete the rows returned from the following SQL Statement? The sql statement will return one employee name. I would lke to delete that one employee from the employee table and I havent been able to figure out how to do it.

////////// Sql Statment
SELECT dbo.EmployeeTerritories.EmployeeID, dbo.EmployeeTerritories.TerritoryID
FROM dbo.EmployeeTerritories INNER JOIN
dbo.Employees ON dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
WHERE (dbo.EmployeeTerritories.TerritoryID = N'30346')
////////// end of sql statement

Thanks
GEMRemove the select clause and replace with a delete clause. Your delete clause needs to include the name of the table you want to delete from.

Assuming of course you want to use the T-SQL extension rather than SQL standard syntax:
http://msdn2.microsoft.com/en-US/library/aa258847(SQL.80).aspx
As ever, BoL is the best starting point|||DELETE dbo.EmployeeTerritories
FROM dbo.EmployeeTerritories
JOIN dbo.Employees
ON dbo.EmployeeTerritories.EmployeeID = dbo.Employees.EmployeeID
WHERE (dbo.EmployeeTerritories.TerritoryID = N'30346')

--ddave

delete multi records

hi,
I found there are few records have duplicate key in my databaes,
for example, title table, have server same title, how to write a query, can
delete teh duplicate entries but only keep one. how to do this? Thanks in
advance.By what criteria would you want to delete rows from this table? Just because
multiple rows have the same TitleName, that doesn't mean that the entire row
is duplicated. You didn't say what the primary key is. If the table does not
have one, then implement that into your design after cleaning up the data.
The following query will return all rows from the Title table that have a
TitleName that is duplicated in another row. Once done, you can review the
list and identify specific rows to delete.
select
*
from
Titles
where
TitleName in
(
select
TitleName
from
Titles
group by
TitleName
having
count(*) > 1
)
"js" <js@.someone@.hotmail.com> wrote in message
news:Ow%23cX8VOGHA.1028@.TK2MSFTNGP11.phx.gbl...
> hi,
> I found there are few records have duplicate key in my databaes,
> for example, title table, have server same title, how to write a query,
> can delete teh duplicate entries but only keep one. how to do this? Thanks
> in advance.
>