I wrote an assembly and attached to a trigger in one of my tables. It works as I expected, but then I decided to modify the code, say adding some new triggers that I will use with other tables. Then I realize that in order to update my new version of the assembly in SQL first I need to remove the trigger in the table, then remove the assembly, add the new version and finally run a script to create the trigger again in the table.
How could I avoid going through all this process everytime I want to update my assembly?
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points: 1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works) 2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
i found one tutorial on self signing assemblies for use in sql server, but it appears to have errors. does anyone know of a better tutorial on this topic? the site im currently looking at is here: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
I have a question: how is it planned to replicate changes made to the assemblies and to the CLR functions or UDT? Will Visual Studio deploy change of the Assembly as ALTER not DROP/CREATE?
How does SQL Server 2005 handle the replication of assemblies? I get the following when trying to replicate an assembly:
Timestamp: 6/15/2007 9:41:33 AM Message: HandlingInstanceID: 05eaed53-b6be-42da-8c72-0f91fcbfa5b6 An exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred and was caught. ------------------------------------------------------------------------------------------------- 06/15/2007 09:41:33 Type : Microsoft.SqlServer.Replication.ComErrorException, Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Message : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber. Source : Merge Replication Provider Help link : ErrorMessage : Unspecified error InterfaceId : 00000000-0000-0000-0000-000000000000 HelpFile : HelpContext : 0 Description : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber. ComHResult : -2147467259 ErrorCode : -2147467259 Data : System.Collections.ListDictionaryInternal TargetSite : Void Run() Stack Trace : at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run() at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize() at SQLClientSync.SQLClientSync.Replicate() Additional Info: MachineName : REPCLIENT TimeStamp : 6/15/2007 1:41:33 PM FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.1.1, Culture=neutral, PublicKeyToken=d2ff10320a77ec18 AppDomainName : sqlclientsync.exe ThreadIdentity : WindowsIdentity : REPCLIENTAdministrator
I appears that the initial replication of the assembly was fine, when re-running the sync (no changes to the assembly), this error is generated.
I need to dynamically create a ADO.NET connection manger
so, my question is if it is safe to create it with the string that is generated (ConnectionManagerType) when creating the connection manager manually on my PC.
I understood that this string is the full qualified name of the assembly, so will it be different when I create it on another P.C or it is fixed on all P.Cs ?
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
Where is it documented what the list of approved assemblies are for SQL 2005. Also, is there a way via T-SQL, or other means to get it programatically?
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
Did this as a exercise and am fed up with my fruitless searching.Went and create an CLR assembly and all with min hassel. Now that Ihave that dll out there, I am tring to find some system table or somedisplay in management studio that shows me where the file is located onthe hard drive and cannot locate it.Can someone please point me in the correct location.Thanks.
I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:
CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'
CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey
GRANT UNSAFE ASSEMBLY TO CLRAssembler
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler
REVOKE CONNECT SQL FROM CLRAssembler
Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.
The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.
Is this the only way to do this? Or am I missing something?
To set up a managed sproc to act as a WSE 3.0 web service client I had to register the following .Net DLLs as UNSAFE assemblies: System.Configuration.Install.dll System.Web.dll Microsoft.Web.Services3.dll
In order to register UNSAFE assemblies I had to set the database's TRUSTWORTHY property to 'true'.
Can anyone tell me what are the implications of doing this?
Hi,I have developed a Stored procedure to output text to a text file, after doing a lot of reading here. I want to be able to output data to a Postgres database, to be used for web mapping. I have referenced an Assembly Npgsql.dll so I can send updates to the postgres database when my ms sql database is updated. However, after building my class library, when I try to CREATE ASSEMBLY in sql server 2005 express, it sends out a message: Msg 10301, Level 16, State 1, Line 1 Assembly 'ClassLibrary1' references assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(error not found)). Please load the referenced assembly into the current database and retry your request. The error seems to be in the Npgsql.dll assembly, can anybody please shed some light on this. I tried to load the system.drawing assembly, and can do so in Unrestricted mode. It then spits out the same problem for System.Windows.Forms.dll. I am still very new to all of this so any help would be appreciated. Cheers, Jatz91.
Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL... Assembly Validation SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:
The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.
The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.
For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:
The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.
The assembly should not contain any static data members in its classes unless they are marked as read-only.
The classes in the assembly cannot contain finalizer methods.
The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.
Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:
Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.
For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.
COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!
I have a mixed CLR assembly (including native C++ and manage C++). Can I use this assembly in SQL SERVER 2005 or 2008? I know pure managed assemblies work fine. Thanks.
I'm creating a script task that needs to reference the Visual Studio Team Foundation Server assemblies, but I'm unable to setup a reference. (Note: the Team Foundation assemblies are part of the VSTS SDK installation.) When designing the script, if I select Project, Add Reference, the .net assemblies for the Microsoft.TeamFoundation namespace are not listed -- even though the assemblies do show up if do the same in a Visual Studio.Net project for C# or VB.
I've also tried to add a reference in a Custom Component Set -- View, Object Browser, Browse, Edit Custom Component Set, and selected a specific assembly. But I get the following error:
--------------------------- Microsoft Visual Studio for Applications --------------------------- The following components could not be browsed:
C:Program FilesVisual Studio 2005 SDK2006.09VisualStudioIntegrationCommonAssembliesMicrosoft.TeamFoundation.dll
Why can that .dll be referenced in standard VB.Net or C#.Net apps, but not in an SSIS script task?
I am using CLR integration to provide all the stored procedures for my project, but the issue I am finding is that deployment of my assembly and it's stored procedures (and two UDTs) is now starting to take so long that I am getting a timeout from SQL Server during deployment (with the default settings of 600 seconds remote connection timeout).
Is there a way to speed-up the deployment process?
I know I could employ a differing strategy of splitting the procedures into multiple assemblies, but I am more concerned that the legth of time used to deploy might indicate some other issues with my server.
By the way, my current assemly contains: 247 Stored Procedures, 7 User Defined Functions, and 2 User Defined Types.
I am developing a Script Task for my SSIS package to transfer a file via FTP over SSH (SFTP). I have a nice set of Assemblies/DLLs (purchased from IP*Works) that gives me the added functionality of SSH.
To even get Visual Studio for Applications to see it in the reference list, I copied the DLL (nsoftware.IPWorksSSH.Sftp.dll) to the C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 directory, added it in the references for the script, and added the Imports nsoftware.IPWorksSSH.Sftp line in the script.
As described in: http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
Yet, when I try to run the SSIS package I get a DTS Script Task Runtime Error:
Could not load file or assembly 'nsoftware.IPWorksSSH.Sftp, Version=7.1.2203.0, Culture=neutral, PublicKeyToken=cdc168f89cffe9cf' or one of its dependencies. The system cannot find the file specified.
I tried to move the DLL to the bin directory in the SSIS Package, but that did not help.
VS2005 has a deployment feature that uses a database connection string to automatically connect to SQL2005 and create the assembly and procedures within the IDE. I am unable to access this feature from MSBUILD or any other command prompt utility so far which is a requirement for our internal build procedures.
I am looking for the ability perform a command line deployment of a SQL CLR project and pass in the proper server and database name also on the command line (or config file if necessary).
Does this functionality exist out of the box? If not, any suggestions on how to accomplish this?
I am using Script component and trying to import the Microsoft.WindowsAzure.Storage package. i used Nuget within my application and it installed successfully. However, I dont see it in the assemblies as well. Also, when i open the project next time, it is not there and i need to re-install or restore it.
I am encountering a timeout expired error when deploying a .NET assembly in SQL Server 2005 using Visual Studio.NET. I already enabled SQL Server for CLR. Whenever I run the CREATE ASSEMBLY command in SQL Management Studio, my query just ends up executing without stopping.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
