Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I hav
e
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
Chris
I have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.
|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing SQL Aent Jobs in SQL2005

Hi,
I have a situation where I had created a maintenance plan and edited the
resulting job. I had to delete the maintenance plan and job. In SQL2000 I
would delete the job first then the maintenance plan. I tried this is
SQL2005 and had errors. It referred to the DELETE statement conflicted with
REFERENCE constraint "FK_subplan_job_id". The conflict occurred in msdb
table dbo.sysmaintplan_subplans", column 'job_id'.
I could not delete the maintenance plan either. In the end I renamed the job
then I could delete the maintenance plan.
This is on SQL2005 SP1.
Thanks
ChrisI have same problem. Did you ever get a reply? If so, what do I do? I have
several orphan jobs I cannot delete. Thanks for any info.|||DaveK,
I mentioned in my post how I fixed this. Nobody else replied.
Chris
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:79B9F73B-C3E3-47AB-8CCB-3AEA8EC02062@.microsoft.com...
>I have same problem. Did you ever get a reply? If so, what do I do? I
>have
> several orphan jobs I cannot delete. Thanks for any info.

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.

deleteing small amount of records from a view causes IX lock on all the base tables.

Sorry that I had to post it as new message instead of replying since I got server application error.
Kalen, this is a different issue. I wonder why other 4 base tables got IX TAB lock as well since the partitioned view is supposed to look up the relevant tables only by querying on the constraint column.Tom
Can you please include relevant portions of the original message, so I can
know what I am replying to without having to search the archives?
If this is a question about partitioned views, did you supply the view
definition, and the version you are using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:29346002-6963-4D4E-B63C-C6A5C5E292CD@.microsoft.com...
> Sorry that I had to post it as new message instead of replying since I got
server application error.
> Kalen, this is a different issue. I wonder why other 4 base tables got IX
TAB lock as well since the partitioned view is supposed to look up the
relevant tables only by querying on the constraint column.

Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> takes
was
> (i.e.,
not,
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
can
reference
> was
> not,
>

Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > I have to delete 30K rows that were inserted into the orders table by
> > mistake.
> >
> > The orderid field is used as a FK on many other tables so the delete
> takes
> > forever.
> >
> > Is there any way to increase the performance of this delete?
> >
> > In this case I know there will not be any associated FKs that reference
> > these orderids in other tables because the insert I am seeking to undo
was
> > made only to the orders table. Therefore no orphans will be produced
> (i.e.,
> > no RI violation) upon delete.
> >
> > Is it possible to perform the delete without constraint checking? If
not,
> > is there anything I can do to speed up the process?
> >
> > Thanks
> >
> >
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> > Do you have indexes on the FK columns in the referencing tables? That
can
> > speed up such an operation significantly.
> >
> > You can disable the FK constraint (see ALTER TABLE), but that disabling
> > applies for all connections, so make sure you are alone on the database
> > while doing so, if you want to take that route.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "David F" <davef@.nksj.ru> wrote in message
> > news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > > I have to delete 30K rows that were inserted into the orders table by
> > > mistake.
> > >
> > > The orderid field is used as a FK on many other tables so the delete
> > takes
> > > forever.
> > >
> > > Is there any way to increase the performance of this delete?
> > >
> > > In this case I know there will not be any associated FKs that
reference
> > > these orderids in other tables because the insert I am seeking to undo
> was
> > > made only to the orders table. Therefore no orphans will be produced
> > (i.e.,
> > > no RI violation) upon delete.
> > >
> > > Is it possible to perform the delete without constraint checking? If
> not,
> > > is there anything I can do to speed up the process?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

Deleteing large bulks of data

Brief background:

We are using SQL Server 2000, and one of the tables stores user
sessions details (each time our users logs into our system we insert a
new record in the session table, and each time user logs out from our
system we insert another record in the same table).
SESSION_ID is the primary key and it is clustered index.
The system produces 5 million session records/day.

The problem:

Each day we transfer the session data (delta only) to other machine and
we want to delete bulk of ~5 million sessions. This should happend
without any interfering of our customers activity ( in the same time,
we should not block the table - new sessions should be created).

What is the best way to perform such task ?generally truncate table xxx ...will be MUCH faster than delete. Be
aware of some of the logging issues associated with truncate table
before you do this. Search your BOL for "truncate table".

MJKulangara
http://sqladventures.blogspot.com|||Truncating table will delete the entire table, and this is not what we
want. We are looing for a method to delete specific sessions (by
specifying the exact sessions ID's).|||rosherman@.hotmail.com (rosherman@.hotmail.com) writes:
> We are using SQL Server 2000, and one of the tables stores user
> sessions details (each time our users logs into our system we insert a
> new record in the session table, and each time user logs out from our
> system we insert another record in the same table).
> SESSION_ID is the primary key and it is clustered index.
> The system produces 5 million session records/day.
> The problem:
> Each day we transfer the session data (delta only) to other machine and
> we want to delete bulk of ~5 million sessions. This should happend
> without any interfering of our customers activity ( in the same time,
> we should not block the table - new sessions should be created).
> What is the best way to perform such task ?

If I understand this correctly, you want to delete the main bulk of the
five million rows, but keep some of them.

I would consider doing something like:

1) Rename the table.
2) Create a new table with the same schema.
3) Insert the rows you want to keep from the old table to the new table.
4) Drop the old table.

