asked on

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

Anthony Perkins

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.

EXTRHMAN

ASKER

acperkins,

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

Thanks
Mike

Anthony Perkins

Provided the EnteredDate is indexed, find an acceptable range of dates and run the SQL Update statement in batches.

EXTRHMAN

ASKER

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

Anthony Perkins

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EXTRHMAN

ASKER

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.

EXTRHMAN

ASKER

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

EXTRHMAN

ASKER

Thanks so much for the help. I never thought to use a stored procedure for this.