SQL Server 2005 and .net framework 3.5

Question

 

Is it possible to use the visual studio 2008 to create SQL server SP having .net framwork 3.5 to run on SQL Server 2005?

Answer 1

Absolutely!

Niels

Answer 2

But I am getting error

 

Error: Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.

 

 

 

Answer 3

Well, I should probably have qualified my answer above: Assemblies created with VS 2008 can definitely be used in SQL Server 2005/2008. However, the list of "blessed" assemblies; i.e. the assemblies that are allowed to be loaded from the GAC, is still the same. So if you have dependencies on assemblies not in that list, you have to manually load them into the datbase before you can load your own user assembly.

Niels

Answer 4

Syste.core is GAC assembly of version 3.5. Why do I need to load it manually? This is already present on machine.

 

Answer 5

As I said in my previous reply, not all system assemblies (in the GAC - 2.0 or 3.5) are allowed to be loaded from the GAC in SQL Server - it is only 13 assemblies that can be loaded from the GAC. All other assemblies, regardless if they are system assemblies or user assemblies, have to be loaded from the database, and subsequently existing in the  database. This is not a 3.5 issue per se, this is what it always has been.

Niels

Answer 6

Can you please let me know the steps to load these GAC assembly? Is there any KB article?

 

Answer 7

It gives below error..

 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for SqlAssembly 'System.Core'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' 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.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type.
[ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x6000793][offset 0x00000071][found address of Byte] Expected numeric type on the stac...
Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. (Microsoft SQL Server, Error: 6218)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=6218&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Answer 8

You most likely have to create it with a permission set of UNSAFE.

Niels

Answer 9

This link tells me this information is no longer available

 

Jim

 

Answer 10

I am trying to use a 3.5 complied assembly and getting the error below while creating the assembly in the database:

 

Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)

 

How do I add this assembly to the SQL catalog?

Answer 11

I copied the system.core.dll from C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5

 to the same folder where my assembly resides.  I tried to use the Studio GUI to create the assembly but the same error occured.  Then I decided instead to use TSQL:

 

Code Snippet

use mydb

Go

Create Assembly MyAssembly from 'C:\Program Files\Microsoft SQL Server\90\Tools\Binn\MyAssembly.dll' WITH PERMISSION_SET = UNSAFE

 

 

 

and it worked.  Gave a warning about sytem.core version 3.5 not being tested, but other than that it was good.

 

Thanks Bob for the help.

 

Answer 12

nielsb wrote:

As I said in my previous reply, not all system assemblies (in the GAC - 2.0 or 3.5) are allowed to be loaded from the GAC in SQL Server - it is only 13 assemblies that can be loaded from the GAC. All other assemblies, regardless if they are system assemblies or user assemblies, have to be loaded from the database, and subsequently existing in the  database. This is not a 3.5 issue per se, this is what it always has been. Niels

 

That's completely lame. So is the SQL Server team gonna get their butts in gear anytime soon and change that? Is there any place I can learn more about this situation, this decision, or keep updated on any changes to this?

Answer 13

Chris O'Brien wrote:

That's completely lame. So is the SQL Server team gonna get their butts in gear anytime soon and change that? Is there any place I can learn more about this situation, this decision, or keep updated on any changes to this?  nielsb wrote: As I said in my previous reply, not all system assemblies (in the GAC - 2.0 or 3.5) are allowed to be loaded from the GAC in SQL Server - it is only 13 assemblies that can be loaded from the GAC. All other assemblies, regardless if they are system assemblies or user assemblies, have to be loaded from the database, and subsequently existing in the  database. This is not a 3.5 issue per se, this is what it always has been. Niels

 

It's not lame, and it make sense to anyone who is a DBA or understands SQL Server and what should/should not be being done inside the database tier.  SQLCLR is not a panacea, it doesn't replace proper application of where certain operations should exist.  I'd recommend that you start first by looking at the Books Online:

 

CLR Integration Security

 

As a DBA, the first thing I looked at when it came to CLR inside SQL was Security, then it took almost 2 yrs before I even enabled it on my own development server to play with it.  If you can't register the assembly under external_access, then in my opinion, it has no business in your SQL Server.  The SQL Server Team doesn't control whether a .NET assembly has the correct HPA's added to it by the .NET Team to certify it as safe for SQL Server.  If you want full access to CLR, then you need to do your coding in a middle/application tier, and leave the database for what it is really designed to do, CRUD, Create/Retrieve/Update/Delete data.  Otherwise you have to accept that CLR in SQL has limits that are imposed for very good reason, and you have to work inside of those.

