Limited number of assemblies available in Visual Studio SQL Server Projects

Limited number of assemblies available in Visual Studio SQL Server Projects

• jimson

  Two additional points:

  1. Actually, VS has a nice feature where if the assembly you're looking for is already registered in your SQL Server database, then you can reference it directly from your VS Database Project, even if it is a custom assembly or one that is not on the supported system assemblies list. It should show up normally under "Add Reference".

  2. There are extremely few things that do not work in unsafe permission set, and the restrictions that are in place weren't done with reliability in mind. There are a number of ways to effectively kill your SQL Server process when running under unsafe, if you wanted to.

  Steven

  
  
  

• NateLucy

  I have solved this. It works if you create it as unsafe.

  Thanks for your answers,

  Johan

  
  

• Ian Bavey - MSFT

  Hi Jens,

  Thanks for your answer.

  Microsoft.AnalysisServices.AdomdClient is already registered in the GAC, but I don't see it in Visual Studio. What else do I have to do to be able to reference it

  From what I can see it has unrestricted permissions in .NET Runtime Security Policy.

  Best Regards,

  Johan Ahlen

  
  

• QingY

  Hi,

  if you want more than these one, you have to register them in the GAC to see them in VS. The ones listed were intensivly tested by Microsoft not to produce any memory leaks being threadsafe etc. Additional (custom) resources have to be listed first for the computer and marked with the security policy level they need. "Doing the stack walk" while registering them at deployment time, SQL Server makes sure that called ressources reflect the at most the security settings of the registered assembly.
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  
  

  
  

• tarun_jwalia

  i meant safe and external access :)
  

• Nate Block

  Hi Johan,

  As the previous person mentions, the assemblies that are available to references in a sql server project are restricted. I think what this person meant to say is that these system assemblies are loaded from the GAC by sql server, yet this has nothing to do with when you wish to use an unsupported system assembly.

  Note here about my terminology, system assembly = .Net BCL, user assembly = custom assembly.

  You are wishing to reference an unsupported system assembly in sql server, this can be attempted (I say this because depending upon what the individual assembly tries to do, even in unsafe it will not be allowed to run when called upon and it's corresponding app. domain will be unloaded).

  To do this, first run a create assembly statement referencing the adomd client assembly. Second, create a new regular class library project and add your reference to the adomd client libary. Then build the class in VS. Next, run the create assembly statement for your newly created class.

  *You may have to set lower permission sets on the adomd client depending upon its attributes etc. There is a concept known as Host Protection Attributes (HPAs) which help sql server in determing which managed libaries should be allowed to execute within the hosted clr context. Basically, you may have to set the adomd client to unsafe it is "threatening" enough. But again, even with unsafe, sql server will only allow so much to occur aka you cannot kill sqlservr.exe from sqlclr code.

  Hope this helps,

  Derek

  
  

• vb6-2-vb2005

  Noted on the unsafe being able to kill sqlservr. I did find that HPAs are only used in safe and unsafe perm buckets.
  

• Ron_

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

  Create failed for SqlAssembly 'Microsoft.AnalysisServices.AdomdClient'. (Microsoft.SqlServer.Express.Smo)

  For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.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.Express.ConnectionInfo)

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

  CREATE ASSEMBLY failed because type 'ShilohSchemas' in safe assembly 'Microsoft.AnalysisServices.AdomdClient' has a static field 'shilohSchemas'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.
  Warning: The SQL Server client assembly 'microsoft.analysisservices.adomdclient, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. (Microsoft SQL Server, Error: 6211)

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

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

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

  
  
  

• Haydee I

  Johan Ahlen wrote:

  It seems impossible to reference AdomdClient from a SQL Server assembly: It cannot be added using CreateAssembly

What error are you getting You should run CREATE ASSEMBLY gainst it's original location, and my bet is that you need to create it as unsafe

Johan Ahlen wrote:

It cannot be loaded at Runtime using Assembly.Load() or similar

that's because it's not in the database. You can only call Assembly.Load against assemblies in the database (or against the system assemblies deemed to be safe to be loaded from the GAC).

Niels