Deploying database with .NET assemblies?

We're going to release a .NET 2.0 application soon (3-4 months) that uses SQL Express 2005 as the backend. We'd like to write some of our SPROC in C# (for obvious reasons!). Our .NET 2.0 app will be deployed using ClickOnce and each customer will have one instance of SQL 2005 Express installed locally.
Is there a way for be to deploy the database with all the C# SPROC "embedded" in it, or I'll have to register assemblies individually? I'm concerned about registering assemblies during installation, especially since we're deploying the application using ClickOnce (but not the database).
Also, from time to time, we need to add stored procedure or modify existing ones. In the "old" .NET 1.1 days, it was easy to do this. We had an assembly which main purpose was to update the database structure and SPROC with simple T-SQL commands (ie: ALTER PROCEDURE). Is there a way for us to achieve something similar with our application if we use C# SPROC?
Another concern I have is that the SQL database will most likely NOT be installed on the same machine as the client application (it will be installed on a server). Sending T-SQL queries worked fine with this scenario before, but now that things have changed, I'd like to know how to achieve such a thing.
Thanks for your help!
Carl
PS: any good book on Yukon out there yet? I can't find any!
Carl,

The best book out there on SQL Server 2005 is "A First Look at SQL Server 2005 for Developers" by Bob Beauchemin, Niels Berglund and Dan Sullivan.

To reload a new version of the assembly, using ALTER ASSEMBLY will work as long as the existing sprocs/functions have the same method signatures.

Deploying the database with the sprocs embedded is no problem. They are just held in varbinary form in a database level system table.

HTH,
Christian|||Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.
About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...
Thanks a lot again!
Carl
|||

Carl Mercier wrote:

Christian,
Thanks for your answer! I looked into the Beauchemin's book on Amazon a few days ago and it looks interresting. What is holding me a little is that it's based on a 1 year old beta. Things have probably changed a lot since and might also change again for RTM.

As a co-author of the book, I can definitely say it is interesting :-). Yes, it is based on a one year old beta, and much have changed. However, we do have an errata page as well as a page for changes, so you'll always be able to see the latest info.

Carl Mercier wrote:

About ALTER ASSEMBLY, do you know if it's possible to ADD managed procedures, functions or modify the signatures in some way? Also, do I need to use ALTER ASSEMBLY from the server machine or it could also work remotely? I assume it would, but I think I'm missing pieces of how this new managed SQL works right now...

Sure you can add managed procedures, after running ALTER ASSEMBLY you just run CREATE FUNCTION/PROCEDURE/TRIGGER against your new methods. You are NOT allowed to modify signatures.
ALTER will work remotely, BUT; notice that the SqlServer project (which are used to create assemblies for SQL Server) in Visual Studio does not have functionality for ALTER assembly. That's one of the reasons I created my "yukondeployment" MSBUILD tasks and templates for Visual Studio. You can download it and read more about it here.
Niels|||Thanks for your help! I will definately check out Yukon Deployment!
I didn't know you were co-author of the book. I thought you deserved one more sale so I went ahead and ordered the book. Hope it arrives in time for my vacation! I'll read it on the beach in a few days! I hope this sale will at least buy you a cold one.
Your help is appreciated!
Carl

Deploying custom assemblies

Hi All,
I have developed a custom assembly and now I want to deploy it on to
the server as per the instructions mentioned in the books online
(ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_prog_rdl_8mue.htm), I want
to remove the commented portion of the RSReportServer.config file to
enable custom assemblies permission.However, I find that I dont have
any such commented portion in the file. I am using the Developer
edition of Reporting Services.
Can any one guide me if the file being edited is correct ? Does it
have to do something with the edition of RS that I am using?
Thanks in advance,
Lone CrusaderHi Loner,
Depending on what permission levels your custom assemblies need you'll need
to update your RS policy file and deply that too.
You'll need to add a PermissionSet AND CodeGroup configuration settings
(when using Report Designer, it uses it's own levels of Code Trusting (it
fakes a few things), when you do it by yourself, you'll need to define this
Code Trust (RS "trusting" your custom assembly) yourself
- peteZ
"Lone Crusader" <ravi32ful@.rediffmail.com> wrote in message
news:cc093b66.0409110001.3a1ac8bd@.posting.google.com...
> Hi All,
> I have developed a custom assembly and now I want to deploy it on to
> the server as per the instructions mentioned in the books online
> (ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_prog_rdl_8mue.htm), I want
> to remove the commented portion of the RSReportServer.config file to
> enable custom assemblies permission.However, I find that I dont have
> any such commented portion in the file. I am using the Developer
> edition of Reporting Services.
> Can any one guide me if the file being edited is correct ? Does it
> have to do something with the edition of RS that I am using?
> Thanks in advance,
> Lone Crusader|||Bryant Likes wrote a nice article on this issue - read it at
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Lone Crusader" <ravi32ful@.rediffmail.com> wrote in message
news:cc093b66.0409110001.3a1ac8bd@.posting.google.com...
> Hi All,
> I have developed a custom assembly and now I want to deploy it on to
> the server as per the instructions mentioned in the books online
> (ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_prog_rdl_8mue.htm), I want
> to remove the commented portion of the RSReportServer.config file to
> enable custom assemblies permission.However, I find that I dont have
> any such commented portion in the file. I am using the Developer
> edition of Reporting Services.
> Can any one guide me if the file being edited is correct ? Does it
> have to do something with the edition of RS that I am using?
> Thanks in advance,
> Lone Crusader

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
>

Deploy .NET Assembly with UDFs to a particular SCHEMA

Is it possible to deploy .NET assemblies and the UDF functions therein from Visual Studio.NET into a particular schema?Unfortunately this is not possible through Visual Studio 2005. You would need to write your own script for doing this.
However, you can execute any SQL script during the post deploy process in your SQL Server project as:

1. Write your script into a file called postdeployscript.sql:

2. Add the file to your project by right clicking your project in Solution Explorer and Add à Existing Item.

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run this scripts after the deployment of your project.
Same holds for predeployscript.sql if you want to execute a script on the SQL server before deployment.

These scripts can be used to automate something that Visual Studio does not currently support.

Thanks,
-Vineet.

|||

postdeployscript.sql and predeployscript.sql are the things I dreamed about all the time . You should definitely include them into default template of SQL Server Project, because I was searching for this solution for two months until I found this post. I asked similar question on the TechNet and the answer was just "you cannot do it".

Deploy .NET Assembly with UDFs to a particular SCHEMA

Is it possible to deploy .NET assemblies and the UDF functions therein from Visual Studio.NET into a particular schema?Unfortunately this is not possible through Visual Studio 2005. You would need to write your own script for doing this.
However, you can execute any SQL script during the post deploy process in your SQL Server project as:

1. Write your script into a file called postdeployscript.sql:

2. Add the file to your project by right clicking your project in Solution Explorer and Add à Existing Item.

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run this scripts after the deployment of your project.
Same holds for predeployscript.sql if you want to execute a script on the SQL server before deployment.

These scripts can be used to automate something that Visual Studio does not currently support.

Thanks,
-Vineet.

|||

postdeployscript.sql and predeployscript.sql are the things I dreamed about all the time . You should definitely include them into default template of SQL Server Project, because I was searching for this solution for two months until I found this post. I asked similar question on the TechNet and the answer was just "you cannot do it".

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