Setting return status from CLR procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Setting return status from CLR procedure

Setting return status from CLR procedure

Rate Topic

Display Mode

Topic Options

Author

Message

mcginn

Posted Thursday, July 16, 2009 6:35 PM

Valued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316

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

Post #754570

Elliott Whitlow

Posted Thursday, July 16, 2009 9:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #754585

mcginn

Posted Friday, July 17, 2009 10:12 AM

Valued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316

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.

Post #754974

Elliott Whitlow

Posted Sunday, July 19, 2009 9:03 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #755455

mcginn

Posted Monday, July 20, 2009 10:21 AM

Valued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316

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.

Post #755975

« Prev Topic | Next Topic »

Permissions