March 12, 2012 at 8:23 am
I am having an issue when updating a couple thousand row table. Ever time I perform a typical update on a table with 2 to 3 thousand rows it will only update 128 rows at a time. I am scratching my head to figure out what setting I need to check. Any help is appreciated.
Here is the statement:
Update Backuptest
Set Filesize = dbo.SVF_ReturnFileSize(foldername)/1024
Where Filesize IS NULL
Here is the code for the function:
create FUNCTION [dbo].[SVF_ReturnFileSize]
(
-- Add the parameters for the function here
@filename varchar(1000)
)
RETURNS BIGint
AS
BEGIN
-- Declare the return variable here
DECLARE @filesize int
-- Add the T-SQL statements to compute the return value here
DECLARE @OLEResult INT
DECLARE @FileID INT
DECLARE @FS INT
DECLARE @Size BIGINT
-- Create an instance of the file system object
EXEC @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, @filename
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @Size OUT
-- Return the result of the function
RETURN @Size
END
March 12, 2012 at 8:38 am
Do you perform a stand alone update statement or there is something else in this script/batch (which includes SET ROWCOUNT 128)?
Any triggers on a table?
May be there are only 128 rows "Where Filesize IS NULL"?
March 12, 2012 at 8:48 am
select count(*) from backuptest where filesize is null --1820
I have added no "SET ROWCOUNT" statements and still it only updates 128 rows at a time. Is there a higher database configuration?
March 12, 2012 at 8:55 am
Try to use sp_OADestroy in your function,
Also, just in case, execute:
SET ROWCOUNT 0
before update
March 12, 2012 at 9:26 am
Got it! Thanks for the help.
As soon as I destroyed the objects I can now update all the rows in the table. I added the following:
EXEC @OLEResult = sp_OADestroy @FileID
EXEC @OLEResult = sp_OADestroy @FS
to the end of the function and its all good now. Thanks for the help. 😀
One more thing in order to convert it to MB I should "x/1024" and for GB "x/1024/1024" correct?
March 12, 2012 at 9:33 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply