Using ActiveX Script in Job Step to pause job

I have never used ActiveX scripts in a job step before so I am a little green here.  I am trying to update a table that has over 27 million records in it.  Some how trailing spaces got into some of our product numbers and our applications are not showing everything when someone searches for a specific product.  To solve this I am running an update SQL statement similar to this:

 Update tbl_Products set ProductCode = rtrim(ltrim(ProductCode)) where EnteredDate > '12/31/2006' and EnteredDate < '7/1/2007'

Because of the size of data I decided to update just six months at a time, so that the server wouldn't get locked down during the update.  Also I am actually trimming all varchar fields in this table just to make sure.  The server is utilized 24/7 so I don't have any down time that I could run this.

I created a Job consisting of a step for the first and last six month period of every year down until 1980.  Because I want other requests to the server to still function.  I created a simple ActiveX Script to pause for 30 seconds as a step between each update.  I simply us this command:

wscript.sleep 30000

Which works great in a .vbs file.  But errors out when the Job tries to execute it, with this error:

Executed as user: WS05\SYSTEM. Error Code: 0  Error Source= Microsoft VBScript runtime error  Error Description: Object required: 'wscript'    Error on Line 0.  The step failed.

Any suggestions on any of this would be appreciated.

Thanks
Mike
LVL 1
EXTRHMANAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
That is because it is not supported in DTS. But you don't need DTS for this simple task and it would also explain why it is taking so long.

While I don't agree with your general approach in T-SQL you can use the WAITFOR as an equivalent of the VBScript Sleep.
0
EXTRHMANAuthor Commented:
acperkins,

Could you elabirate on what you believe a plausible solution would be for trimming the data?

Thanks
Mike
0
Anthony PerkinsCommented:
Provided the EnteredDate is indexed, find an acceptable range of dates and run the SQL Update statement in batches.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EXTRHMANAuthor Commented:
acperkins,

I might not have said what I was doing correctly.  That is exactly what I am doing.  I have a job set up to run all of the SQL statements, but was using an activex script to pause between each statement.  And the EnteredDate is indexed.

Thanks
Mike
0
Anthony PerkinsCommented:
Ok. So than do something like this (not tested):

Declare      @BeginDate smalldatetime,
      @EndDate smalldatetime,
      @Interval int,
      @RowCount int


SET NOCOUNT ON

Select      @BeginDate = '2006-12-31',
      @Interval = 180,                  -- Acceptable range of days
      @RowCount = 1

While @RowCount > 0
   Begin
      Set @EndDate = DATEADD(day, @Interval, @BeginDate)

      Update      tbl_Products
      Set      ProductCode = RTRIM(LTRIM(ProductCode))
      where      EnteredDate BETWEEN @BeginDate And @EndDate

      Set @RowCount = @@ROWCOUNT
      If @RowCount > 0
         Begin
            Set @EndDate = DATEADD(day, 1, @BeginDate)
         End

      CHECKPOINT
   END

Print 'Done!'
0
Anthony PerkinsCommented:
Or if you prefer to still add a pause than:

Declare      @BeginDate smalldatetime,
      @EndDate smalldatetime,
      @Interval int,
      @RowCount int


SET NOCOUNT ON

Select      @BeginDate = '2006-12-31',
      @Interval = 180,                  -- Acceptable range of days
      @RowCount = 1

While @RowCount > 0
   Begin
      Set @EndDate = DATEADD(day, @Interval, @BeginDate)

      Update      tbl_Products
      Set      ProductCode = RTRIM(LTRIM(ProductCode))
      where      EnteredDate BETWEEN @BeginDate And @EndDate

      Set @RowCount = @@ROWCOUNT
      If @RowCount > 0
         Begin
            Set @EndDate = DATEADD(day, 1, @BeginDate)
            WAITFOR DELAY 0:00:30
         End

      CHECKPOINT
   END

Print 'Done!'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EXTRHMANAuthor Commented:
Sorry for the delay in this.  I have not been back to the customers yet to try this out.  Looks like I might be there next week.  I will try it first chance I get.

thanks for being patient.
0
EXTRHMANAuthor Commented:
Sorry this took so long.  I got put on another project.  I have just picked this back up and have gitten it to work.

Thanks
Mike
0
EXTRHMANAuthor Commented:
Thanks so much for the help.  I never thought to use a stored procedure for this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.