When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later
I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
Try the simple code to get a .dll file:
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SampleSP(string connstring, out int spid)
{
using (SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT @.@.SPID", conn);
spid = (System.Int16)cmd.ExecuteScalar();
}
}
}
And then let's manually create assmebly from the dll file in Management Studio:
1. Login to SQL2005 as 'sa', locate a database->new query->execute following command:
alter database yourdb set trustworthy on
go
sp_configure 'clr enabled',1
reconfigure with override
go
2. create an assembly from the dll file
3. create a stored procedure which references the assembly:
create procedure testCLR @.conn nvarchar(max),@.i int output as
external name ClassLibrary2.[StoredProcedures].SampleSP
4. Test the procedure:
declare @.i int
exec testCLR 'Data Source=.;Integrated Security=SSPI;Database=master',@.i output
select @.i
And here is a good torturial for using CLR in SQL2005:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.asp
