x

sp_OACreate vbscript.regexp Class not registered

Imagine a Windows Server 2003 Enterprise x64 Edition.

I can create a 'vbscript.regexp' object using a vbscript (confirming that the DLL is registered).

I cannot create a 'vbscript.regexp' object using sp_OACreate in a SQL Server 2005 SP3 (.4053)(cluster) environment.

OLE Automation is checked ON in surface area configuration.

The sp_OACreate was working until very recently.

Can you help me?

CODE

             declare
                 @source varchar(5000),
                 @regexp varchar(1000),
                 @replace varchar(1000),
                 @globalReplace bit,
                 @ignoreCase bit

             set @source = 'NBG76TF43'
             set @regexp = '[^0-9]'
             set @replace = ''
             set @globalReplace = 1
             set @ignoreCase = 1

                 DECLARE @hr integer
                 DECLARE @objRegExp integer
                 DECLARE @result varchar(5000)

                 EXECUTE @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
                 IF @hr <> 0 
                     BEGIN
                         EXEC @hr = sp_OADestroy @objRegExp
                         PRINT  'A'
                         PRINT @hr
                     END
                 EXECUTE @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
                 IF @hr <> 0 
                     BEGIN
                         EXEC @hr = sp_OADestroy @objRegExp
                         PRINT  'B'
                         PRINT @hr
                     END
                 EXECUTE @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
                 IF @hr <> 0 
                     BEGIN
                         EXEC @hr = sp_OADestroy @objRegExp
                         PRINT  'C'
                         PRINT @hr
                     END
                 EXECUTE @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
                 IF @hr <> 0 
                     BEGIN
                         EXEC @hr = sp_OADestroy @objRegExp
                         PRINT  'D'
                         PRINT @hr
                     END

                 EXECUTE @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
                 IF @hr <> 0 
                     BEGIN
                         EXEC @hr = sp_OADestroy @objRegExp
                         PRINT  'E'
                         PRINT @hr
                     END

                 EXECUTE @hr = sp_OADestroy @objRegExp
                     IF @hr <> 0 
                         BEGIN
                             PRINT  'F'
                         PRINT @hr
                         END

                 PRINT  @result

             /*
             A
             -2147211480
             B
             -2147211480
             C
             -2147211480
             D
             -2147211480
             E
             -2147211480
             F
             -2147211480
             */
more ▼

asked Nov 20, 2011 at 01:56 PM in Default

avatar image

echoScout
11 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Is there any environment change like from 32 bit to 64 bit? Normally, This problem occurs because Microsoft does not support creating an OLE object from a 32-bit COM DLL in an x64 edition of SQL Server 2005. To work around this problem, use a 64-bit compiler to recompile the COM DLL. Please let us know if this solves your problem.

more ▼

answered Nov 20, 2011 at 09:03 PM

avatar image

Usman Butt
14k 6 13 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018
x20
x9

asked: Nov 20, 2011 at 01:56 PM

Seen: 1635 times

Last Updated: Nov 20, 2011 at 01:56 PM

Copyright 2016 Redgate Software. Privacy Policy