CLR Integration in SQL Server 2005: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
CLR Integration in SQL Server 2005

page 5 of 7

by Soyuj Kumar Sahoo
Feedback

Average Rating:

Views (Total / Last 10 Days): 24100/ 187

Article Contents:

Security Permissions

Security Permissions are mainly due to the code check performed by the SQL Server 2005. There are certain code checks performed by SQL Server 2005 when we build a managed stored procedure (or CLR integrated stored procs/functions/or triggers). SQL Server performs checks on the managed code assembly during two steps.

· It checks at the Creation time (i.e. CREATE ASSEMBLY) when the managed code is first registered in the database.

· It also checks at runtime.

Create Assembly Checks

The following checks are performed when the CREATE ASSEMBLY statement is run for each security level. If any check fails, CREATE ASSEMBLY will fail with an error message. Global Checking for any mode:

· The assembly should already register in the database.

· The assembly is one of the supported assemblies.

· All the referenced assemblies and their dependencies are available in the given location <location>, which is given in the create statement i.e. CREATE ASSEMBLY FROM <location>.

« (Page 4)

View Entire Article

(Page 6) »

Article Feedback

Comment:
Title:
Name:
Url:	( Optional )

Please add 3 and 8 and type the answer here:

User Comments

Title: best one
Name: niraj
Date: 9/10/2008 4:02:12 PM
Comment:
thank you for this article

Title: Good one
Name: Vijay Kumar
Date: 2/20/2008 1:13:32 AM
Comment:
Hi Soyuj,
Really a nice article.

Thank you
Vijay Kumar
Software Engineer

Title: CLR Integration
Name: Hemant Sudehely
Date: 2/11/2008 2:53:52 AM
Comment:
Thanks for providing such a good article to understand features of sql-server2005. Its really good to read.

Title: Very nice Article
Name: Pankaj Bahuguna
Date: 1/14/2008 6:17:46 AM
Comment:
Thanks Soyuj Kumar Sahoo,

Just because of this code,
I have been able to implement CLR Integration in my application

Title: Nice Article
Name: Sumit Kumar Sharma
Date: 9/27/2007 7:30:01 AM
Comment:
Hi Soyuj,
This article is really useful for me to builed and deploy the Assemblies into Sql Server.

Thanks Once again....!!!

Title: Execution of the CLR Stored Procedure failed
Name: Raaz
Date: 8/20/2007 9:06:57 AM
Comment:
The following are the steps that were followed to call the webservice from CLR Stored Procedure...

I was able to create the assembly and CLR stored procedure in SQL SERVER but failed at final execution of the procedure....

wsdl /o:TerraService.cs /n:ClrWebServices.Test http://www.terraserver-usa.com/TerraService2.asmx
--------------------
cLR Procedure
--------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ClrWebServices.Test;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertLocation(SqlString city,
SqlString state, SqlString country)
{
using (SqlConnection cn =
new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.Locations
(City,State,Country,AerialMap) VALUES
(@City, @State, @Country, @Image)";

using (SqlCommand insertCommand =
new SqlCommand(query,cn))
{
SqlParameter[] sqlParams =
new SqlParameter[4];
sqlParams[0] =
new SqlParameter("@City", city);
sqlParams[1] =
new SqlParameter("@State", state);
sqlParams[2] =
new SqlParameter("@Country", country);
sqlParams[3] =
new SqlParameter("@Image",
GetLocationImage(city,state,country));

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}

public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString countr

Title: Can't deploy the assembly
Name: Mr SQL
Date: 3/30/2007 7:23:15 AM
Comment:
I get an error when attempting to deploy the assembly to SQL Server Express. It comes up with

Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'MyCLRAssembly' failed because assembly 'MyCLRAssembly' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

Does anybody know hot to fix this? I have followed all the steps as above and they worked fine until I hit this error.

Many thanks.

Title: WonderFul One
Name: Vinuraj
Date: 3/15/2007 6:10:12 AM
Comment:
Its a Wonderful One I am searching. With a Brief Descrition I Got it.

Title: Its realy nice and very very important stuff for creating the system.web.dll
Name: Nitin Shende
Date: 2/6/2007 6:26:08 AM
Comment:
Thanx for the detail description.
This article covers all the steps required for the creating the assembly, which reference of the system.web.dll
Do posting more and more.

Title: Problem with system.web.dll
Name: Josh
Date: 1/15/2007 5:09:17 PM
Comment:
I'm having the same problem as Lan calling CREATE ASSEMBLY on an assembly which references System.Web.dll.

See this post for more info. Please respond with help there.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/8782336f27e9271c/?lnk=igtc#

Thanks,

Josh

Title: Need help for deploying assembly
Name: Lan
Date: 1/14/2007 9:04:23 AM
Comment:
This is great! Thank you for the detail description.
I'm trying to deploy a simple assembly that references the System.Web.HttpUtility class. I got the following error:

Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Web' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Drawing.BufferedGraphicsContext::bFillColorTable][mdToken=0x6000243][offset 0x00000053][found address of Byte] Expected numeric type on the stack....

When I tried to deploy the system.web.dll, I got the same error. Any idea of what could be the problem?

Thank you!
Lan

Title: Its really cool
Name: vas
Date: 1/8/2007 10:00:25 AM
Comment:
Its one of the best article i read so far. keep it up. do posting more...

Title: fed back for soyuj
Name: ranjan patra
Date: 12/26/2006 9:57:51 AM
Comment:
the way u discussed all the things is awosome.
it is always expected from a guy like u to share u'r knowledge with others.
as i personally know u it is a great pleasure to get some basic idea about stored procedures .
keep it up.
tk cr
ranjan

Title: Very Very Important and new stuff
Name: Abhishek Kumar Singh
Date: 12/20/2006 5:25:37 AM
Comment:
This article covers one of the newest and important functionality of SQL Server 2005. As a .net programmer I got to know feature to implement. Thanx Soyuj for providing such a wonderful content.