SQL SERVER sp_OACreate Throws an error "ODSOLE Extended Procedure" after executing for 128 rows

Question

I need to extract matching texts from a input based on a regular expression. As there are no built in support for regular expressions in SQL Server, I have written the following UDF to get the job done.

CREATE FUNCTION GetMatch
(
    @Pattern VARCHAR(255),
    @Text VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @Response INT, @ObjRegexExp INT, @ObjMatch INT, @Matchcount INT, @Match VARCHAR(1000);
    DECLARE @source VARCHAR(500), @description VARCHAR(500);

    EXEC @Response = sp_OACreate 'VBScript.RegExp', @ObjRegexExp OUT;

    IF @Response = 0
        EXEC @Response = sp_OASetProperty @ObjRegexExp, 'Pattern', @Pattern;
    IF @Response = 0
        EXEC @Response = sp_OASetProperty @ObjRegexExp, 'IgnoreCase', 1;
    IF @Response = 0
        EXEC @Response = sp_OASetProperty @ObjRegexExp, 'MultiLine', 0;
    IF @Response = 0
        EXEC @Response = sp_OASetProperty @ObjRegexExp, 'Global', 0;                        
    IF @Response = 0
        EXEC @Response = sp_OAMethod @ObjRegexExp, 'execute', @ObjMatch OUT, @Text;
    IF @Response = 0
        EXEC @Response = sp_OAGetProperty @ObjMatch, 'count', @Matchcount OUT;

    IF @Response = 0 AND @Matchcount >= 1
        EXEC @Response = sp_OAGetProperty @ObjMatch, 'item(0).Value', @Match OUT;

    IF @Response <> 0
    BEGIN
        EXEC @Response = sp_OAGetErrorInfo @ObjRegexExp, @source OUT, @description OUT;
        SET @Match = 'source ' + ISNULL(@source, '') + ' -- description ' + ISNULL(@description, '');
    END

    RETURN @Match;
END
GO

I consume it as follows:-

SELECT Barcode, dbo.GetMatch('(^[a-zA-Z]+)', Barcode) BarcodePrefix
FROM TestData

Each time it gets executed successfully for 128 times and then generates the error ODSOLE Extended Procedure which I have captured by sp_OAGetErrorInfo. Any idea what causes this?

It's 2018. Why are you using SQL Server's clumsy automation procedures to do this versus using the modern CLR approach? — Damien_The_Unbeliever, Jul 12 '18 at 09:40
Unfortunately I cannot use CLR due to other restrictions. Otherwise defensively It would have been my first choice. — Chamika Goonetilaka, Jul 12 '18 at 09:43
I don't know if it would fix the issue (seems like, unfortunately, we're not seeing the error *description*, just the source, which doesn't tell us much), but just for cleanliness I'd recommend adding an `sp_OADestroy` to clean up the created objects. — Damien_The_Unbeliever, Jul 12 '18 at 09:58
@Damien_The_Unbeliever Thanks mate. I tried adding sp_OADestroy as you said. It improves the results a bit. Now I can go upto 255 rows. but after that it's the same story again. FYI description is NULL. — Chamika Goonetilaka, Jul 12 '18 at 10:05
Try passing `NULL` rather than `@ObjRegexExp` as the first parameter to `sp_OAGetErrorInfo` and see if that makes it more forthcoming. — Damien_The_Unbeliever, Jul 12 '18 at 10:13

SQL SERVER sp_OACreate Throws an error "ODSOLE Extended Procedure" after executing for 128 rows

0 Answers0