Deleting rows

Hi there,
can you help me?
I'm trying to delete rows in a single table.
When i exec select , rows returne are 6098 (right number)
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
When i try to delete that records rows deleted are 13708 (wrong)
DELETE TBL_CHARACTERbackup WHERE FLD_USERID IN
(
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
)
Donno why, please help
TksThe DELETE statement will delete every row in your table which has a
FLD_USERID value that is duplicated, whereas the SELECT will only show
ONE row per FLD_USERID.
Possibly this is what you intended:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level < TBL_CHARACTERbackup.fld_level)
but that's only a guess.
David Portas
SQL Server MVP
--|||
"David Portas" ha scritto:

> The DELETE statement will delete every row in your table which has a
> FLD_USERID value that is duplicated, whereas the SELECT will only show
> ONE row per FLD_USERID.
> Possibly this is what you intended:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level < TBL_CHARACTERbackup.fld_level)
> but that's only a guess.
> --
> David Portas
> SQL Server MVP
Ok what i need is to delete all fld_userid with lower fld_level
just to have
only one records for each fld_userid with highest fld_level
How can i do?
Please help.
> --
>|||You haven't told us what the key of your table is. Assuming
(fld_userid, fld_level) is unique then just turn around the sign in my
first attempt:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level > TBL_CHARACTERbackup.fld_level)
(still untested)
If (fld_userid, fld_level) isn't unique that means there may be more
than one row with the same maximum value of fld_level so you will have
to add some other criteria to the WHERE clause. If you need more help
then the following article explains the best way to describe the
details of your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
Do you have any say over naming conventions in your design? Why the
useless tbl_ and fld_ prefixes? They tell us nothing (we know what
tables and columns are) and just make the names harder to read. Also,
the usual convention is to use the term "column" not "field" when
referring to relational data. Some people find this distinction more
important than others and associate the two terms with totally
different concepts but almost everyone loathes to see prefixes on
column names.
Hope this helps.
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> You haven't told us what the key of your table is. Assuming
> (fld_userid, fld_level) is unique then just turn around the sign in my
> first attempt:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level > TBL_CHARACTERbackup.fld_level)
> (still untested)
> If (fld_userid, fld_level) isn't unique that means there may be more
> than one row with the same maximum value of fld_level so you will have
> to add some other criteria to the WHERE clause. If you need more help
> then the following article explains the best way to describe the
> details of your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Do you have any say over naming conventions in your design? Why the
> useless tbl_ and fld_ prefixes? They tell us nothing (we know what
> tables and columns are) and just make the names harder to read. Also,
> the usual convention is to use the term "column" not "field" when
> referring to relational data. Some people find this distinction more
> important than others and associate the two terms with totally
> different concepts but almost everyone loathes to see prefixes on
> column names.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
I'll explain :
each row_USERID corrisponds more records
ex
FLD_USERID FLD_LEVEL CHARACTERS
daniele 1 PIPPO
daniele 27 PLUTO
daniele 37 Paperino
I must keep in table
FLD_USERID FLD_LEVEL CHARACTERS
daniele 27 PLUTO
daniele 37 Paperino
That means only 2 records for FLD_USERID wiyh 2 higher FLD_LEVEL levels
Please help
> --
>|||Try:
DELETE FROM TBL_CHARACTERbackup
WHERE fld_level =
(SELECT MIN(fld_level)
FROM TBL_CHARACTERbackup AS T
WHERE fld_userid = TBL_CHARACTERbackup.fld_userid)
David Portas
SQL Server MVP
--

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replicat...on_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicat...on_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
--
Thanks
RonFTake a look at this article:
http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:
> Take a look at this article:
> http://www.mssqlserver.com/replication/bp_manual_replication_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> > I previously had a publication set up on my database. I've dropped it but
> > still have a number of system tables in my database of the form
> > conflict_dbnamePub_tablename. Is there a way I can delete these
> > --
> > Thanks
> > RonF
>
>

