CLR Stored Procedures

Question

CLR Stored Procedures

ennawarner

SSC Rookie

Points: 41
More actions
March 4, 2013 at 1:52 pm

#290480

Hi All,
I am trying to create a CLR stored procedure. What I was hoping for was a stored procedure that would understand what my object was. Ex: I have a MedicalRecord.dll that I developed. I want to be able to set a reference to my MedicalRecord.dll. How can this be done? I'm not simply looking for a substitute for a T-SQL insert/update statement.
Thanks,
Bill

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

it's possible, but you have to register your DLL into the database as an assembly, before you can create a CLR which uses your Medical.DLL as a reference. You might need to provide more details.

this can be a little tough, because a lot of it depends on your DLL. if it has dependencies, you might have to register the dependancies for Medical.dll before you can register that, and it can go pretty deep down the rabbit hole; eventaulyl you might find there are some dll.s that are not allowed to be registered as an assembly in SQL.

a basic example: if you wanted to create a CLR which converts RTF to Text, or vice versa, you find you need a reference to Windows.forms.

--you might need to set trustworthy on in the database as well:

--set the database trustworthy

ALTER DATABASE DMZ SET TRUSTWORTHY ON

so you'd have to register the windows dll first, before you could reference it in a new CLR:

--Create assembly [System.Windows.Forms]

CREATE ASSEMBLY [System.Windows.Forms] AUTHORIZATION dbo

FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll'

WITH PERMISSION_SET = UNSAFE

GO

if it was just a Plain Old Class Object(POCO) in the Medical.DLL, it will register cleanly; but it depends on what your DLL really references.

After the above is done, when you create a Visual Studio project, it will be allowed to references Any DLL that has been registered as an assemby(ie System.Windows.Forms.dll or your Medical.DLL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ennawarner SSC Rookie Points: 41 More actions · Answer 2

Thanks Lowell. My object is a hybrid POCO. POCO plus SQL classes already present. Thanks a ton bro