How to Create CLR Based Stored Procedures

I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.

O sure it does, its located under File --> New --> Project --> Visual C# --> Database --> SQL Server Project.

If its not located in there you have to reinstall the Visual Studio template from the setup file.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.

What is the best way to do this inside a CLR procedure?

Thanx a lot

Hi!

Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).

|||Thank you,

I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block

Any idea about that?

I will try to build a small example an post it here.|||

Hello,
I made a small example to reproduce the error.

CLR procedure
<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub Test(ByVal sConnectString As String)

Dim t As SqlClient.SqlTransaction = Nothing

Dim c As SqlClient.SqlConnection = Nothing

c = New SqlClient.SqlConnection(sConnectString)

c.Open()

t = c.BeginTransaction()

t.Commit()

c.Close()

End Sub

T-SQL caller:
begin tran
execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
commit tran

Error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
System.Data.SqlClient.SqlException: Transaction context in use by another session.
System.Data.SqlClient.SqlException:
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.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

|||

Hi!

Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.

More on "Enlist" option is in doc on SqlConnection.ConnectionString property

How to create a CLR integrated trigger on table in schema?

In CLR integrated trigger:

If I want to make a trigger:
- For Insert
- With name: NewEmployeeInserted
- On table dbo.Employees

I add the following attribute above the desired .net method logic:
[SqlTrigger(Event = "For Insert", Name = "NewEmployeeInserted", Target = "Employees")]

How to make a trigger on for example: Production.Employees table?
where Production is the schema where this table resides.

Thank you.

Hi,

this was discussed in an earlier thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=132062&SiteID=1

Normally you should be able to only prefix the Schema before the table target.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de