using CLR assembly

Question

using CLR assembly

evaleah

Ten Centuries

Points: 1372
More actions
October 29, 2008 at 1:01 pm

#195473

This is the first time I have had a call to use SQL CLR and somehow I can't seem to find the syntax to actually USE my new shiny assemblies in SQL 2005.
Can someone send me a good, basic link or just post the correct syntax? This is an assembly that requires a parameter.
Thanks!

Viewing 15 posts - 1 through 15 (of 18 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Can you describe what you have done so far so that we can better answer your question? Have you deployed the Assembly to SQL Server from VS 2005 using the deploy command? If not, have you compiled it to a DLL? What kinds of objects are in the Assembly? Does the Assembly do any External_Access or Unsafe method calls?

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

Better than that, tell us what the CLR is supposed to do so we can try to show you how to do it without a CLR... there's usually a way... 😉

--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)

evaleah Ten Centuries Points: 1372 More actions · Answer 3

Sorry about the missing information.

I deployed it to SQL 2005 and it appears in the Assembly area. The routine creates an iCalendar formatted email message and then sends it. I need to feed it the ID Key of the Email record in my EmailMessage table.

It is written as a Stored Procedure calling a handful of classes. Everything is marked SAFE.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

If you look under the stored procedures, you should see a stored procedure with the name you used to define it inside the C# or VB.NET code. From SQL you just EXECUTE it like you would a TSQL Stored Procedure, and provide the parameters just like you would for a TSQL Stored Procedure.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

evaleah (10/29/2008)
Sorry about the missing information.
I deployed it to SQL 2005 and it appears in the Assembly area. The routine creates an iCalendar formatted email message and then sends it. I need to feed it the ID Key of the Email record in my EmailMessage table.
It is written as a Stored Procedure calling a handful of classes. Everything is marked SAFE.

Gosh... sounds like a little "push" and you'd have a full blown app. Why not write it as an app?

--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)

evaleah Ten Centuries Points: 1372 More actions · Answer 6

First, thanks for the pointer to the stored procedures area. I actually found it by accident looking at a different sproc and it in fact feels just like all the others. What magic.

As far as building an app that does this, that is one option. I have thought about doing it that way and then calling the exe from the command line at a scheduled time. The issue there is we run a lot of automated emails from SQL already and I wanted to manage all of that in one place. Mostly for admin purposes a person would only have to look in one area for all the tools that do the same thing. Feels more manageable.

Thanks again for all the responses.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Jeff Moden (10/29/2008)
Gosh... sounds like a little "push" and you'd have a full blown app. Why not write it as an app?

Jeff,

As resistent as you are to CLR inside SQL, it is actually becoming more and more popular, even among MVP's for things just like this. I believe that PASS Summit will have 2 sessions on CLR implementations in SQL Server. The topic is gaining more traction with time.

EDIT:

I should note that I don't mean the above negatively towards your sentiment. Utility purposes similar to this one, seem to be better received in the community than others.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 8

Jonathan Kehayias (10/30/2008)
As resistent as you are to CLR inside SQL, it is actually becoming more and more popular, even among MVP's for things just like this.

Heh... even though a gazillion people do something wrong, it's still wrong. 😉

--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)

jgrubb SSCrazy Points: 2542 More actions · Answer 9

Jeff Moden (10/30/2008)
Jonathan Kehayias (10/30/2008)
As resistent as you are to CLR inside SQL, it is actually becoming more and more popular, even among MVP's for things just like this.
Heh... even though a gazillion people do something wrong, it's still wrong. 😉

Yah, but if a thousand people jump off a cliff, you won't be fired for being the 1001st

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

jgrubb (10/31/2008)
Jeff Moden (10/30/2008)
Jonathan Kehayias (10/30/2008)
As resistent as you are to CLR inside SQL, it is actually becoming more and more popular, even among MVP's for things just like this.
Heh... even though a gazillion people do something wrong, it's still wrong. 😉
Yah, but if a thousand people jump off a cliff, you won't be fired for being the 1001st

Maybe...but you'll be free-falling from the top of a cliff....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 11

I'm not sure it's a cliff jumping, and our OP feels her thread was a little hijacked.

Please debate this elsewhere. Although it's a good debate, it appears the question was answered in this thread.

jgrubb SSCrazy Points: 2542 More actions · Answer 12

Steve Jones - Editor (11/2/2008)
I'm not sure it's a cliff jumping, and our OP feels her thread was a little hijacked.
Please debate this elsewhere. Although it's a good debate, it appears the question was answered in this thread.

A valid point. Unfortunately, It's hard to get a good, civil, debate going with an title like "CLR functions in SQL, Insane or Brilliant?" 😀

Most good people don't want to discuss the merits of a particular except in reference to a real world problem.

We should be very sure to first, make sure the question is answered, second, educate without condescension, and third sprinkle some informed, non-ad-hominen discussion of the merits of a particular direction.

J-440512 SSCertifiable Points: 6285 More actions · Answer 13

Never used CLR yet. This was supposed to be the greatest thing since sliced bread.

Jeff, would you please enlighten me as to why CLR is viewed as a bad idea ?

Regards

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Evaleah -

Is your stored proc working the way you're expecting it to? Are you still having an issue? Jonathan mentioned some good advice, but it does assume that you ran BOTH and CREATE ASSEMBLY and a CREATE PROC statement (which is the manual way to deploy a CLR procedure to a server).

The "easy" way to do that is to simply ask Visual studio to deploy the procedure for you, but that was less than obvious in the documentation (took me a while to figure that one out).

Anyway - could you let us know how you're doing with it? If you're still having trouble - specific descriptions of what is happening should help find out what it going on.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?