October 31, 2006 at 11:23 pm
I have noticed two problems with my SQL 2000 database recently in regards to updates.
There are two tables...one is called Customers and the other is Address.
In the Address table are two fields: locationcontact and locationphone. For years, that information has been filled with information. Now they want the VB application that accesses this database to move that information to a temporary field in the order and to clear out the locationcontact and locationphone values in the database when that is done. What the application does is sends an SQL string as follows:
Update Address Set LocationContact = '', LocationPhone = '' Where ID = <some number here>
I've run through the code and can see that it is actually executing the line of code that updates the locationcontact and locationphone, but the database doesn't update.
I then have this cursor that takes some values from a history table, sums them up and then updates a value in the customers table. If I run it for 15,000 qualifying customers, roughly 1,000 will update while the rest do not update. Even in query analyzer, the updates don't happen.
Where do I look to solve this problem?
Mike
November 1, 2006 at 1:35 am
Is any error been thrown? Does the user executing it have update rights to the table? Are there any triggers on the table that might be rolling back your changes?
Can you post the code here, especially for the history cursor (which probably doesn't need to be a cursor) and we can take a look.
Also, make sure that the app is connecting to the database that you think it is. I've seen that before here, where the app is updating one database and the person is looking at another.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2006 at 10:18 am
The user definitely has permissions because the total number of eligible records prior to running the cursor was 15,261 and after it was 14,441, which means it updated 820 records.
As for an update statement that doesn't work, something as simple as:
Update Address Set LocationContact = '' Where ID = 42587
is not working.
We have run CHECKDB and have even dropped and recreated the indexes on the table in question and get no change in results. Here is the update cursor:
declare @customerid int, @pointsearned int, @pointsredeemed int
declare rewards_totals_update_cursor cursor for
select id from customers where isnull(member, 0) = -1
open rewards_totals_update_cursor
fetch next from rewards_totals_update_cursor
into @customerid
while @@fetch_status = 0
begin
select @pointsearned = sum(RewardPointsEarned) from RewardHistoryCustomerLink Where CustomerID = @customerid and IsNull(Pending,-1) = 0
select @pointsredeemed = sum(points) from RewardsRedeemed Where CustomerID = @customerid
update customers set RewardPointsLifetime = @Pointsearned, RewardPointsRedeemed = @pointsredeemed where id = @customerid
fetch next from rewards_totals_update_cursor
into @customerid
end
close rewards_totals_update_cursor
deallocate rewards_totals_update_cursor
print '---Update completed---'
Any help would be appreciated
Mike
November 1, 2006 at 10:55 pm
Is there any trigger on the Address table? Are any errors been raised?
Are you using a cursor for the Address update as well? If so, I may have an idea what the problem is. If it is a cursor, please post the entire cursor code.
That rewards cursor is unnecessary. Here's 2 updates version that should do the same thing. I could do it in a single update, but this should surfice. It'll most definatly be faster than your cursor-based approach.
UPDATE
customers set RewardPointsLifetime = TotalPointsEarned
FROM (SELECT sum(RewardPointsEarned) AS TotalPointsEarned, CustomerID from RewardHistoryCustomerLink WHERE Pending=0) CustomerRewards
WHERE CustomerRewards.CustomerID = customers.CustomerID AND Customers.member = -1
UPDATE
customers SET RewardPointsRedeemed = TotalPointsRedeemed
FROM (SELECT sum(Points) AS TotalPointsRedeemed, CustomerID from RewardsRedeemed) CustomerRewards
WHERE CustomerRewards.CustomerID = customers.CustomerID AND Customers.member = -1
The isnulls are unnecessary, since NULL != 0 and NULL != -1
You'd need an ISNULL if you were doing something like ISNULL(member,0)=0
See if that works. If not, there's something realy wierd going on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply