Multiple connections in a CLR trigger

Hello

Is there nay way that it is possible to connect to another database within a trigger…

My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server

Thanks for any ideas, solutions or hints you can give me..!!!

Hi

I developed one solution like that Try Referencing server Y in server X wthi sp_addlinkedserver and then directly try to make the update like this

update servery.database.dbo.tabley set yfield=xfield

from serverx.database.dbo.tablex

|||

I tried the suggested function but i didn’t help and I still didn’t figure out how to make it work

Cheers

|||Did you try to just instatiate a new sqlconnection object using a normal connection string ? Make sure that you will need to grant a special trust level (external) to the assembly to make it work.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I did try to instantiate a new sqlconnection object. But I sadly I don't know about the trust level which you are talking about... Could you give me further information have I could grant the trust level to the assembly? It would be highly appreciated

Thanks

|||This would be a quick sample:

// Create Table Table1

//(

// SomeInt INT

//)

//INSERT INTO Table1 VALUES (1)

//EXEC sp_configure 'clr enabled' , '1'

//ALTER DATABASE Northwind SET TRUSTWORTHY ON

//RECONFIGURE

// Enter existing table or view for the target and uncomment the attribute line

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = "Table1", Event = "FOR INSERT")]

public static void Trigger1()

{

SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Select 1";

conn.Open();

cmd.ExecuteNonQuery();

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti