Hosting SQL Server 2005 As a Runtime Host

I am trying to use the new Common Language Runtime (CLR) hosting feature to write stored procedures in C#
i have added
Microsfot.sqlserevr.server name space
and ia m trying to use sqlContext Object as below

using (SqlConnection connection = new SqlConnection(dbConn))
{
connection.Open();
SqlCommand sqlcmd = new SqlCommand("select @.@. version", connection);
SqlContext.Pipe.ExecuteAndSend(sqlcmd);
}

i get the below error when i execute (SqlContext.Pipe.ExecuteAndSend(sqlcmd);)

System.InvalidOperationException was unhandled by user code
Message="The requested operation requires a SqlClr context, which is only available when running in the Sql Server process."
Source="System.Data"

i checked if (SqlContext.IsAvailable) and it returnsfalse as well.

Please le me know how to make it work.

Thanks

THNQDigital

Try to use context connection in this way:

using (SqlConnection connection = new SqlConnection("context connection=true"))

You can find an example here:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe(d=ide).aspx

And here is an article about context connection:

http://msdn2.microsoft.com/en-us/library/ms254981(d=ide).aspx

|||

Thank you Jay.

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

sqlContext.IsAvailable has to reurn true in order to confirm that managed code is running in the same process as sql server ( in process). For me 'sqlContext.IsAvailable' is returning false.

How do we acheive in process communication bewteen managed code ( c#) and sql server. In other words how do we make sqlContext.IsAvailable return true.

Please le me know Thanks for your help

THNQDigital

|||

THNQdigital:

But "context connecion = true", means we are not providing any user credential to login to sql server. The managed code has to run in the same process as SQL server right?.

Yes, I agree with you.

As I understand the sqlContext.IsAvailable should return true when the code is running inside SQL Server using common language runtime integration--that means in SQL you can create an assmebly pointing to the dll file compiled from your code, then create UDF or stored procedure to reference the code. You may take a look at this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp

|||

Hi Jay,

The Link you provided above is not taking me to the related topic. Could you please verify and re send me the correct link. I greatly appreciate your help. Thanks.

THNQDigital

|||

Sorry it's my fault, please try this one

http://msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp?frame=true