Field not being updated within Stored Procedure

I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @.lastscandate would ever be
NULL. If it is null it should be equal to @.maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @.lastscandate. This should then active the next if
statement and set the @.lastscandate equal to the @.maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.

Thanks for your help.

DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

OPEN c1
FETCH FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

WHILE @.@.fetch_status = 0
BEGIN

--Get MailDate from Manifest - if NULL then use GetDate
set @.maildate = NULL
SELECT @.maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @.acctno AND ordid = @.ordid AND cycle = @.cycle
if @.maildate is NULL
set @.maildate = getdate()

--Get Last Scan Date from Transactions - if NULL then use MailDate
set @.lastscandate = NULL
select @.lastscandate=max(actiondate) from transactions where
acctno=@.acctno and ordid=@.ordid and cycle=@.cycle and actionid=303
if @.lastscandate is NULL
set @.lastscandate = @.maildate

BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @.maildate,
lastscandate=@.lastscandate
WHERE id = @.mid

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @.acctno, @.ordid,
@.cycle, 'Update Mail Dates')
COMMIT TRANSACTION

FETCH NEXT FROM c1 INTO @.mid, @.acctno, @.ordid, @.cycle

END

CLOSE c1[posted and mailed, please reply in news]

AS400 Guru (hazen@.candid.com) writes:
> I have this stored procedure that loops through a table and updates a
> couple of fields. For some reason one of the fields is not being
> updated. If I run the same code from query analyzer, it works fine.
> Let me know if anyone can figure out why @.lastscandate would ever be
> NULL. If it is null it should be equal to @.maildate. The senerio that
> seems to fail is when no records are returned from the select statement
> to fill in @.lastscandate. This should then active the next if
> statement and set the @.lastscandate equal to the @.maildate. MailDate
> is always filled in in the database and LastScanDate will be NULL.

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
SELECT m.id, m.acctno, m.ordid, m.cycle
FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML')
AND (m.batchid IS NULL OR m.batchid >= 0)
AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno
AND m.ordid = p.ordid
AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Fetch Query that triggered the TRIGGER

Hello

The problem is need to find out the querry that has updated or inserted
into the table and in turn 'Triggered the Trigger'. I have the user
name, the machine name, Application name, but not the query. The update
is not desired and the application is doing it but the application
being so large we are unable to pin-point the code which is doing the
dammage.

Pls help!

Regards
AnubhavAnubhav (anbansal@.gmail.com) writes:

Quote:

Originally Posted by

The problem is need to find out the querry that has updated or inserted
into the table and in turn 'Triggered the Trigger'. I have the user
name, the machine name, Application name, but not the query. The update
is not desired and the application is doing it but the application
being so large we are unable to pin-point the code which is doing the
dammage.

If the UPDATE is coming directly from a client, you can use

dbcc inputbuffer(@.@.spid) with tableresults

but if the error is in a stored procedure, you will not see the UPDATE
statement, only the call submitted by the client.

--
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

Fastest way of updating a row

In relation to my last post, I have a question for the SQL-gurus.

I need to update 70k records, and mark all those updated in a special
column for further processing by another system.

So, if the record was

Key1, foo, foo, ""

it needs to become

Key1, fap, fap, "U"

iff and only iff the datavalues are actually different (as above, foo
becomes fap),

otherwise it must become

Key1, foo,foo, ""

Is it quicker to :
1) get the row of the destination table, inspect all values
programatically, and determine IF an update query is needed

OR

2) just do a update on all rows, but adding
and (field1 <> value1 or field2<>value2) to the update query

that is
update myTable
set
field1 = "foo"
markField="u"
where key="mykey" and (field1 <> foo)

The first one will not generate new update queries if the record has
not changed, on account of doing a select, whereas the second version
always runs an update, but some of them will not affect any lines.

Will I need a full index on the second version?

Thanks in advance,
Asger Henriksen[posted and mailed, vnligen svara i nys]

Asger Jensen (akj@.tmnet.dk) writes:
> Is it quicker to :
> 1) get the row of the destination table, inspect all values
> programatically, and determine IF an update query is needed
> OR
> 2) just do a update on all rows, but adding
> and (field1 <> value1 or field2<>value2) to the update query
> that is
> update myTable
> set
> field1 = "foo"
> markField="u"
> where key="mykey" and (field1 <> foo)

I'm not sure that I follow, but it sounds to me that the in first
approach you would retrieve rows one by one.

In any case, the second approach leaves all the jub to the computer,
and there is a reason why we have computers, isn't there? :-)

The only catch is that with too many rows in the table there can be
a strain on the transaction log. But with only 70000 rows, this is
not worth worrying about.

Obviously there query will run faster if there is a clustered index
on the column "key".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I'm not sure that I follow, but it sounds to me that the in first
> approach you would retrieve rows one by one.

Yes, thats what it was. Nasty.
> In any case, the second approach leaves all the jub to the computer,
> and there is a reason why we have computers, isn't there? :-)
:-), yes, only in my scenario it took soooo long.

> The only catch is that with too many rows in the table there can be
> a strain on the transaction log. But with only 70000 rows, this is
> not worth worrying about.

ok,

> Obviously there query will run faster if there is a clustered index
> on the column "key".
There was, actually, but during the 70000 records it became slower and
slower. I solved it by adding a dynamically generated index on ALL
fields, not just "key", this sped things up considerably, so I went
from 1 hour to 10 minutes.

I guess it is due to the server needing to do a lookup/record on all
value fields to see if they have changed, and this can be done more
efficiently with a n index.

Regards
Asger

faster page update using SQL Server data

Hi,

What is the fastest way to get informations from a SQL BD (using stored procedure) in a WEB Page ?

This web page need to be updated EVERY SECONDE !
Javacript / OleDB / SQLConnection / ... ?

I plan to use a a usercontrol containing the informations, am I right ?

thank you for your help,You can get HTML code directly using the Web Tasks functionality in SQL Server ... I think this is the easiest and the fastest way to get web pages directly from SQL Server ...

Refer to SQL Server BOL for further info ...