Showing posts with label define. Show all posts
Showing posts with label define. Show all posts

Sunday, March 11, 2012

CLR Stored Procedure Exception Handling

Hi,

1) Is there a way to define a global unhandled exception
handler in managed stored procedures?

Something like we're used to in Winform project:

AppDomain.CurrentDomain.UnhandledException
+= new UnhandledExceptionEventHandler(MyHandler);

(I've tried the latter but get some security exception.)

2) Same question about MS Office CLR unhandled exceptions handler...

3) Also, for WebServices Stored Procedure, is there a way to
customize the SOAP Exception <detail> node?

Thanks
Martin1. This is not possible inside SQL Server. If any exception is not handled, SQL Server catches it and sends an appropriate error message to the user. SQL Server does not allow you to specify your own handler for unhandled exceptions.
2. What is the question here? SQL Server does not install an unhandled exception handler.
3. Through native Web Services in SQL Server 2005, there is no mechanism for the user to control what the SOAP fault contains. The old SQL 2000 ASP.Net Web Services mechanism will continue to allow you to capture the Sql exception in the mid-tier and craft the SOAP fault that goes back to the client.

Thanks,
-Vineet Rao

CLR stored procedure

I got a problem with my CLR C# stored procedure. The proc have just one
param VARCHAR(8000) which I define in C# as String. Works fine until string
exceed 4000 bytes .
Instead of String I tryed SqlChars and SqlString - doesn't work.
In this case I get error "MDX statement was expected. An MDX expression was
specified."
Please help."Tim" <Tim@.discussions.microsoft.com> wrote in message
news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
>I got a problem with my CLR C# stored procedure. The proc have just one
> param VARCHAR(8000) which I define in C# as String. Works fine until
> string
> exceed 4000 bytes .
> Instead of String I tryed SqlChars and SqlString - doesn't work.
> In this case I get error "MDX statement was expected. An MDX expression
> was
> specified."
> Please help.
How about VARCHAR(MAX)?
David|||Try it too - same result.
Thanks.
"David Browne" wrote:

> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
> How about VARCHAR(MAX)?
> David
>
>|||This shouldn't be possible, if you tried to create your stored proc with
parameter varchar(8000) it would fail with the following error:
CREATE PROCEDURE for "MyStoredProc" failed because T-SQL and CLR types for
parameter "@.myparameter" do not match.
String parameters to CLR stored procs need to be nvarchar based because CLR
strings are all unicode, so to support strings larger than 4000 characters,
you'll need to use nvarchar(max).
Steven
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
>I got a problem with my CLR C# stored procedure. The proc have just one
> param VARCHAR(8000) which I define in C# as String. Works fine until
> string
> exceed 4000 bytes .
> Instead of String I tryed SqlChars and SqlString - doesn't work.
> In this case I get error "MDX statement was expected. An MDX expression
> was
> specified."
> Please help.

CLR programming: best place to define utilities functions

Hallo,

I find the CLR integration very handy and I would like to use it in the proper way but I need some helps because is not easy to find "best practice" programming info.

This is what I have to do: from a "period" coming from the DB I have to compute start/End date and save them in the DB.

The computation involves regular expression and several functions that makes several calculations on date type variable.

I have created my UserDefinedFunction class that contains the TV function to return a row with 2 fields (startdate,enddate).
Now my question is: what is the base place to define my utilities functions that perform the computation?
In the same class or it is better to add a new class?

For example I have created a module that contains all the Regular expressions patterns.

Any help is appreciated!

Thankx

Marina B.

I think that this is no matter of a SSQL Server decision rather than a cidong decision. It is always good to have a namespace schema and separated classes which allow you to reuse your your. I would split the regular expressions in you case out of the datetime classes if you want to use them in other projects as well. If this is an internal regular expression calculation you should use them in the same class (and make them evtl. internal if you don′t want to call them directly from SQL Server).

Jens K. Suessmeyer.

http://www.sqlserver2005.de