CLR Trigger

Hi,

I want to call webservice in the Trigger. How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.

And i worked out the following Example code for CLR Trigger.

using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;

namespace CLR
{
public class Class1
{
public static void InsertTrigger()
{
SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand cmd = SqlContext.GetCommand();
if (sqlTrigger.TriggerAction == TriggerAction.Insert)
{
cmd.CommandText = "Select * from Inserted";
SqlDataRecord dr = cmd.ExecuteRow();
string Subject_uri = dr[0].ToString();
string predicate = dr[1].ToString();
string Obj = dr[2].ToString();
sqlPipe.Execute(cmd);
sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj);
}
}

}
}

After that i created Assembly and Trigger and clr enabled

create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll'

CREATE TRIGGER InsertTrigger
ON Triplets
For Insert
As
External Name
CLR.[CLR.Class1].InsertTrigger

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'clr enabled', 1
RECONFIGURE

insert into Triplets values('test','_2','Testing1')

When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message.
How do i know whether my CLR trigger is working?
Thanks,
VinothMy entire body just shuddered! You want to call a Web Service in a database trigger? Do you realize what the performance hit will be for that?|||

To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.

If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.

I have to extra comments:
1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.

2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.

Hope this helps.

|||I don't think if there is any performance related problem because of using web services as far as it takes place in another thread. After all Notification services do the similar thing.|||

What makes you think that the Web Services call takes place on another thread?

|||

Web services calls don't take place on another thread. The application code can explicitly create a working thread and call any time-consuming tasks or blocking calls on that thread.

From the other hand I'm not sure even calling the "time-consuming tasks or blocking calls" directly from the triggered function causing performance hits, because I guess Sql Server engine is smart enough to call the callbacks (subscribed trigger methods) asynchronously (multithread call) not sequentially.

|||I think you will probably find that SQL Server will prevent new threads being created.

Can you imagine if a certain trigger created a new thread everytime it ran, and then a table was updated a 1000 times in a row? This would soon bring down the server.|||Have you tried that before?|||

By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:

SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code. UNSAFE: Access is not limited whatsoever.|||Just because you can do it, doesn't make it a good idea.|||

hi,

i am also doing the same type of thing.

i made a Dll in c#2005, which is consuming a Java Based web service.

then, i registered that Assembly within sql server 2005. then i created a function which use that Assmbly. then i created a stored procedure which is using that function.

Now, the Issue is the performance. if i hit the webservice from the windows Based Application. it works very fine.

but from Sql Server its performance is worst.

since Stored procedure is called by a Bill payment System. so this procedure receives more than 30000 calls per day. But most of the time the assmbly gets failed.

sometime i receive Error Like:

Can't load Assembly....

and sometime i receive error like:

The underlying connection was closed: An unexpected error occurred on a receive. .......System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond....

Anyone can help me to improve this performance issue.

Regards,

Vineet