CLR Trigger Exception
-
This is the exception I'm getting but I'm not sure I understand it. My take on it is that when trying to get the Trigger Context it causes the IndexOutOfRangeException. Why the hell it does that is beyond me though.
I have included the exception I got and the code for the trigger.
Can anyone help me please?
Cheers
Stu
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Runtime.InteropServices.COMException: A .NET Framework error occurred during execution of user defined routine or aggregate 'TR_Client':
System.IndexOutOfRangeException: Index was outside the bounds of the array.
System.IndexOutOfRangeException:
at System.Data.SqlServer.Internal.ClrLevelContext.GetTriggerDataImpl(SmiEventSink sink, TriggerAction& eTriggerAction, Boolean[]& rgfColumnsUpdated, XvarBlobStream& blobStream, SqlXml& xmlEventData)
at System.Data.SqlServer.Internal.ClrLevelContext.GetTriggerInfo(SmiEventSink eventSink, Boolean[]& columnsUpdated, TriggerAction& action, SqlXml& eventData)
at Microsoft.SqlServer.Server.SqlContext.get_InstanceTriggerContext()
at Microsoft.SqlServer.Server.SqlContext.get_TriggerContext()
at Ariel.SQLCLR.Triggers.TR_Client()
and here's the code for the trigger itself.
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using Microsoft.SqlServer.Server;
namespace Ariel.SQLCLR
{
public partial class Triggers
{
[SqlTrigger(Name = "TR_Client", Target = "client", Event = "FOR INSERT, UPDATE")]
public static void TR_Client()
{
string sTableName = "client";
string sColumnIDName = "clientid";
SqlTriggerContext trigContext = SqlContext.TriggerContext;
StringBuilder sb = new StringBuilder();
switch (trigContext.TriggerAction)
{
case TriggerAction.Insert:
case TriggerAction.Update:
using (SqlConnection dbConn = new SqlConnection("context connection=true"))
{
using (SqlCommand dbCmd = dbConn.CreateCommand())
{
////SQL Data Reader
dbCmd.CommandText = "SELECT " + sColumnIDName + " as ID FROM inserted";
dbCmd.CommandType = System.Data.CommandType.Text;
dbConn.Open();
SqlDataReader sdr = dbCmd.ExecuteReader();
bool bFirst = true;
if (sdr.HasRows)
{
sb.Append(string.Format("[{0}].[{1}] In (", sTableName, sColumnIDName));
while (sdr.Read())
{
if (bFirst)
{
sb.Append(sdr[0].ToString());
bFirst = false;
}
else
sb.Append(", " + sdr[0].ToString());
}
sb.Append(") ");
}
sdr.Close();
dbConn.Close();
if (sb.Length > 0)
{
//SqlContext.Pipe.Send(sb.ToString());
SQLCLRManager.QueueSQLInsertUpdateMessage(sTableName, sb.ToString());
}
}
}
break;
case TriggerAction.Delete:
using (SqlConnection dbConn = new SqlConnection("context connection=true"))
{
using (SqlCommand dbCmd = dbConn.CreateCommand())
{
////SQL Data Reader
dbCmd.CommandText = "SELECT " + sColumnIDName + " as ID FROM deleted";
dbCmd.CommandType = System.Data.CommandType.Text;
dbConn.Open();
SqlDataReader sdr = dbCmd.ExecuteReader();
Dictionary<string, string> lDeletedIDs = new Dictionary<string, string>();
if (sdr.HasRows)
{
while (sdr.Read())
{
lDeletedIDs.Add(sdr[0].ToString(), "");
}
}
sdr.Close();
dbConn.Close();
if (lDeletedIDs.Count > 0)
{
//SqlContext.Pipe.Send(sb.ToString());
SQLCLRManager.QueueSQLDeleteMessage(sTableName, lDeletedIDs);
}
}
}
break;
default:
break;
}
}
}
}
-
If I may, what is this trigger doing?
-
I'm not a CLR expert, but I don't see anything in the code you posted that should throw an out of bounds error.
I'd also be interested in what the code does as well.
What is the SQLCLRManager? I searched for it and the only thing that comes up is this thread.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
The SQLCLRManager is a class that I have written that connects to my companies base framework that all of our code uses as a base.
The idea of the trigger is to inform the SQLCLRManager that an update has occured on one or more records and to send it through to a type of subscription module. User requests for data subscriptions are stored and when a trigger is fired the SQLCLRManager informs the subscribies of the record id that they will need to request.
I agree though that I can't see anything that would throw an index out of bounds exception. Its rather frustrating.
-
I figured the SQLCLRManager was custom. I think your error may be "bubbling up" from there. Are you using any COM components in the SQLCLRManager? I ask because INTEROPSERVICES is in the error message.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
I guess then that gives your application the ability to display 'refresh required' or to automatically refresh - a bit like some real time stuff i did once using using microsoft message que - probably a bit old school now.
Forgive me if I am way off - sounds interesting anyway
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
Hi Jack,
I'm not using any COM objects in the SQLCLRManager.
Also worth noting is that this has been working fine for several weeks.
C# Screw,
That is exactly what its for. Auto refresh in as near real time as possible. The application was polling the db for changes and on occasion missing some changes as well.
This should stop the polling and turn it into more of a push technology.
-
Hi
I dont think it helps with your error at all, and I fear the links might be a little out of date
but here is an example of creating MSMQ message from SQL Server [edit : that you can add to your table insert/update trigger]:
http://support.microsoft.com/kb/555070
And here is some more c# for consuming the messages
http://www.c-sharpcorner.com/UploadFile/klaus_salchner@hotmail.com/MSMQ11292005035019AM/MSMQ.aspx
(I had good success with real time using MSMQ - but it wass using VB6 + and PropertyBags)
Please don't shoot me for not addressing OP's error problem - just thought might be an alternative ...:ermm:
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
sbowell (12/10/2009)
Hi Jack,I'm not using any COM objects in the SQLCLRManager.
Also worth noting is that this has been working fine for several weeks.
C# Screw,
That is exactly what its for. Auto refresh in as near real time as possible. The application was polling the db for changes and on occasion missing some changes as well.
This should stop the polling and turn it into more of a push technology.
This is what SQL Server Service Broker and or BizTalk are intended to do. I wouldn't use SQLCLR for this, especially when you can use external activation or a SqlDependency in the application to handle the signal to begin processing.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url] -
Hey Jonathan,
I did have a play with Service Broker and couldn't get what I wanted out of it. I haven't a clue what BizTalk is or even how to use it.
I had heard of SQLDependency but could actually find how to implement it.
Can you suggest any sites or books which would have an example of what you are suggesting?
It would be most appreciated
Cheers
Stuart
-
Hi there
I think BizTalk will cost you quite allot ?
I would seriously look at Microsoft Message Que links I provided... for real time it works a treat & its free 🙂 You just need to create the message from your table insert/update trigger and then your application needs to listen to the que... thats about it ...
Cheers
C# Gnu
____________________________________________________
Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015 -
I will have a look mate, cheers
I'll let you know how I get on.
Stu
-
sbowell (12/13/2009)
...I did have a play with Service Broker and couldn't get what I wanted out of it. I haven't a clue what BizTalk is or even how to use it.
...
What did you try with Service Broker that you couldn't get it to do?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
I went through the tutorial in BOL but I got the impression (wrongly I guess) that it was to pump through changes to other SQL Servers. I wanted to update other applications with changes to the data in the formatted views that are setup, not from the base tables in raw data format. I couldn't find any other examples but I probably didn't look hard enough.
Do you have any examples or ideas as I couldn't even figure out how to connect to the Service Broker, let alone how to do anything with it...
Cheers
-
sbowell (12/23/2009)
I went through the tutorial in BOL but I got the impression (wrongly I guess) that it was to pump through changes to other SQL Servers. I wanted to update other applications with changes to the data in the formatted views that are setup, not from the base tables in raw data format. I couldn't find any other examples but I probably didn't look hard enough.Do you have any examples or ideas as I couldn't even figure out how to connect to the Service Broker, let alone how to do anything with it...
Cheers
Yep, see my blog site (http://www.MovingSQL.com), look for the the download .ZIP kit for my presentation "The Top 10 Reasons You Aren't Already Using Service Broker." Let me know if you have any questions.
{edit: fixed link & typos)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply