Setting return status from CLR procedure

  • I have a CLR C# DLL. It contains a try/catch loop. I am using the SqlContext.Pipe method to pipe the error messages back to the client. The messages are trapped and returned, but the status of the procedure always returns a 0. What can be done such that the CLR procedure will return a status of 1 or non-zero in case of error?

    Kevin McGinn

  • Instead of defining it as a sub define it as a function with a return value of sqlint (I think thats how I did it)..

    I did it in VB though..

    CEWII

  • Thankyou for the suggestion. I have only written CLR procedures in the past. I was of the impression that a CLR function would not allow file system I/O which is the fundamental purpose of the CLR. If I am incorrect and a Function CLR does in fact allow file system I/O then I will convert over to a Function type.

  • If it is added to SQL as a SAFE SQLCLR then it won't be able to, but if it is EXTERNAL_ACCESS or UNSAFE then it can.

    CEWII

  • The Function solution does work; thank you. However, the stored procedure approach, when implemented correctly, also will return the correct status. I realized that I had the CLR procedure defined as a 'public static void'. I changed that to 'public static int' and returned 1 or 0 depending upon whether errors were encountered and the status is properly returned.

Viewing 5 posts - 1 through 4 (of 4 total)

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