ASP.NET, SQL Server, WPF, C#, VB.NET

When you use User Instance = True, you get a new (virgin) instance of SQL  
  
Server Express. This does not have the CLR enabled which means your  
  
application will have to enable it (at least once).  
  
hth  
  
--  
  
____________________________________  
  
William (Bill) Vaughn  
  
Author, Mentor, Consultant  
  
Microsoft MVP  
  
INETA Speaker  
  
www.betav.com/blog/billva  
  
www.betav.com  
  
Please reply only to the newsgroup so that others can benefit.  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
__________________________________  
  
"Michael Lang" <mickblang@keinSpam*hotmail.com> wrote in message  
  
news:e$eBxqjNGHA.1312@TK2MSFTNGP09.phx.gbl...

Try setting the Command object's connection property before ExecuteAndSend:  
  
command.Connection = connection;  
  
--  
  
Hope this helps.  
  
Dan Guzman  
  
SQL Server MVP  
  
"Michael Lang" <mickblang@keinSpam*hotmail.com> wrote in message  
  
news:e$eBxqjNGHA.1312@TK2MSFTNGP09.phx.gbl...

OK to get straight to the point, is it possible to use CLR stored procs from  
  
SQLExpress instance or from an MDF external database file ?  
  
If the answer is "no and no" then there's no need to read on, otherwise  
  
please read on.....  
  
I have a very simple stored proc...  
  
[Microsoft.SqlServer.Server.SqlProcedure]  
  
public static void GetCustomer(int customerid)  
  
{  
  
using (SqlConnection connection = new SqlConnection("context  
  
connection=true"))  
  
{  
  
connection.Open();  
  
SqlCommand command = new SqlCommand("Select * from Customer  
  
where CustomerID = " + customerid);  
  
SqlContext.Pipe.ExecuteAndSend(command);  
  
}  
  
}  
  
I have attempted to call this from within a database within SQLExpress and  
  
also from within an MDF external database file with results as follows.....  
  
----------------------------------SQLEXPRESS  
  
CASE--------------------------------------------------  
  
I've switched on CLR for SQLExpress by going to the SQL Server 2005 Surface  
  
Area Configuration selecting CLR Integration and checking "Enable CLR  
  
integration".  
  
When I attempt to call this stored proc within a database inside my  
  
SQLExpress instance I get...  
  
System.Data.SqlClient.SqlException was caught  
  
Message="A .NET Framework error occurred during execution of user defined  
  
routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:  
  
The context connection is already in  
  
use.\r\nSystem.InvalidOperationException: \r\n   at  
  
System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n   at  
  
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString  
  
options, Object providerInfo, DbConnection owningConnection)\r\n   at  
  
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions  
  
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection  
  
owningConnection)\r\n   at  
  
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection  
  
owningConnection, DbConnectionPoolGroup poolGroup)\r\n   at  
  
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection  
  
owningConnection)\r\n   at  
  
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection  
  
outerConnection, DbConnectionFactory connectionFactory)\r\n   at  
  
System.Data.SqlClient.SqlConnection.Open()\r\n   at  
  
Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n  
  
at StoredProcedures.GetCustomer(String customerid)\r\n."  
  
Source=".Net SqlClient Data Provider"  
  
ErrorCode=-2146232060  
  
Class=16  
  
LineNumber=0  
  
Number=6522  
  
Procedure="GetCustomer"  
  
Server=".\\SQLExpress"  
  
State=1  
  
StackTrace:  
  
at System.Data.SqlClient.SqlConnection.OnError(SqlException  
  
exception, Boolean breakConnection)  
  
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException  
  
exception, Boolean breakConnection)  
  
at  
  
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject  
  
stateObj)  
  
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,  
  
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet  
  
bulkCopyHandler, TdsParserStateObject stateObj)  
  
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()  
  
at System.Data.SqlClient.SqlDataReader.get_MetaData()  
  
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader  
  
ds, RunBehavior runBehavior, String resetOptionsString)  
  
at  
  
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)  
  
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,  
  
DbAsyncResult result)  
  
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)  
  
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior  
  
behavior, String method)  
  
at  
  
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior  
  
behavior)  
  
at  
  
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior  
  
behavior)  
  
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,  
  
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String  
  
srcTable, IDbCommand command, CommandBehavior behavior)  
  
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32  
  
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,  
  
CommandBehavior behavior)  
  
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)  
  
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)  
  
in  
  
E:\DEV.NET2.0\Test\Windows\WindowsApplication1\WindowsApplication1\Form1.cs:line  
  
35  
  
I also get this same exception when I EXEC the proc from within a Management  
  
Studio query window or from my test windows application.  
  
I have no problems calling  
  
[Microsoft.SqlServer.Server.SqlProcedure]  
  
public static void GetCustomer(int customerid)  
  
{  
  
SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");  
  
}  
  
----------------------------------END SQLEXPRESS  
  
CASE--------------------------------------------------  
  
----------------------------------STANDALONE MDF  
  
CASE--------------------------------------------------  
  
When I attempt to call it from a standalone MDF file I get...  
  
System.Data.SqlClient.SqlException was caught  
  
Message="Execution of user code in the .NET Framework is disabled. Enable  
  
\"clr enabled\" configuration option."  
  
Source=".Net SqlClient Data Provider"  
  
ErrorCode=-2146232060  
  
Class=16  
  
LineNumber=1  
  
Number=6263  
  