Deleting Pub Tables

I previously had a publication set up on my database. I've dropped it but
still have a number of system tables in my database of the form
conflict_dbnamePub_tablename. Is there a way I can delete these
Thanks
RonF
Take a look at this article:
http://www.mssqlserver.com/replicati...n_cleanup.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"RonF" <RonF@.discussions.microsoft.com> wrote in message
news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
> I previously had a publication set up on my database. I've dropped it but
> still have a number of system tables in my database of the form
> conflict_dbnamePub_tablename. Is there a way I can delete these
> --
> Thanks
> RonF
|||Thanks for the assistance!
Ron
"Dejan Sarka" wrote:

> Take a look at this article:
> http://www.mssqlserver.com/replicati...n_cleanup.asp.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "RonF" <RonF@.discussions.microsoft.com> wrote in message
> news:E22685FE-F085-4B32-AF26-F2C5814FDD5C@.microsoft.com...
>
>

Deleting min value from grouped records

I have a table where no keys are currently defined, so we have dups...kind
of. In this table the account number with be the primary key, and we also
have a date field. There are records in there that have the same account
number, but a different date. I want to find the duplicates, which is the
easy part. Then from there I want to delete the record that has the oldest
date. Example
record 1
account 1
date 1-1-2004
record 2
account 1
date 5-1-2004
I want to delete record 1. I am having trouble coming up with the code.
Any help is appreciated.
ThanksTry,
delete t1
where exists(select * from t1 as a where a.account_id = t1.account_id and
a.col_date > t1.col_date)
This will not eliminate duplicated rows with same col_date.
AMB
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Delete SomeTable
from SomeTable
INNER JOIN
(
Select MIN([Date]), account from SomeTable
Group by account
) Subquery
on
Subquery.record = SomeTable.Record AND
Subquery.account = SomeTable.account AND
Subquery.[date] = SomeTable.[date]
HTH, Jens SUessmeyer.
"Andy" <Andy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4F5EBDA6-6BAE-44D0-9413-869F150B3640@.microsoft.com...
>I have a table where no keys are currently defined, so we have dups...kind
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the
> oldest
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks|||Are you sure you "want to delete the record with the oldest Date" and that's
all? W
--What if there is only one record?
-- What if there are morethan 2 records?
Most of the time what is desired is t odelete ALL BUT The most recent
record... whichis actually easier..
But...
Delete T
From Table T
Where DateCol =
(Select Min(DateCol) From Table
Where AccountNo = T.AccountNo)
-- Add this if you only want to delete when there are dupes with same
accountNo
And Exists (Select * From Table
Where AcountNo = T.AccountNo
And DateCol > T.DateCol)
This will delete all reco
"Andy" wrote:

> I have a table where no keys are currently defined, so we have dups...kin
d
> of. In this table the account number with be the primary key, and we also
> have a date field. There are records in there that have the same account
> number, but a different date. I want to find the duplicates, which is the
> easy part. Then from there I want to delete the record that has the oldes
t
> date. Example
> record 1
> account 1
> date 1-1-2004
> record 2
> account 1
> date 5-1-2004
> I want to delete record 1. I am having trouble coming up with the code.
> Any help is appreciated.
> Thanks

Deleting large number of rows in SQL Server 2000

Hi,

I have some problems with our database which is growing too large, and was hoping someone might have some tips on what I can do!

I have about 100 clients, each logging about 10 000 rows of status logs a day. So after just a few days the db is growing very large.

At present it's manageable, since I don't need to "dig" into the logs more than a few times a day. The system it self is not affected by the size of the log or traffic on the server. But it will increase to about 500 clients in 2004, and 1000-1500 in 2005. So I really need a smarter solution than what I have today to be able to use the log efficiently.

