[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

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
0
EXTRHMAN
Asked:
EXTRHMAN
  • 5
  • 4
1 Solution
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now