You would need to put 1) and 2) into a transaction. During this
transactions logins would be blocked, but it would be a matter of
centiseconds.

If you can find a method to define a clean cut a head, then you could
consider partitioned views. That is, you would have a set of table
that are united in a view, and a CHECK constraint defining which
intervals that go into which table. Insertions would be into the view.
You would transfer one table a time, and then truncate and finally
redefine it to fit another slot in the future.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. We'll try this method.

Deleteing Duplicates

Hi,
Does anyone have a useful way of deleting duplicates so that it leaves 1 in
the table and removes the other. Currently I use a #temp table but was just
wondering if there is something slicker.
Thanks
Steve LloydDepends, assuming you have a primary key:
DELETE FROM YourTable
WHERE EXISTS
(SELECT *
FROM YourTable AS T
WHERE col1 = YourTable.col1
AND col2 = YourTable.col2
AND ... etc
AND key_col < YourTable.key_col)
If you don't have a key at all then SELECT DISTINCT into a new table
and add the key... and don't create tables without keys in future!
David Portas
SQL Server MVP
--|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
How to Identify and Delete Duplicate SQL Server Records
http://www.sql-server-performance.c..._duplicates.asp
AMB
"Steve Lloyd" wrote:

> Hi,
> Does anyone have a useful way of deleting duplicates so that it leaves 1 i
n
> the table and removes the other. Currently I use a #temp table but was ju
st
> wondering if there is something slicker.
> Thanks
> Steve Lloyd
>
>

Deleteing duplicate records from my table

I just discovered that all my records appear twice inside my table, in
other words, they repeat on the row below. How can I delete all of the
duplicates? I'm sure there must be a tidy line of sql to do that.

Thanks,

Billbillzimmerman@.gospellight.com (Bill) wrote in message news:<8da5f4f4.0307310856.79830a8e@.posting.google.com>...
> I just discovered that all my records appear twice inside my table, in
> other words, they repeat on the row below. How can I delete all of the
> duplicates? I'm sure there must be a tidy line of sql to do that.
> Thanks,
> Bill

Maybe run a select distinct query and insert the results into a new
table? If need be, you could then delete all records from your
original table and insert the records back.

That should be reasonably tidy but it is hard to say what the
performance would look like without knowing the specifics.

deleteing duplicate records ?

Hi,
This is my table structure

Name Age

Siva 24
Siva 24
Raghu 25

In this above table siva 24 row is inserted twice . how to delete duplicate record .

If you use SQL Server 2005 the following query will help you..

WITH MYTABLE as (select *, Row_Number() over (order by Name,age) RowId from names)
Delete from MyTable Where Rowid Not in(Select Min(rowId) from MyTable Group By Name,Age);

If you use SQL Server 2000 you should have minimum one unique column (id) to remove the duplicates.

|||

One way (that works with both SQL 2000 and SQL 2005) is to create a new table, add the non-duplicate data to the new table, then drop the old table and rename the new table to the same name as the old table.

Here is a demonstration:

Code Snippet

SET NOCOUNT ON

CREATE TABLE MyTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Raghu', 25 )

SELECT *
FROM MyTable

CREATE TABLE MyNewTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyNewTable
SELECT
[Name],
[Age]
FROM MyTable
GROUP BY
[Name],
[Age]
SELECT *
FROM MyNewTable
DROP TABLE MyTable
GO

EXECUTE sp_rename 'MyNewTable', 'MyTable'

SELECT *
FROM MyTable

|||

In this case we can simply use,

Code Snippet

Select Distinct Name, Age Into MyNewTable From MyTable;

Truncate Table MyTable;

Insert Into MyTable Select * From MyNewTable;

Drop Table MyNewTable;

deleteing columns from a saved fixed width file connection object

How do I delete columns in a fixed width column file connection object, after I've saved it I can't remove columns anymore?

You'll need to reset your columns. See this thread for details ... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=434333&SiteID=1

Donald

Deleteing a RO Warm Standby

I restored a a dB and for what ever resaon, it is a warm
standby that is replicated. I am attempting to delete
the dB via EP, but...
I cannot delete it because it is being used for
replication.
I cannot remove replication because it is read-only.
I cannot remove the read-only flag because it is a warm
stand by.
HELP!!!
How do I go about getting rid of this dB?
Larry...
Larry,
before altering the database in any way you'll need to recover it. Please
try this script:
restore database xxx with recovery
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
HTH,
Paul Ibison
|||Thanks, this did the trick!!!