98-99% of these rows are status-messages which are more or less garbage during normal operation. But I still need to keep them in case an error occurs, and we need to go back an hour or two (maybe a day) to see what went wrong. After 24-48 hours these 98-99% are of no use. I do however like to keep the remaining 1-2%, they are messages like startup, errors, etc. Ideally they should be logged in two separate tables by the clients, but unfortunatelly I cannot make the clients change their logging.

This presents problems on multiple levels. Mainly in searching, which often times out, but also with backup and storagespace. At the moment I check the system for errors, and every other day I just truncate the log-file. It works, but it's not exacly elegant.....

The server is a 1100 MHz P3 / 512MB / Windows 2000 Server /
SQL Server 2000. Faster hardware would help, but the problem is more of a "bad design" than "slow hardware" problem.

My log is pretty simple, as follows:

LogId - int - primary key - clustered index
ClientId - int - index asc
LogTypeId - int - index asc
LogValue - nvarchar[2500], ikke index
LogTimeStamp- datetime - index asc

I have deducted 3 different solutions:

Method 1:
Simply run "Delete from db_log where logtyipeid <> stuff_I_want_to_keep".

This is the simplest and the one i prefer, but it takes too long time to complete. Any tips to speed this process up?

Method 2:
Create a trigger which runs something like "Delete from db_log where logtypeid <> stuff_I_want_to_keep and date < today_minus_two_days" every hour or so. This will ensure that the db doesn't grow to large. But if I'm away from work a few days we might loose data we'd wanted to keep.

Method 3:

Copy what I want to keep into another table, and empty the log. Sort of like "Insert into db_log_keep stuff_to_keep; drop db_log; create table db_log; " (or truncate, but that takes a long time too)

But then I would be stuck with two log tables, "48-hour_db_log" and "db_log_keep". I could use a view to "union" them so they would appear as a single table, but that's not ideal either.

However, it seems as this method is what will work best for my set-up, unless there are other suggestions??

Method 4:

...eagerly awaiting ideas!!! :-)

(Also, whatever tips and/or links to info on maintaing VLDB's are greatly appreciated. )

Thanks in advance for your help! :-)

NikolaiI would personally use method 3, and then rename the table back to original name after the original table had been dropped.

I find this site is quite useful

http://www.sql-server-performance.com/|||Method 3 is what I would do as well. Just cause I like to be overprotective. I would move data you don't need into another table, keep it there for a day, and then empty it out at night. Sort of like an archive table.|||Is there any redundancy in:

LogTypeId - int - index asc
LogValue - nvarchar[2500], ikke index ?

What I mean is, are the messages free form or are the same messages and types being sent repeatedly? If there's good redundancy, particularly in the pair values (TypeID AND LogValue), these could be normalized out and the log reduced to holding just the client id and the time stamp.|||(Sorry about that "ikke", means no index... :-) )

From the last post, I tried turning of the indexing (sp_autostats) and then on again after deleting, but that didn't help either... deleting such large amounts just takes too much time I guess.

Indeed, pairing logvalue and logtype might reduce the log size, I'll try it and see what improvements I'll get. The problem however is that as I mentioned, I cannot change the way the client logs, so I have to add some sort of trigger or something to get around it.

Of the 98% "garbage", Logvalue would be typically about 20 different values that are repeated often. This number will increase to approx. 150 or so by 2005.

Overly simplified for demonstration purposes, Logtype and logvalue looks something like this:

2010, "stringvalue1"
2010, "stringvalue2"
2010, "stringvalue3"
2010, "stringvalue4"
2020, "some_status_message_different_each_time"
2010, "stringvalue1"
2010, "stringvalue2"
2010, "stringvalue3"
2010, "stringvalue4"

2010 is the most used value, and it's paired with stringvalue1 through 20 or so. So by exchanging 2010, "stringvalue1" with LogEntryValueId1, and 2010, "stringvalue2" with LogEntryValueId2 or something like that would really reduce my database.

I didn't actually think about renaming the table back to the original, but that's a great idea! I'll try that if that pairing thing has to be postponed to the next version.

