Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
            */
share|improve this question
add comment

1 Answer 1

up vote 0 down vote accepted

Ten months later, the issue occurred again on the same system.

  • Specific error: 0x8007045A (A dynamic link library (DLL) initialization routine failed)

I cannot find the cause of this issue. However, I found enough related scenarios (ten months ago) that suggested that rebooting the server would solve the problem.

I rebooted the server cluster (both nodes, following controlled failovers), and the issue was resolved (can create a 'vbscript.regexp' object using sp_OACreate).

share|improve this answer
1  
Why don't you just use CLR regular expressions instead of these antiquated automation procedures? –  Martin Smith Sep 29 '12 at 17:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.