Answer 14

Guidance is needed.

I don't have 2 years to learn all of the subtleties of this issue.

Is there guidance on this issue? Is there a document which discusses this? If there is, please supply a link.

Thank you.

Answer 15

USE [database]

GO

EXEC sp_changedbowner 'sa'

ALTER DATABASE [database] SET TRUSTWORTHY ON;

Create assembly [System.Core]

from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll'

with permission_set = unsafe

--Drop assembly [System.Core]

I created the assembly this way, but it is unsafe. So when I deploy my .NET3.5 stored procedure it complaints about static declaration.


Error    1    CREATE ASSEMBLY failed because method 'storedProcedure1' on type 'StoredProcedures'  in safe assembly 'Server' is storing to a static field. Storing to a static field is not allowed in safe assemblies.


So maybe there is a way to create the System.Core with "Safe"???? any ideas?

---

Give credit for those who guided you to the right direction by clicking "Mark As Answer"

Answer 16

@Jonathan Kehayias: Actually, it is lame, and I'll tell you why. Because Microsoft omits and overlooks functionality, generates obscure and unhelpful error messages, fails to completely document correct procedures to work around the problems it creates, then talks down to its users who need a product that actually works right or is documented accurately. It falls on the development community at large to commit years of trial and error to find a fix, offer it up through their own sweat and hair loss, then refine it through multiple streams of Q&A sessions between veterans and newbies to arrive at a workable solution. All that could be avoided by thoroughly and clearly conveying a product's limits and how to work within them.

This maddening cycle repeated time and time again wears on the most patient and trusting programmer who waits and hopes at his/her own peril for Microsoft, who is supposed to know its own products, to finally get its act together to at least take some responsibility for and document the deficiencies, when it's obvious this information is so desperately needed. The fact it doesn't is what's lame.

Instead of speaking to us condescendingly for having to guess, prod, and pick at a workable way of doing things, why don't you instruct us on the proper or acceptable way to address the problem.

My issue here is wanting to use the CLR to access operations missing from SQL Server 2005. I've spent two months trying to find complete documentation about how to use what is accessible in .NET 3.5, and it really scalds me to hear a flippant and typically-Microsoft non-answer.

Please pardon the flame, as it's not intended to be personal.

Answer 17

@Jonathan Kehayias: Actually, it is lame, and I'll tell you why. Because Microsoft omits and overlooks functionality, generates obscure and unhelpful error messages, fails to completely document correct procedures to work around the problems it creates, then talks down to its users who need a product that actually works right or is documented accurately. It falls on the development community at large to commit years of trial and error to find a fix, offer it up through their own sweat and hair loss, then refine it through multiple streams of Q&A sessions between veterans and newbies to arrive at a workable solution. All that could be avoided by thoroughly and clearly conveying a product's limits and how to work within them.

This maddening cycle repeated time and time again wears on the most patient and trusting programmer who waits and hopes at his/her own peril for Microsoft, who is supposed to know its own products, to finally get its act together to at least take some responsibility for and document the deficiencies, when it's obvious this information is so desperately needed. The fact it doesn't is what's lame.

Instead of speaking to us condescendingly for having to guess, prod, and pick at a workable way of doing things, why don't you instruct us on the proper or acceptable way to address the problem.

My issue here is wanting to use the CLR to access operations missing from SQL Server 2005. I've spent two months trying to find complete documentation about how to use what is accessible in .NET 3.5, and it really scalds me to hear a flippant and typically-Microsoft non-answer.

Please pardon the flame, as it's not intended to be personal.

You're ultimate vote in this area is to not use Microsoft technologies...if you're being forced in your job then go find a job selling t-shirts or building houses or something else more rewarding and less frustrating. If you want to continue discussing the specific issue then post something productive and help us help the original poster. You're ripping into a valuable member of the .NET and SQL Server community...and for what? What have you done lately?

---

Make everything as simple as possible, but not simpler.
-Albert Einstein

Answer 18

You're ultimate vote in this area is to not use Microsoft technologies...if you're being forced in your job then go find a job selling t-shirts or building houses or something else more rewarding and less frustrating. If you want to continue discussing the specific issue then post something productive and help us help the original poster. You're ripping into a valuable member of the .NET and SQL Server community...and for what? What have you done lately?

---