Thank so very much for for all your help guys!!! :-)

Deleting duplicates

I have a table with 5 columns.
Column 1 is the ID and is unique
Column 2 is a number and has many duplicates
Column 3-5 are just desicrptions
It holds 60,000 products but many man of them are duplicates, the way I know
is that they have the same code in column # 2
How can I select only non-duplicates ?
Column 1: ID
Column 2: SKU
Column3: Description
Column 4: Price
Column5: Quantity
I need to select all columns, that's why I could not : select distinct SKU
from table, because it would only select one column, how can
I select all columns where sku is unique ?
ASELECT id, sku, description, price, quantity
FROM YourTable AS T
WHERE id =
(SELECT MIN(id)
FROM YourTable
WHERE sku = T.sku)
David Portas
SQL Server MVP
--|||Excelent !
Thanks David
A
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112881987.619604.173390@.z14g2000cwz.googlegroups.com...
> SELECT id, sku, description, price, quantity
> FROM YourTable AS T
> WHERE id =
> (SELECT MIN(id)
> FROM YourTable
> WHERE sku = T.sku)
> --
> David Portas
> SQL Server MVP
> --
>sql

deleting backups older than X days?

Hello,
I've written a number of scripts for custom maintenance and backups on our
server, the last problem I have is I don't know how to delete backup files o
lder
than x amount of days. Can anyone tell me how to do this?
Thanks,
Craig.Why don't you use database maintenance plans? They do this (delete old
files) for you...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
> Hello,
> I've written a number of scripts for custom maintenance and backups on our
> server, the last problem I have is I don't know how to delete backup files
older
> than x amount of days. Can anyone tell me how to do this?
> Thanks,
> Craig.|||If you have a look at the delete code in this proc it should give you the
right idea
http://www.sql-server-performance.c...sp?TOPIC_ID=864
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
> Hello,
> I've written a number of scripts for custom maintenance and backups on our
> server, the last problem I have is I don't know how to delete backup files
older
> than x amount of days. Can anyone tell me how to do this?
> Thanks,
> Craig.|||Which should cause you to go stright for the Maint. Plan Wizard
Neil MacMurchy
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OwfUuAUBEHA.1236@.TK2MSFTNGP11.phx.gbl...
> If you have a look at the delete code in this proc it should give you the
> right idea
> http://www.sql-server-performance.c...sp?TOPIC_ID=864
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
> news:O7PJ2aTBEHA.2768@.tk2msftngp13.phx.gbl...
our
files
> older
>|||It's not pretty is it, what was I thinking :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Neil MacMurchy" <neilmcse@.hotmail.com> wrote in message
news:%23CzFYMUBEHA.1700@.TK2MSFTNGP12.phx.gbl...
> Which should cause you to go stright for the Maint. Plan Wizard
> Neil MacMurchy
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OwfUuAUBEHA.1236@.TK2MSFTNGP11.phx.gbl...
the
> our
> files
>|||Thanks for that link Jasper... but I think I'll stick with the maintenance p
lan
wizard for backups.
Jasper Smith wrote:

> If you have a look at the delete code in this proc it should give you the
> right idea
> http://www.sql-server-performance.c...sp?TOPIC_ID=864
>|||I KNEW IT! I must be an Oracle (ooooo bad pun....)
;-)
Neil
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:eFrTuvUBEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Thanks for that link Jasper... but I think I'll stick with the maintenance
plan
> wizard for backups.
>
> Jasper Smith wrote:
>
the|||It does look scary but rest assured we have been running this in production
for a long time and its got tens of thousands of backups under its belt. It
is getting to be a bit of a monster procedure though :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:eFrTuvUBEHA.3748@.TK2MSFTNGP11.phx.gbl...
> Thanks for that link Jasper... but I think I'll stick with the maintenance
plan
> wizard for backups.
>
> Jasper Smith wrote:
>
the

