Post reply

xp_create_subdir error handing

  • October 8, 2014 at 12:00 pm

    #297196

    Hey folks.

    I've been knocking my head against this for a bit and I think I'm just going to write a CLR procedure in place of the master..xp_create_subdir or call it via sqlcmd and scrap the output results for the error (UGH) ... That being said, I'd prefer not to reinvent the wheel if it doesn't need to occur.

    Anyways so I'm trying to capture the error (when on occurs) when the master..xp_create_subdir proc is called. Understandability I can capture the status return code of 0 or 1 however the when the procedure is called and a error occurs the error is bubbled up to the session however the try/catch does not capture this nor does the catch stop the error from begin returned

    example error that is returned to the session however is NOT capture in the try/catch:

    Msg 22048, Level 16, State 1, Line 0

    xp_create_subdir() returned error 3, 'The system cannot find the path specified.'

    here is a sample code set to test

    begin try

    declare @ret int , @errmsg varchar(max)

    exec @ret= master..xp_create_subdir 'Z:\test' -- "Z:" is just a volume that does not exists (basically something to raise an error)

    end try

    begin catch

    SET @errmsg = 'Msg ' +

    cast(ERROR_NUMBER() as varchar(20)) + ', Level ' +

    cast(ERROR_SEVERITY() as varchar(20)) + ', State ' +

    cast(ERROR_STATE() as varchar(20)) + ', Line ' +

    cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' +

    isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'')

    end catch

    print @errmsg -- no data

    print @ret -- expected return code of 1 for error found

    Any suggestions would be greatly appreciated

    Thanks

    -Eric

  • October 11, 2014 at 12:47 am

    #1751781

    Note that xp_create_subdir is undocumented so you may want to avoid it anyway.

    I recently implemented these SQLCLR objects from CodePlex with some modifications to suit my environment, and they work quite well. There is one for creating a directory that would replace xp_create_subdir:

    FileSystemHelper SQL Server CLR[/url]

    I am not a fan (despise actually) of accessing the file system from within a T-SQL context but I was retrofitting a legacy application and could not completely eliminate file system access due to project constraints. I was however able to get to the point where I could disable xp_cmdshell, a win nonetheless.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply