Executing .net code in SQL Server 2008

[JScannell]

Apparently you are supposed to be able to create an entity of .net code that can be executed from within a stored procedure of SQL Server 2008.

I need an explanation of exactly what I need to do to do this. I found an example where you could create an assembly using VS 2005. I have VS 2010 and the instructions I found don't play out in that environment.

I've already created a class object called baseClass with a method called CreateSID.

Now I need to do something to create an assembly that can be registered with SQL server.

That's where I'm lost. Could someone provide me with a detailed instructions on what to do?

Thanks in advance,

Jerry Scannell

 

[RTag]

http://msdn.microsoft.com/en-us/library/ms254498.aspx

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[JScannell]

Thanks for the quick turnaround on this. I think that I might have missed something when I initially created my .dll because when i follow this instructions, I run into a problem with item 3. My "Build" menu only has "Build solution" and "Build CreateSID". What do I have to do to make it so I see "Deploy" ?:

To deploy the assembly using Visual Studio

1.
Build the project by selecting Build <project name> from the Build menu.

2.
Resolve all build errors and warnings before deploying the assembly to the test server.

3.
Select Deploy from the Build menu. The assembly will then be registered in the SQL Server instance and database specified when the SQL Server project was first created in Visual Studio.


Thanks in advance,

Jerry Scannell

 

[RTag]

You are prbably looking at VS2010 .

Check the VS2005 page :

http://msdn.microsoft.com/en-us/library/ms254498(v=VS.80).aspx

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[JScannell]

Now I am really confused. Where am I supposed to be writing the code? In VS 2010 or inside SQL Server?

The link you sent me to looks like everything is being done in SQL server.


Jerry Scannell

 

[RTag]

JScannell, I am sorry. I never tried CLR in VS2005. Deploying assembly from VS2010 is prety easy.

In general, once you compile your assembly you need to let SQLServer where is it using CREATE ASSEMBLY.

http://msdn.microsoft.com/en-us/library/ms189524.aspx

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[JScannell]

RTAg,

I am using VS2010. How do you make an Assembly with VS 2010? I can make a DLL, which I've already done. It's the making of the Assembly that I don't know how to do.

Once I get over that hump, then I can run the SQL command to register it.

Thanks in advance,

Jerry Scannell

 

[JScannell]

As a followup, I just tried to do the create assembly based on the instructions I found on that link you gave me. Here is the command I ran:

CREATE ASSEMBLY CreateSID FROM C:\CreateSID\CreateSID.dll WITH PERMISSION_SET = SAFE;

This is the error I got:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'C:'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

This is the syntax example that the MSDN site gave which I followed to the letter substituting my dll's path:

CREATE ASSEMBLY HelloWorld FROM <system_drive>:\Program Files\Microsoft SQL Server\100\Samples\HelloWorld\CS\HelloWorld\bin\debug\HelloWorld.dll
WITH PERMISSION_SET = SAFE;

What am I doing wrong?


Jerry Scannell

 

[gmmastros]

try putting single quotes around the dll, like this:

CREATE ASSEMBLY CreateSID FROM [!]'[/!]C:\CreateSID\CreateSID.dll[!]'[/!] WITH PERMISSION_SET = SAFE;

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JScannell]

That syntax worked. But I couldn't successfully create the assembly. I got this error:

Msg 6257, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'CreateSID' failed because the assembly is built for an unsupported version of the Common Language Runtime.


So, what's next? I am using VS 2010 (the latest version, I think) and SQL Server 2008 (also the latest version) so what could it be looking for?

Is there still something I'm supposed to do to properly create the dll?


Jerry Scannell

 

[JScannell]

I researched that error. It turns out that my dll was created with .net 4.0. I changed it to .net 3.5 and the assembly was successfully created in SQL Server.

Thanks for everyone who has assisted in this effort. I've learned a lot and hopefully others have as well.

1. making sure that the .net framework is correct.
2. MSDN example error by not showing the single quote marks around the assembly's path
3. there is nothing special that needs to be done to compile a dll into an assembly (older versions of .net had considerations about that)

Thanks,

Jerry Scannell

 

[JScannell]

Now that I have successfully created an Assembly, how do I use it in a SQL command? I've found an example where a function needs to be created with the syntax of (the items below constitute AssemblyName:ClassName::MethodName):

create function GetConvertedSID ( @sid nvarchar(28) )
returns string
external name CreateSID:baseClass::CreateSID
go

This produces the following error:
Msg 102, Level 15, State 1, Procedure GetConvertedSID, Line 3
Incorrect syntax near 'CreateSID:'.

So what's wrong this time?




Jerry Scannell

 

[RTag]

JScannel, the format is :

create function GetConvertedSID ( @sid nvarchar(28) )
returns string
external name CreateSID.baseClass.CreateSID
go

you may need to use CreateSID.[<NAMESPACE>.baseClass].CreateSID
where <NAMESPACE> is the namespace where your class is placed.

check this article:

http://blogs.msdn.com/b/mattn/archive/2008/04/22/writing-clr-stored-procedures.aspx

search for "EXTERNAL NAME" to find where is your case.


You may need to set TRUSTWORTHY property of the database

http://msdn.microsoft.com/en-us/library/ms187861(v=SQL.105).aspx

ALTER DATABASE <DatabaseName> SET TRUSTWORTHY ON

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[JScannell]

So add that to the list of things. The syntax I had found was the one with the colons as seperators.

So, I tried this:
create function GetConvertedSID ( @sid nvarchar(28) )
returns varchar(50)
external name CreateSID.baseClass.CreateSID
go

but got this error:
Msg 6505, Level 16, State 2, Procedure GetConvertedSID, Line 1
Could not find Type 'baseClass' in assembly 'CreateSID'.

However, in my code, this is what I have:

Imports Microsoft.VisualBasic
Imports System.Security.Principal
Imports System.Data
Imports System.Text

Public Class baseClass
Public Function CreateSID ( sidBytes as Byte() ) as string
.
.
.
End Function
End Class

So I don't know why SQL said that there was no baseClass.

Should I put in a nemespace? I didn't see anything like that in any of the examples I found.



Jerry Scannell

 

[RTag]

Check what is the default namespace in your assembly. Right click on the project -> Properties ->tab "Application" . Default namespace should be before your class name

CreateSID.[<NAMESPACE>.baseClass].CreateSID

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.