January 27, 2016 at 12:11 pm
Hello -
Was working with a rather simple update statement, and ran across a behavior that I am not 100% certain as to the origins of. I ran the following on 2 different server instances. One is SQL 2012 Enterprise Edition (x64) - (SQL Server 11.0.5058), and the other is SQL 2008 R2 Standard Edition (x64) - (SQL Server 10.50.2550). The version of SSMS being used is SQL 2012 (SSMS 11.0.2100.60), and here is the statement...
while @@ROWCOUNT > 0
BEGIN
UPDATE Database2.dbo.HTT_INVENTORY_SIM
SET ICCID_BATCH_ID = 'VST22620'
WHERE ICCID_BATCH_ID = 'VST22626'
AND ICCID_NUMBER in
(
select top 5000 ICCID_NUMBER from Database1.dbo.test_HTT_INVENTORY_SIM b
where ICCID_NUMBER in
(select ICCID_NUMBER FROM Database2.dbo.HTT_INVENTORY_SIM (nolock) WHERE ICCID_BATCH_ID = 'VST22626')
)
END
When I run the above code against my 2012 server, it ran as I expected it to - in batches of 5,000 records (so as to avoid locking things up in once mass swoop). The total records being updated is only 25,000 but the system cannot be locked for any great length of time (when this was needing to be performed), hence the reason for batching it this way.
However - when it runs against the 2008 R2 system (which is the PROD system in this scenario), it says command completed successfully, but no records were updated. I walked through each of the sub queries, but could find no problems or issues. I then ran it manually 5 times, and it worked fine.
Anyone seen this behavior before? Is it possibly just a setting in SSMS that I am not aware of that behaves differently in my copy between the to instance versions?
If you have the ability to set up a test with the logic above, and could share your results and insights - sure would appreciate it.
Thank you
January 27, 2016 at 1:47 pm
There is nothing before the @@ROWCOUNT check in the WHILE loop, so clearly one connection does SOMETHING on the spid that gets you an @@ROWCOUNT return other than 0.
If you are going to do it this way, I would explicitly FORCE the start of the WHERE to work with this:
SELECT top 1 * from sys.objects
WHILE @@ROWCOUNT > 0
BEGIN
...
END
Oh, I also note that you do not have any explicit BEGIN TRAN/CHECK FOR ERROR/COMMIT OR ROLLBACK in your loop. That is a must!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2016 at 3:14 pm
thank you so much! I will test this out, and yes - I normally treat all my DML as Transactions, but here in my new position - they do a full copy of the table to a test table (so as to avoid any mistakes). I will incorporate both just for safer keeping.
Thanks again!
January 27, 2016 at 4:05 pm
Oh, another thing I do when batching bulk stuff like this is to drop in a WAITFOR DELAY '00:00:0.x' or something appropriate to give the server some breathing room and let other processes have a crack at potentially locked resources. The explicit tran stuff becomes important for this to work.
I also often check the tlog size and kick off backups as required to keep from filling it up if I have any sizable activity to handle.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2016 at 9:10 am
Tried posting how I might approach writing this update, but couldn't do it directly from work. When I got home last night, didn't even get on my computer so didn't post it then either.
Attached is a file with the code I wrote yesterday. It is not syntax checked, nor is it tested (no database/schema/data) so no promises out of the box. If some one would like to post the code in an IFcode block for all to see, thanks.
Please note that the CATCH block is not fleshed out.
Edit: Added rollback to the CATCH block. I don't know how I missed that when I first wrote this code snippet.
January 28, 2016 at 10:30 am
Lynn Pettis (1/28/2016)
Tried posting how I might approach writing this update, but couldn't do it directly from work. When I got home last night, didn't even get on my computer so didn't post it then either.Attached is a file with the code I wrote yesterday. It is not syntax checked, nor is it tested (no database/schema/data) so no promises out of the box. If some one would like to post the code in an IFcode block for all to see, thanks.
Please note that the CATCH block is not fleshed out.
Here is the code (unchanged from attachment).
declare @Batch int = 5000;
while @Batch > 0
begin
begin tran;
begin try;
update top (@Batch) his2 set
ICCID_BATCH_ID = 'VST22620'
from
Database2.dbo.HTT_INVENTORY_SIM his2
inner join Database1.dbo.test_HTT_INVENTORY_SIM his1
on his2.ICCID_NUMBER = his1.ICCID_NUMBER
where
his2.ICCID_BATCH_ID = 'VST22626';
set @Batch = @@rowcount;
commit;
waitfor delay '00:00:00.050';
end try
begin catch;
print 'Error occurred';
-- error code goes here
end catch;
end
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 28, 2016 at 11:48 am
Thanks, Phil.
January 28, 2016 at 11:52 am
Hey Phil, can you add a rollback in the CATCH block? I just noticed that I forgot that in the code I uploaded.
I fixed the code I had uploaded in the earlier post.
January 28, 2016 at 12:01 pm
Lynn Pettis (1/28/2016)
Hey Phil, can you add a rollback in the CATCH block? I just noticed that I forgot that in the code I uploaded.I fixed the code I had uploaded in the earlier post.
Sure. Here's a modified version:
declare @Batch int = 5000;
while @Batch > 0
begin
begin tran;
begin try;
update
top (@Batch) his2
set ICCID_BATCH_ID = 'VST22620'
from Database2.dbo.HTT_INVENTORY_SIM his2
inner join Database1.dbo.test_HTT_INVENTORY_SIM his1 on his2.ICCID_NUMBER = his1.ICCID_NUMBER
where his2.ICCID_BATCH_ID = 'VST22626';
set @Batch = @@rowcount;
commit;
waitfor delay '00:00:00.050';
end try
begin catch;
print 'Error occurred';
if @@TRANCOUNT > 0
rollback transaction;
end catch;
end
Alternatively, a SET XACT_ABORT ON at the start would do the same job.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 28, 2016 at 12:06 pm
I like your modification better than mine. I left out the check of @@TRANCOUNT in the code I reposted in my attachment. Definitely a good idea to have but I'm not going to make that change. Hopefully people finding this thread will read the whole thread.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply