rows deletion affected by cursor

Hello,

I am using a cursor to navigate on data...of a table...
inside the while @.@.fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)Perhaps the fetch into @.var is already executed, changes to the resultset do not affect values in variables? If not, could you post your code?|||The deleted is executed before the fetch next statement.

My code is:

Set @.items = Cursor
For
Select T.patentrynr,t.patcode,t.patname,cast(t.groupid As Nvarchar(10)),g.groupname,
(select Sum(idaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid )as Idaxia,
(select Sum(tamaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Tamaxia,
(select Sum(insuraxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Insuraxia,
Itemnum,indvrate,indvamount,tamrate,
Tamamount,insurrate,insuramount,maxqty,tamname,gro upnum,t.groupid
From @.trans T Inner Join @.rates R
On R.groupid = T.groupid And R.groupid Is Not Null
And T.itemid!=cast(t.groupid As Nvarchar(10))
Inner Join Groups G On G.groupid = T.groupid
Order By T.patentrynr,t.groupid

Open @.items

Fetch From @.items Into
@.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,@.i daxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,
@.tamamount,@.insurrate,@.insuramount,@.maxqty,@.tamnam e,@.groupnum,@.groupid

While @.@.fetch_status = 0
Begin
If @.indvamount Is Not Null And @.groupnum Is Not Null And
@.idaxia Is Not Null And @.idaxia!=0
Begin
If @.groupnum > @.maxqty And @.maxqty Is Not Null
Begin
Set @.indvposo = @.maxqty*@.indvamount
End
Else
Begin
Set @.indvposo = @.groupnum*@.indvamount
End
End
Else If @.indvrate Is Not Null And @.idaxia Is Not Null
Begin
Set @.indvposo = @.idaxia*(@.indvrate/100)
End


Insert Into @.result (patentrynr,patcode,patname,tamname,itemid,itemnam e,indvtziros,
Tamtziros,insurtziros,indvpososto,tampososto,insur pososto,parakrat)
Values(@.patentrynr,@.patcode,@.patname,@.tamname,@.ite mid,@.itemname,@.idaxia,@.tamaxia,@.insuraxia,
@.indvposo,@.tamposo,@.insurposo,@.parakrat)


Set @.idaxia=null
Set @.tamaxia =null
Set @.insuraxia=null
Set @.itemnum=null
Set @.indvrate=null
Set @.indvamount=null
Set @.tamrate=null
Set @.tamamount=null
Set @.insurrate=null
Set @.insuramount=null
Set @.maxqty=null
Set @.indvposo=null
Set @.tamposo=null
Set @.insurposo=null

Delete From @.trans Where Patentrynr = @.patentrynr
And Groupid = @.groupid


Fetch Next From @.items
Into @.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,
@.idaxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,@.tamamount,
@.insurrate,@.insuramount,@.maxqty,@.tamname,@.groupnum ,@.groupid
End --end While|||I don't see a reason why rows would not be deleted from the variable table. Are you saying no rows at all are deleted from the table? What are the values of @.groupid and @.patentrynr prior the delete (what rows match)?

I've setup an example that basically does the same, perhaps it gives you an idea.

use monkey
go

set nocount on

declare @.varTab table ( myInt integer, myValue varchar(3))

insert into @.varTab (myInt, myValue) values (1, 'aaa')
insert into @.varTab (myInt, myValue) values (2, 'aaa')
insert into @.varTab (myInt, myValue) values (3, 'aaa')
insert into @.varTab (myInt, myValue) values (4, 'aaa')

declare @.myint integer
declare @.myvalue varchar(3)
declare cur_tab1 cursor DYNAMIC
for select myInt, myvalue from @.varTab

declare @.mtef cursor
set @.mtef = cur_tab1 -- ?

open @.mtef
fetch next from @.mtef into @.myint, @.myvalue
while @.@.fetch_status = 0
begin
-- update tab1 set myValue = 'bbb' where myInt = @.myInt + 1
delete from @.varTab where myInt = @.myInt
select 'myInt: ', @.myint, @.myvalue

fetch next from @.mtef into @.myint, @.myvalue
end

select * from @.varTab

deallocate @.mtef
go|||I have executed your example an it works...fine
but when i added a select statement in my code
before the fetch next statement to find what the table hoes
I found that the values are deleted...
I have also added a select statement after the fetch next...
to find out the values that will be next processed
and they are the next values found in the table before the deletion...

Do I have to change anything in the cursor declaration?|||I'm not sure what you're saying. So the rows are deleted from @.trans? What do you mean with "Do I have to change anything in the cursor declaration?" (assuming the delete works)?|||The delete works but...
I want the fetch next to fetch the next row in the table after the deletion.
This does not work.
It cursor fetches the next row in the table as it was before the deletion.
It seems that the data in the cursor is static...and it is not affected by the deletion.|||so it's like...

...
for select myInt, myvalue
from @.varTab
order by myInt -- order by clause
...
delete from @.varTab where myInt = @.myInt + 1 -- First run: delete myInt = 2
...
deallocate @.mtef
deallocate cur_tab1

The output includes all four rows...

Does your cursor declaration include an 'ORDER BY'-clause?
If it does, your cursor is converted into a KEYSET-cursor (see BOL on this).
What I know from keyset cursors is from BOL, so I'm a bit guessing here but I think the deletes are not visible because it's not the cursor doing the deletes.

Rows Affected By Delete

Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!
Brian@.@.ROWCOUNT stores the number of rows affected by the last statement. It is continually changing, so you may need to store the value in another variable immediately after your statement is executed.

RowCount is returning null

i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.

SP Code

ALTER

PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]

@.assignmentPrimaryKey

int,

@.rowCount

intOUTPUT

AS

BEGIN

SETNOCOUNTON;SELECT employeePrimaryKeyFROM assignmentTableWHERE primaryKey= @.assignmentPrimaryKey;SET @.rowCount=@.@.RowCount;

END

c# code

SqlConnection

conn = GetOpenSqlConnection();if (conn ==null) returntrue;SqlDataReader reader =null;SqlParameter p1 =newSqlParameter(); SqlParameter p2 =newSqlParameter();try{SqlCommand command =newSqlCommand();

command.CommandText =

"RetrieveEmployeeKeyFromAssignmentTable";

command.CommandType =

CommandType.StoredProcedure;

command.Connection = conn;

p1.ParameterName =

"@.assignmentPrimaryKey";

p1.Value = assignmentPrimaryKey;

p2.ParameterName =

"@.rowCount";

p2.Direction =

ParameterDirection.Output;

p2.Value = 0;

command.Parameters.Add(p1); command.Parameters.Add(p2);

reader = command.ExecuteReader();

if (p2.Value ==null)//always true

any suggestions would be appreciated.

thanks. matt

also, reader.HasRows is true.

matt

|||

Hello,

first, I was suspicious of your code that you assigned the value of output parameter p2.value=0.

but it was O.K., and I was wrong , I tested with query analyzer.

I looked carefully in your code, find that you called with ExecuteReader ==> it is connected data object.

you have to close the Reader object before you try to get Output or else paremeter values.

verified with internet search...

|||

EXCELLENT.

thank you very much.

matt

Row wise operation in MSSQL Server

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!Yes, you could put a cursor in the trigger that operates on each row of the INSERTED table. But do yourself a favor and don't do it. Find a set-based solution using the INSERTED table instead.

Please describe what you are trying to do and why you think you need a row-wise operator (cursor). These are generally only needed by dynamic sql procedures or processes where the results of the operation on one record affects the results of the operation on the next record.|||AND...I'll add...anything that would require a cursor should not be done in a trigger...sql

Row will not update or delete using Database Explorer (VWD Express)

Hi

I'm trying to update a database table through Database explorer and occassionally when I try to change an entry it will not update or delete. I therefore have a corrupt record which I can't get rid off and therefore can't use the table. The error report is as follows:

"...A problem occurred attempting to delete row 29

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)

Correct the errors and attempt to delete the row again or press ESC to cancel the change(s)..."

All columns allow Nulls and there is no primary key or foreign key relationships with other tables.

Can anyone suggest what I am doing wrong or will I just have to resort to data entry using an ASP.Net control such as Detailsview.

Cheers

Chris

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)

In SQL Server, there MUST be a way to uniquely identify a row for a DELETE or UPDATE action. IF the row(s) you are attempting to DELETE are duplicate to other rows, you will not be allowed to delete them.

You options are to create either an IDENTITY field in the table, or a PRIMARY KEY -both of which will uniquely identify the row -allowing deletes to occur.

|||

Arnie

Thank you for your quick response and for your clear explanation as to the problem. I can now delete the rows.

Many thanks

Chris

routine to archive records

Howdy,
I'm working on a routine to archive records. It is
the basic copy a massive amount of records from table
a to table b and then delete the records copied from
table a. I plan to do this using a trigger for delete
on table a. Neither table has a clustered index tho
they both have some non clustered indexes.
Please look at the outline and let me know if this is
a good plan. Is there anything that I might do to
improve? I know that the trigger will affect the
delete query performance ... but I'm not sure how best
to make sure that the deleted records are copied to
the archive database.
First I'm going to create a following trigger on
the source table Active_DB.dbo.Important_Records
CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
FOR DELETE
AS
INSERT INTO Archive_DB.dbo.Important_Records
(...columns...)
SELECT ...columns...
FROM deleted
The actual delete routine will be something like:
USE Archive_DB
ALTER DATABASE Archive_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
USE Active_DB
ALTER DATABASE Active_DB
SET RECOVERY SIMPLE
--DISABLE TRANSACTION LOG BACKUP JOB
DECLARE @.DayCount int
SET @.DayCount = 576 --(for example)
DECLARE @.RowsDeleted int
SET @.RowsDeleted = -1
SET ROWCOUNT 1000
WHILE @.RowsDeleted <> 0
BEGIN
DELETE
FROM dbo.Important_Records
WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
dbo.Important_Records WHERE < (getdate() - @.DayCount))
SET @.RowsDeleted = @.@.ROWCOUNT
END
END
SET ROWCOUNT 0
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Active_DB
SET RECOVERY BULK_LOGGED
USE Archive_DB
--sp_updatestats @.resample = 'resample'
CHECKPOINT
--ENABLE TRANSACTION LOG BACKUP JOB
ALTER DATABASE Archive_DB
SET RECOVERY BULK_LOGGED
--Full Backup of Active_DB
--Full Backup of Archive_DBA faster way would be:
1. bcp the data out to a flat file
2. delete the data in batches (10000 rows each would be fine)
3. bulk insert the data in in batches
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...
> Howdy,
> I'm working on a routine to archive records. It is
> the basic copy a massive amount of records from table
> a to table b and then delete the records copied from
> table a. I plan to do this using a trigger for delete
> on table a. Neither table has a clustered index tho
> they both have some non clustered indexes.
> Please look at the outline and let me know if this is
> a good plan. Is there anything that I might do to
> improve? I know that the trigger will affect the
> delete query performance ... but I'm not sure how best
> to make sure that the deleted records are copied to
> the archive database.
> First I'm going to create a following trigger on
> the source table Active_DB.dbo.Important_Records
> CREATE TRIGGER ArchiveRecord ON [dbo].[Important_Records]
> FOR DELETE
> AS
> INSERT INTO Archive_DB.dbo.Important_Records
> (...columns...)
> SELECT ...columns...
> FROM deleted
> The actual delete routine will be something like:
> USE Archive_DB
> ALTER DATABASE Archive_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> USE Active_DB
> ALTER DATABASE Active_DB
> SET RECOVERY SIMPLE
> --DISABLE TRANSACTION LOG BACKUP JOB
> DECLARE @.DayCount int
> SET @.DayCount = 576 --(for example)
> DECLARE @.RowsDeleted int
> SET @.RowsDeleted = -1
> SET ROWCOUNT 1000
> WHILE @.RowsDeleted <> 0
> BEGIN
> DELETE
> FROM dbo.Important_Records
> WHERE EXISTS (SELECT OCCURRED WITH (NOLOCK) FROM
> dbo.Important_Records WHERE < (getdate() - @.DayCount))
> SET @.RowsDeleted = @.@.ROWCOUNT
> END
> END
> SET ROWCOUNT 0
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Active_DB
> SET RECOVERY BULK_LOGGED
> USE Archive_DB
> --sp_updatestats @.resample = 'resample'
> CHECKPOINT
> --ENABLE TRANSACTION LOG BACKUP JOB
> ALTER DATABASE Archive_DB
> SET RECOVERY BULK_LOGGED
> --Full Backup of Active_DB
> --Full Backup of Archive_DB
>|||oj, thanks for the advice.
I haven't used bcp. This morning I experimented with the northwind db
to get a feel for bcp. I would like to know how can I ensure that the
records I bcp from the dbo.Important_Records are the very same records
that I delete from dbo.Important_Records? That is the concern that
leads me to want to use a trigger for delete.
oj wrote:
> A faster way would be:
> 1. bcp the data out to a flat file
> 2. delete the data in batches (10000 rows each would be fine)
> 3. bulk insert the data in in batches
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125450831.524829.115380@.z14g2000cwz.googlegroups.com...|||bcp can take a query. That would be the way to extract only the desired
rows.
e.g.
bcp "select * from Northwind..Orders where OrderID<10250" queryout
"c:\Orders.txt" -T -w
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125500222.595819.253710@.z14g2000cwz.googlegroups.com...
> oj, thanks for the advice.
> I haven't used bcp. This morning I experimented with the northwind db
> to get a feel for bcp. I would like to know how can I ensure that the
> records I bcp from the dbo.Important_Records are the very same records
> that I delete from dbo.Important_Records? That is the concern that
> leads me to want to use a trigger for delete.
> oj wrote:
>|||Thanks again for the input.
I have the bcp part working ... it copies data to text files and then
uploads to the destination beautifully.
But I'm having a performance issue on the delete query ( appended below
). The select statement runs very quickly and uses a clustered index
s. But when the delete action is held up by sorts on three non
clustered indexes. The sorts account for 76% of the query cost.
I'm wondering if it would make sense to drop the non clustered indexes
before running the delete. The table itself has 28 million records so
dropping and rebuilding the non clustered indexes might be quite a
chore in itself. My other thought was to modify the non clustered
indexes to include the clustered index ...
Query:
CREATE PROCEDURE dbo.DeleteImportant_Record
@.DayCount int
AS
SET NOCOUNT ON
SET ROWCOUNT 1000
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
(NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
SET NOCOUNT OFF
GO
oj wrote:
> bcp can take a query. That would be the way to extract only the desired
> rows.
> e.g.
> bcp "select * from Northwind..Orders where OrderID<10250" queryout
> "c:\Orders.txt" -T -w|||There is really no need for the subquery. Try:
declare @.dt datetime
set @.dt=(getdate() - @.DayCount)
WHILE 1=1
BEGIN
DELETE
FROM dbo.Important_Record
WHERE RecordDate < @.dt
IF @.@.ROWCOUNT = 0
BREAK
END
-oj
<NTuser_Man@.msn.com> wrote in message
news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...
> Thanks again for the input.
> I have the bcp part working ... it copies data to text files and then
> uploads to the destination beautifully.
> But I'm having a performance issue on the delete query ( appended below
> ). The select statement runs very quickly and uses a clustered index
> s. But when the delete action is held up by sorts on three non
> clustered indexes. The sorts account for 76% of the query cost.
> I'm wondering if it would make sense to drop the non clustered indexes
> before running the delete. The table itself has 28 million records so
> dropping and rebuilding the non clustered indexes might be quite a
> chore in itself. My other thought was to modify the non clustered
> indexes to include the clustered index ...
>
> Query:
> CREATE PROCEDURE dbo.DeleteImportant_Record
> @.DayCount int
> AS
> SET NOCOUNT ON
> SET ROWCOUNT 1000
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE EXISTS (SELECT OCCURRED FROM dbo.Important_Record WITH
> (NOLOCK) WHERE RecordDate < (getdate() - @.DayCount))
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
> SET NOCOUNT OFF
>
> GO
>
> oj wrote:
>|||Hey, thanks for all the help. Now the query deletes over 10,000
records/minute. Now I have only 27188456 recods to go.
oj wrote:
> There is really no need for the subquery. Try:
> declare @.dt datetime
> set @.dt=(getdate() - @.DayCount)
> WHILE 1=1
> BEGIN
> DELETE
> FROM dbo.Important_Record
> WHERE RecordDate < @.dt
> IF @.@.ROWCOUNT = 0
> BREAK
> END
>
>
> --
> -oj
>
> <NTuser_Man@.msn.com> wrote in message
> news:1125524729.117694.268840@.g49g2000cwa.googlegroups.com...