Make everything as simple as possible, but not simpler.
-Albert Einstein

No, I'm not ripping into a valuable member. I'm ripping into responses that repeatedly avoided answering the original poster's (and my) questions with details about how to use the CLR to access operations missing from SQL Server 2005.

@opc.three: What have I done lately? Asked for help and received none, then kept working at it without your non-existent help. It was also abundantly clear you didn't answer the question either, so you have no room to preach about "discussing the specific issue".

Many of the readers here - most I would hazard to say - are programmers. We're not DBAs and we're not privy to the internals of SQL Server. Instead of saying what is conveyed as, "well you don't know, so you're an idiot", provide information to educate. Forums are for learning, not for spreading worthless attitude. That's what I object to.

As to the original topic, here's what I have found after piecing together tiny bits from months of research and experimentation on my own:

• System.Core has to be created as an UNSAFE assembly before it can be used by a CLR user-defined function. This can be accomplished using the following SQL statements after copying System.Core.dll to the same directory as your DLL (MyProject\bin\Debug, in this example):
  

  ALTER DATABASE master SET TRUSTWORTHY ON;
  USE [master]
  -- Must execute change owner even though it fails.
  EXEC sp_changedbowner 'sa'
  GO
  CREATE ASSEMBLY SystemCoreLib
   FROM 'C:\MyProject\bin\Debug\System.Core.dll'
   WITH PERMISSION_SET = UNSAFE;
  GO

• Without the EXEC sp_changedbowner call, you can get intermittent errors about loading the assembly:
  

  An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
  System.IO.FileLoadException: Could not load file or assembly 'AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

• Alternately, SQL Server can register System.Core it automatically when you register your DLL that is dependent upon it. It will emit the following warning in either case:
  

  Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
  

• Register your DLL using the CREATE ASSEMBLY statement. If it doesn't reference one of the "blessed" assemblies, e.g. System.Core, it will have to be created with UNSAFE permissions and the database will have to be operated in TRUSTWORTHY ("ignore trust issues") mode. My DLL was named MyUdfsLib.dll and I named my assembly MyUdfs.
  

  CREATE ASSEMBLY MyUdfs
   AUTHORIZATION dbo
   FROM 'C:\MyProject\bin\Debug\MyUdfsLib.dll'
   WITH PERMISSION_SET = UNSAFE;
  

• When you create your DLL, the public class that contains your CLR functions must be in the unnamed namespace, i.e. no "namespace" block surrounding it. Otherwise, when you register the procedure or function, SQL Server will be unable to find it.
• The procedures and functions must be decorated with "[SqlProcedure]" or "[SqlFunction]" from Microsoft.SqlServer.Server. If you are using Visual Studio 2005 to create the user-defined function within a SQL Server project, using the Add > New Item... > User-Defined Function or Stored Procedure template will add this method decoration for you and place it in the predefined UserDefinedFunctions (partial) class. Example:
  

  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using Microsoft.SqlServer.Server;
  
  public partial class UserDefinedFunctions
  {
   [Microsoft.SqlServer.Server.SqlFunction]
   public static SqlInt32 StrLen(SqlString source)
   {
   // Return the actual number of characters in a string
   // because SQL Server's LEN() function is broken.
   return new SqlInt32(source.ToString().Length);
   }
  };
  
  

  And the associated CREATE FUNCTION statement in the [master] database:
  

  CREATE FUNCTION StrLen(
   @source NVARCHAR(MAX)
  ) RETURNS INT
  AS
   EXTERNAL NAME MyUdfs.UserDefinedFunctions.StrLen;
  GO

• Now you can execute your function using its fully-qualified name:
  

  USE [MyDatabase]
  SELECT master.dbo.StrLen('12345 ');

 

Answer 19

Thank you for showing all the steps on how you solved it.  It's unfortunately that including static variables or methods (including extension methods) requires the System.core library.  

Answer 20

I see. So your workaround (hack, really) is to run your database with the TRUSTWORTHY bit ON and to register System.Core as UNSAFE? This type of configuration should not be documented, at least not without a giant caveat, because it is horribly unsafe and unsecure. Enjoy supporting that. I hope you're not in a shared database environment with any less-than-expert developers writing SQLCLR objects.

Anyone else who reads the post from "--Buckeye Mountain Wireles..." and thinks it is a good idea, proceed with caution.

Oh yeah, and have a great day :-)

---

Make everything as simple as possible, but not simpler.
-Albert Einstein