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,
Vinoth
[1723 byte] By [
Vinoth] at [2008-2-5]
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.
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.
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
