Deploying CLR Stored Procedures to a client, is there a better way

I'm developing an application locally, and after I complete major revisitions/bugfixes, I transfer the code/database changes to the client. If I change the code for a CLR stored procedure locally, I can just deploy the project to the local database, which handles everything for me.

Transferring to the client's database is more difficult, however. In order to update the CLR procedures, I need to drop my regular stored procedures, drop my functions, drop the CLR procs, and then re-add the CLR procs, functions and stored procedures to avoid errors based on dependencies. The stored procs/functions don't change, and it seems like there should be a way to just insert the new CLR stuff without dealing with the regular stored procedures or userdefined functions.

Basically, I'm asking whether there is a better way to do this, or if I'm stuck doing this excessive routine every time I need to update.

Thanks in advance

it sounds like your in the "debug" phase right now, so you could use multiple assemblies to seperate those SQLCLR routines that you know are still giving you problems and those that are not. Once fully tested you could then re-merge them back into one assembly.

You could also look at using the scripting wizard to script the assembly and all of it's dependencies from your local/dev box.

Derek

|||

Have you tried using ALTER ASSEMBLY from T-SQL to deploy changes to the assembly.

Detailed documentation on ALTER ASSEMBLY is available at http://msdn2.microsoft.com/en-us/library/ms186711(d=ide).aspx

Thanks,

-Vineet

deploying CLR objects best practice

Hi,
When I want to deploy CLR assemblies on a SL Server Production Server. What
is the best pratice to define where to store theses assemblies?
In the case of deploying with VS 2005, where the tool put the assemblies on
the server?
Thanks in adavance,
LaurentAssemblies are stored in databases, no matter if you deploy them using
CREATE ASSEMBLY command of VS.NET IDE deployment. Therefore, store the
assembly in the database where you need your CLR objects.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"laurent banon" <laurent.banon@.soorce.com> wrote in message
news:uNPGDOl%23FHA.2812@.TK2MSFTNGP09.phx.gbl...
> Hi,
> When I want to deploy CLR assemblies on a SL Server Production Server.
> What is the best pratice to define where to store theses assemblies?
> In the case of deploying with VS 2005, where the tool put the assemblies
> on the server?
> Thanks in adavance,
> Laurent
>

Deploying CLR from VStudio -- issue with permissions

I have a CLR project in my VStudio solution.

Whenever I deploy the project to SQL, the deployment will be successful, but, because the stored proc wrappers that get made in SQL are remade, all permissions that I had applied to those stored procs are gone and have to be reentered through SQL. I have made a TransactSQL script to run to automate this. Basically, what I need is to give them a group permission that is associated with the tables that they work with.

I gotta figure I am missing something simple that would allow my permissions to stick.

?

You can create "postdeployscript.sql" with everything you need to be executed after deployment, and put it to the root of the SQL-CLR project. Same holds for "predeployscript.sql" - it will be executed before deployment

|||Thanks -- that solves my problem.

Deploy CLR SQL Project using MSBuild

What is the "official" line on deploying CLR SQL projects through the use of build scripts like NANT and MSBuild?

I'm obviously keen to hook up my project to my continuous integration build but the only things I have found that touch on the subject are:

Do it by hand yourself using xcopy of assemblies and T-SQL

Use SQLCLR project

This old post that says what *might* happen

Cheers

I don't really understand your question; are you asking what MS says about using NANT/MSBUILD for deployment - or are you asking what the community thinks?

Anyway, if your aim is to automate your deployment, and continuous build my preferences would be:
1. scripting
or
2. SQLCLR project (I would say that as I am the developer)

I would not use the VS built in SQL Server project type, as I feel I do not have the control as I would like.

Niels

Dependant assemblies in CLR

This is related to one of my previous posts.

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)

Any ideas?

Thanks,

BIG

Hi BIG,

Are you loading the Novell library into SQL? One restriction with CLR integration is that outside of a static list of "approved" assemblies that we access in the GAC, all assemblies must be loaded into SQL.

Cheers,

-Isaac

|||Yes, they are loaded assemblies.
New info:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Novell.Directory.Ldap.Connection.connect(String host, Int32 port, Int32 semaphoreId)|

what the heck?|||

Hi BIG,

As you surmised, Novell is trying to load the Mono security assembly as part of the connect method. The problem is that dynamically loading assemblies under SQL CLR is explicitly disallowed as the error message states.

Unfortunately, Novell is explicitly calling Assembly.LoadFrom("Mono.Security.dll") which will always try to load the assembly from disk so there is no easy way to work around this problem. If instead the call had been Assembly.Load with the Mono.Security DLL's four-part name then you could solve this by preloading the Mono.Security.Dll into SQL Server yourself.

Steven

|||What if I use the sgen.exe tool and serialize the assembly and load teh serialized assembly?|||

That is the solution for a different problem where Xml Serialization also tries to load a dynamic assembly. Good try though.

Without changing the Novell source code to use Assembly.Load rather than Assembly.LoadFrom, I don't think there is any way around this. Sorry I can't be of any help.

Steven

|||Believe it or not, I got the source code for that .dll and did change it to Load now it does not want to work...gonna contact Novell.

Thanks,

BIG|||

So the Mono.Security assembly now loads correctly, but you're hitting a different error that prevents you from using it? Or are you still running into Loading issues?

|||Loading issue...
Original code: Assembly.LoadFrom("Mono.Security.dll");
My code: Assembly.Load("Mono.Security.dll");

Actually:

// Load Mono.Security.dll
Assembly a;
try
{
a = Assembly.Load("Mono.Security.dll");
}
catch(System.IO.FileNotFoundException)
{
throw new LdapException(ExceptionMessages.SSL_PROVIDER_MISSING,LdapException.SSL_PROVIDER_NOT_FOUND, null);
}

Error message is from the Exception thrown when the assembly is not found.

I am gonna research other ways to load an assembly, but I am sure I am screwed here, will try to post this in the relavant Novell forum as well.

What I may end up doing is trying to integrate these two assemblies into one (which I think Novell should have done in the first place :( )
OR
Making this into a standard exec and have SQL Server run the job calling that exec every night, so not in CLR, and lose portability with db.

Unless anyone has a better idea?

Thanks,

BIG S|||

Do you have the Mono.Security.Dll loaded in SQL Server yet? In order for the Assembly.Load to suceed, the assembly already needs to exist in your appdomain. So you need to do CREATE ASSEMBLY [Mono.Security] FROM 'path\Mono.Security.dll' with permission_set = unsafe first.

Also, you need to pass the full, 4-part assembly name to the Mono.Security assembly as documented: http://msdn2.microsoft.com/en-us/library/ky3942xh.aspx. You can get the 4-part name from sys.assemblies.

If this still doesn't work, you can verify that the Mono.Security.dll is reported as being loaded in your appdomain from the sys.dm_clr_loaded_assemblies dmv. If it's not there, try doing a dummy CREATE FUNCTION foo() returns int as external name [Mono.Security].bar.foo. (Sorry I can't verify if this is necessary right now - I just moved to Vista at home and don't have SQL Server installed yet).

Steven

|||ok working on it...

BTW: Even if I can't get it going, Steven u rock!

BIG S|||You are bloody brilliant...I got it working!

If you are ever in Toronto, beer is on me!

BIG S