Error deploying managed stored proc

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

Error Creating User on Database

Hi there,

I am setting up a database for the first time using SQL Server Express.

I have managed to create a database and create a table with data etc, all straight forward.

The problem I am having is when I come to creating a user for the database. I have the following users in there already, which I am guessing are put in by default:

dbo

guest

INFORMATION_SCHEMA

sys

When I try creating a new user via this screen I get the following error:

TITLE: Microsoft SQL Server Management Studio Express

Create failed for User 'growstudiouser'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

'growstudio' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

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

I was wondering if anybody could advise me as to what I'm doing wrong. I am typing in the name as "growstudiouser" and the login name as "growstudio".

Or am I just using the wrong section to do what I want. I'm trying to create a user so that when the database is built I can connect to it using a username and password that I have created. I'm not sure I'm taking to the correct route as there is no password option anywhere when creating a new user.

Thanks in advance,

Kevin

hi Kevin,

usually a database user is associated with a server's login, if you do not define a user as

USE tempdb; GO CREATE USER testUser WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo; GO DROP USER testUser;

where the database user is not associated with a server level principal, but this is another story..

so you should first create the server login as

USE master; CREATE LOGIN testLogin WITH PASSWORD = 'his strong password'; GO USE tempdb; GO CREATE USER testUser FOR LOGIN testLogin WITH DEFAULT_SCHEMA = dbo; GO DROP USER testUser; GO USE master; GO DROP LOGIN testLogin;

regards

|||

SQL Server follows two level security architecture. Login and User . Login is to access to the server. If you have login it does not mean that you have access to all the databases in that server. NO. To access database, the Login has to be mapped/added to the database as user. So , in your case what your are probably doing is you are not maping the user with a login . You should select a login while creating user

Madhu