How do I throw custom errors from SQLCLR code back to calling T-SQL?

Question

How do I throw custom errors from SQLCLR code back to calling T-SQL?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

I don't know why folks say that CLR's perform better and are more scalable... it's absolutely not true in about 99% of the cases and several of us have beat the pants off of CLR's just using T-SQL on many different types of problems. Dunno about this particular application of CLR's, but I kicked the snot out of a great deal of CLR's using T-SQL.

The cases where we have found that a CLR will sometimes beat T-SQL is RegEx replace and that's about it. I suppose there are some complex math calculations where they may perform better, but I haven't actually seen one, yet.

The problem with sp_OA* memory leaks has supposed been repaired way back in 2k... like anything else, you do have to remember to close the connections or you can get "connection leaks" which some folks misinterpret as memory leaks because they also take some memory. Of course, no one who's ever written VB or C or C# has ever forgotten to do such a thing, huh? 😉

The real key here should be... why are you trying to build a file handler in T-SQL to begin with... the app should do this all. :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

Lynn Pettis (7/7/2008)
Jeff,
Just one little problem with your code above. The closing paren for the CREATE TABLE #FileDetails is included in your comment. It took me a bit to figure out that problem, kept getting an error near the INSERT statment following it.
😎

Thanks for the catch, Lynn ... I was trying to get rid of smiley faces and I flubbed that line. I've repaired the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

Jeff Moden (7/7/2008)
I don't know why folks say that CLR's perform better and are more scalable... it's absolutely not true in about 99% of the cases and several of us have beat the pants off of CLR's just using T-SQL on many different types of problems. Dunno about this particular application of CLR's, but I kicked the snot out of a great deal of CLR's using T-SQL.
The cases where we have found that a CLR will sometimes beat T-SQL is RegEx replace and that's about it. I suppose there are some complex math calculations where they may perform better, but I haven't actually seen one, yet.
The problem with sp_OA* memory leaks has supposed been repaired way back in 2k... like anything else, you do have to remember to close the connections or you can get "connection leaks" which some folks misinterpret as memory leaks because they also take some memory. Of course, no one who's ever written VB or C or C# has ever forgotten to do such a thing, huh? 😉
The real key here should be... why are you trying to build a file handler in T-SQL to begin with... the app should do this all. :w00t:

I'm sorry, I can't get into this discussion again, I've got too much to do and life is too short...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

If you're not going to finish it, you shouldn't start it. 😉 What do you want to race on... again? 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)