Deleting All Subscriptions

I have about a large number of subscriptions for ONE report that I would like
to bulk delete. Could you please advise if I could run an SQL query on the
database to achieve this? Due to the large number of subscriptions, I am
finding it impossible to delete the subscriptions thru the browser (by going
to the "Subscriptions" tab and ticking the "select all" button and clicking
"Delete". My browser keeps timing out). Hence, I am looking for an
alternative to delete from the backend. Please help. Thank you!I have managed to delete the subscriptions from the ReportServer database and
it has not caused any issues whatsoever.sql

delete record does not exist in table

I want to delete records in table1 if account number does not exist in table
2.
I have following code:
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
Should the above code work?
Any informaion is great appreciated,Yes, that should work just fine...
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2)
You can also use Not Exists
DELETE TABLE1 T1
WHERE Not Exists
(SELECT * FROM TABLE2
Where ACCOUNT_NUMBER =
T1.ACCOUNT_NUMBER)
"Souris" wrote:

> I want to delete records in table1 if account number does not exist in tab
le2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||To add to CBretana's response, your NOT IN construct will work as expected
unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
when there are one or more NULL values. Personally, I prefer NOT EXISTS
because that produces the behavior desired in most situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>I want to delete records in table1 if account number does not exist in
>table2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||Dan
I agree , but if you change 'a little bit :-)' his query it should work as
well as NOT EXISTS .
People just forget with NOT IN to add WHERE condition with an outer table.
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2 Where TABLE1.ACCOUNT_NUMBER =
TABLE2 .ACCOUNT_NUMBER)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OlRMglaRFHA.252@.TK2MSFTNGP12.phx.gbl...
> To add to CBretana's response, your NOT IN construct will work as expected
> unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
> when there are one or more NULL values. Personally, I prefer NOT EXISTS
> because that produces the behavior desired in most situations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>

DELETE permission denied problem when using a stored proc to delet

I'm using a stored proc to delete a record in two tables (in two different
databases) and I keep receiving “Error Number: 229 -- Error State: 5 -- Er
ror
Message: DELETE permission denied on object 'ewBehaviour', database
'eWorkSpaceV5', owner 'dbo' ”. The stored proc works for me (as sysadmin
for
the server), but won’t work for any other user. I’ve tried giving a use
r
db_owner access for both the databases but I still receive the error.
Below is the stored proc:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spWEB_Delete_Detention
@.DetentionID as int
AS
SET XACT_ABORT ON
DECLARE @.BehaviourId as int
IF NOT EXISTS
(
SELECT DetentionID
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
)
BEGIN
RAISERROR ('Detention does not exist in DC_Detention ',16,1)
RETURN -1
END
IF NOT EXISTS
(
SELECT Id
FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE ID =
(SELECT Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID)
)
BEGIN
RAISERROR ('Behaviour entry does not exist in ewBehaviour',16,1)
RETURN -1
END
SELECT @.BehaviourId=Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
BEGIN TRANSACTION
print 'Begin Transaction'
print 'Try Delete DC_Detentions'
DELETE FROM DC_Detentions
WHERE (DetentionID = @.DetentionID)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention from DC_Detention',16,1)
print 'Delete from DC_Detention failed'
RETURN -1
END
print 'Try Delete eWorkSpaceV5 ewBehaviour'
DELETE FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE (Id = @.BehaviourId)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention into ewBehaviour',16,1)
print 'Delete from ewBehaviour failed'
RETURN -1
END
COMMIT TRANSACTION
RETURN 0
SET XACT_ABORT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOAs long as you dont activate ownership chain (as I assume that you are
deleting data in a different database) this won=B4t work. Ownerchip
chains is disabled by default since SP3.
Look for cross database ownership chain in BOL or for the thread:
http://groups.google.de/group/micro...ramming/browse=
_frm/thread/4b86a2ccefd974af
HTH, JEns Suessmeyer.