Procedure=""  
  
Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"  
  
State=1  
  
StackTrace:  
  
at System.Data.SqlClient.SqlConnection.OnError(SqlException  
  
exception, Boolean breakConnection)  
  
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException  
  
exception, Boolean breakConnection)  
  
at  
  
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject  
  
stateObj)  
  
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,  
  
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet  
  
bulkCopyHandler, TdsParserStateObject stateObj)  
  
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()  
  
at System.Data.SqlClient.SqlDataReader.get_MetaData()  
  
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader  
  
ds, RunBehavior runBehavior, String resetOptionsString)  
  
at  
  
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)  
  
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,  
  
DbAsyncResult result)  
  
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior  
  
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)  
  
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior  
  
behavior, String method)  
  
at  
  
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior  
  
behavior)  
  
at  
  
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior  
  
behavior)  
  
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,  
  
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String  
  
srcTable, IDbCommand command, CommandBehavior behavior)  
  
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32  
  
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,  
  
CommandBehavior behavior)  
  
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)  
  
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)  
  
in  
  
E:\DEV.NET2.0\Test\Windows\WindowsApplication1\WindowsApplication1\Form1.cs:line  
  
37  
  
It's pretty clear I need to switch on CLR integration for my MDF but how ?  
  
----------------------------------END STANDALONE MDF  
  
CASE--------------------------------------------------  
  
Has anyone else encountered these problems ?  
  
Do you have a solution ?  
  
Thanks in advance...  
  
Michael

You need to enable CLR at the *instance* level, not the database level.  If  
  
you are using a user instance as Bill suggested, you'll need to enable it  
  
during initialization of your app.  This is because SQL Server Express  
  
creates a new instance from the SQL Express template files each time you  
  
connect with 'User Instance=true'.  After connecting:  
  
SqlCommand command = new SqlCommand(  
  
"EXEC sp_configure 'clr enabled', 1 RECONFIGURE",  
  
connection);  
  
command.ExecuteNonQuery();  
  
--  
  
Hope this helps.  
  
Dan Guzman  
  
SQL Server MVP  
  
"Michael Lang" <mickblang@keinSpam*hotmail.com> wrote in message  
  
news:Ogxd3YoNGHA.3864@TK2MSFTNGP10.phx.gbl...

You're right. It is a bit goofy (that's a technical term I picked up in  
  
Orlando). The new feature (User Instances) has a few wrinkles that they're  
  
working out. The fact that you're allowed to do this at all (it's an admin  
  
task) is testament to the way the user instance of SS is created and owned.  
  
The other question you (and everyone) need to ask is do I have a problem  
  
that MUST be solved by a CLR executable. There are few good reasons to use  
  
them... just be aware of these issues before going crazy with the new shiny  
  
feature.  
  
--  
  
____________________________________  
  
William (Bill) Vaughn  
  
Author, Mentor, Consultant  
  
Microsoft MVP  
  
INETA Speaker  
  
www.betav.com/blog/billva  
  
www.betav.com  
  
Please reply only to the newsgroup so that others can benefit.  
  
This posting is provided "AS IS" with no warranties, and confers no rights.  
  
__________________________________  
  
"Michael Lang" <mickblang@keinSpam*hotmail.com> wrote in message  
  
news:%23y4Fm7uNGHA.428@tk2msftngp13.phx.gbl...

Thanks....  
  
This fixed the prob with using SQLExpress .... I did have a break point in  
  
the stored proc, I've only just realised it wasn't hitting it for some  
  
reason but thats another issue.  
  
I'm still at a loss though how I enable the CLR for an MDF file.  
  
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message  
  
news:%23pyO4GkNGHA.536@TK2MSFTNGP09.phx.gbl...

Hey thanks for the reply...  
  
I did have CLR enabled for SQLExpress.  I don't know of anyway to turn it on  
  
for an MDF.  I know you can set it in code for SQLExpress using...  
  
EXEC sp_configure 'show advanced options' , '1';  
  
go  
  
reconfigure;  
  
go  
  
EXEC sp_configure 'clr enabled' , '1'  
  
go  
  
reconfigure;  
  
EXEC sp_configure 'show advanced options' , '1';  
  
go  
  
But correct me if I'm mistaken but these system procs rnt available in an  
  
MDF.  So does this mean T-SQL only for MDF database's (not hosted by an  
  
instance of SQLServer) ?  
  
"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message  
  
news:eQQPd9jNGHA.3360@TK2MSFTNGP15.phx.gbl...

Thanks...  
  
This did work.  I must say I find it quite strange this needs to be done in  
  
code, when it's really a one off administrative setting.  
  
I did actually try to call this stored proc from a query window within  
  
VS.NET.  It seems whenever I use EXEC from a query window in VS.NET (go to  
  
server explorer, right click a Data Connection select "New Query") I get an  
  
error "The EXEC SQL construct or statement is not supported.".  Strangely  
  
enough it does seem to continue on and call the stored proc anyhow, results  
  
do appear for the proc.  In the case of sp_configure however it does seem to  
  
have failed....  I mistakenly mistook this failure as an indication that  
  
this system proc wasn't actually available within the "User Instance"  
  
database file.  
  
As you might tell I'm new to this, I'm finding that the errors and behavior  
  
of the tools, don't seem to be quite as intuitive as they were with VS  
  
2002/2003....  
  
"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message  
  
news:u5PGWruNGHA.524@TK2MSFTNGP09.phx.gbl...