I am try to raise a error to sql server from a clr procedure. I  tried

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("raiserrro("foo", 16, 1)));
try { p.ExecuteAndSend(cmd); } catch { }

which casuse a message to be printed in query analizer but it doesn't seem
to get caught by a begin try / end try ... begin catch / end catch block in
transact sql.

what is going on here?

Show quote Hide quote Well, that code obviously won't even compile.  So please explain what you
are trying to do, and ilustrate it with a real code sample.

david

raiserrro ???

Is that some 'new' keyword?

Show quoteHide quote

Hello Pma_Shane,

Right, because raising an error from a SQLCLR is also generates and exception,
which you eat with the empty catch. Remove the C# try/catch and see if the
T-SQL try/catch works.

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

If you dont have a catch in the clr code you will get an SqlException which
apears on the transact sql side as a general error 6522 which wraps the clr
exception text (ussualy truncating it), which appears after the error raised
by raiserror. I did a bit of gogling on this and found several references
which suggested catching the SqlException. Check out this thread for example

http://forums.microsoft.com/msdn/showpost.aspx?postid=9813&siteid=1

If want a 6522 error I can just use throw in my clr procedure. There are 2
problems with this. 1 you get an ugly gerneric error message. 2. You clr
procedure does not exit gracefully. TSQL doesn't work this way, if a
procedure uses raiserror it doesn't stop running. 

What I want to do is raise a custom error from clr code without generating a
6522 error which can be caught by a begin/end catch block.  So for example,
is possible to wtie a clr procedure which does the same as this TSQL
procedure?

create procedure pFoo
as
begin
     raiserror('foo', 16, 1)
end











Show quoteHide quote

Why don't you use the return value?

Isn't this the common order of error management actions:

1) prevent;
2) if can't be prevented handle inside the procedure;
3) if can't be handled propagate to the client.

It seems as if you're simply trying to propagate the error message straight
to the client. But then again - perhaps you should describe in more detail
what it is that you're actually trying to do.


ML

---
http://milambda.blogspot.com/

yes I am trying to propage the error to the cleint. I could use a return code
but then I loose the advatages of structured exception handling - I have to
remember to check the return code on each call to the procedure an so forth.
The try/catch block was added to TSQL to support structured exception
handling so I am trying to use it.
If it turns out that if what I want to do can't be done then I will have to
do it another way. I think that what I want to do seems perfectly reasonable.
I just want to riase an error from a clr procedure without creating and
unhandled clr exception and whcih gets wrapped as 6522 errror. When I am
calling a procudure from TSQL I shouldn't need to care whether the procedure
was implemeted in TSQL or managed code - I should be able to use the same
error handling semantics in either case.

Show quoteHide quote

Iterating through SQL Server data
Require One of 3 Columns NOT NULL
hierarchical query 2005
problem with select
Detect Changes Using Checksum