NSExecuteRuleFiring returns Invalid Column Name

Question

Hello,

I'm getting the following errors when SQL-NS instance fires my rule:

Description: The running of the rule failed. Please check the rule.
EventParameters:
  Application Name: Messages
  Quantum ID: 30040
  Rule Firing ID: 21218
  Rule Name: MessageEventRule
  Notification Throttle: 1000
  Event Class ID: 1
  Subscription Class ID: 1
  Rule ID: 2
  Rule Action: EXEC stp_Insert_Message_Alerts
  Stored Procedure Name: [dbo].[NSFire2]

SqlServerError:
  Error Number: 0
  Source: .Net SqlClient Data Provider
  Number: 207
  State: 1
  Class: 16
  Server: NOCMON-DEV
  Message: Invalid column name 'Date'.
  Procedure: 
  Line Number: 16

  Error Number: 1
  Source: .Net SqlClient Data Provider
  Number: 207
  State: 1
  Class: 16
  Server: NOCMON-DEV
  Message: Invalid column name 'Date'.
  Procedure: 
  Line Number: 16

Description: Invalid column name 'Date'.
Invalid column name 'Date'.
InstanceName: NOCMon
ApplicationName: Messages
Component: Generator
Thread: 4


For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

My ADF looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ApplicationDefinitionFileSchema">

  <!-- Version -->

  <!-- Database Definition -->

  <!-- Event Classes -->
  <EventClasses>
    <EventClass>
      <EventClassName>NOCMonMessageEvents</EventClassName>
      <Schema>
        <Field>
          <FieldName>SourceID</FieldName>
          <FieldType>int</FieldType>
        </Field>
		<Field>
		  <FieldName>ErrorCategoryID</FieldName>
		  <FieldType>int</FieldType>
		</Field>
        <Field>
          <FieldName>Date</FieldName>
          <FieldType>datetime</FieldType>
        </Field>
        <Field>
          <FieldName>Station</FieldName>
          <FieldType>nvarchar(50)</FieldType>
        </Field>
        <Field>
          <FieldName>Message</FieldName>
          <FieldType>nvarchar(max)</FieldType>
        </Field>
        <Field>
          <FieldName>StatusID</FieldName>
          <FieldType>int</FieldType>
        </Field>
		<Field>
		  <FieldName>nUser</FieldName>
		  <FieldType>nvarchar(50)</FieldType>
		</Field>
		<Field>
		  <FieldName>nSubject</FieldName>
		  <FieldType>nvarchar(max)</FieldType>
		</Field>
      </Schema>
      <IndexSqlSchema>
        <SqlStatement>
			CREATE INDEX myIndex
			ON NOCMonMessageEvents ( SourceID );
		</SqlStatement>
      </IndexSqlSchema>
		<Chronicles>
			<Chronicle>
				<ChronicleName>NOCMonMessageEventsChronicle</ChronicleName>
				<SqlSchema>
					<SqlStatement>
						IF EXISTS(SELECT name FROM dbo.sysobjects WHERE name = 'NOCMonMessageEventsChronicle')
						DROP TABLE dbo.NOCMonMessageEventsChronicle
						CREATE TABLE NOCMonMessageEventsChronicle
						( [SourceID] int,
						[ErrorCategoryID] int,
						[Date] DateTime,
						[Station] nvarchar(50),
						[Message] nvarchar(max),
						[StatusID] int,
						[nUser] nvarchar(50),
						[nSubject] nvarchar(max))
					</SqlStatement>
				</SqlSchema>
			</Chronicle>
		</Chronicles>
		<ChronicleRule>
			<RuleName>NOCMonMessageEventsChronicleRule</RuleName>
			<Action>
				INSERT INTO NOCMonMessageEventsChronicle (SourceID, ErrorCategoryID, Date, Station, Message, StatusID, [nUser], nSubject)
				SELECT	E.SourceID, E.ErrorCategoryID, E.Date, E.Station, E.Message, E.StatusID, E.[nUser], E.nSubject
				FROM	NOCMonMessageEvents E
			</Action>
			<ActionTimeout>PT10M</ActionTimeout>
		</ChronicleRule>
    </EventClass>
  </EventClasses>

  <!-- Subscription Classes -->
  <SubscriptionClasses>
    <SubscriptionClass>
      <SubscriptionClassName>NOCMonMessageEventSubscription</SubscriptionClassName>
      <Schema>
        <Field>
          <FieldName>DeviceName</FieldName>
          <FieldType>nvarchar(255)</FieldType>
        </Field>
        <Field>
          <FieldName>SubscriberLocale</FieldName>
          <FieldType>nvarchar(10)</FieldType>
        </Field>
		  <Field>
			  <FieldName>SourceID</FieldName>
			  <FieldType>int</FieldType>
		  </Field>
		  <Field>
			  <FieldName>ErrorCategoryID</FieldName>
			  <FieldType>int</FieldType>
		  </Field>
		  <Field>
			  <FieldName>Station</FieldName>
			  <FieldType>nvarchar(50)</FieldType>
		  </Field>
		<Field>
		  <FieldName>Description</FieldName>
		  <FieldType>nvarchar(max)</FieldType>
		</Field>
		  <Field>
			  <FieldName>Threshold</FieldName>
			  <FieldType>int</FieldType>
		  </Field>
		  <Field>
			  <FieldName>TimeOfDayRangeBegin</FieldName>
			  <FieldType>nvarchar(50)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>TimeOfDayRangeEnd</FieldName>
			  <FieldType>nvarchar(50)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>TimeBuffer</FieldName>
			  <FieldType>nvarchar(50)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>AlarmGrouping</FieldName>
			  <FieldType>bit</FieldType>
		  </Field>
		  <Field>
			  <FieldName>GroupingTime</FieldName>
			  <FieldType>nvarchar(50)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>ElementContainer</FieldName>
			  <FieldType>nvarchar(MAX)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>AcknowledgeNotification</FieldName>
			  <FieldType>bit</FieldType>
		  </Field>
		  <Field>
			  <FieldName>SecondaryNotification</FieldName>
			  <FieldType>nvarchar(MAX)</FieldType>
		  </Field>
		  <Field>
			  <FieldName>TertiaryNotification</FieldName>
			  <FieldType>nvarchar(MAX)</FieldType>
		  </Field>
      </Schema>
      <EventRules>
        <EventRule>
          <RuleName>MessageEventRule</RuleName>
          <EventClassName>NOCMonMessageEvents</EventClassName>
			<ConditionAction>
			  <SqlLogin>NSRulesEvaluator</SqlLogin>
			  <SqlUser>NSRulesEvaluator</SqlUser>
			  <InputName>NOCMonMessageEvents</InputName>
				<SqlExpression>
					EXEC stp_Insert_Message_Alerts
				</SqlExpression>
			</ConditionAction>
        </EventRule>
      </EventRules>
    </SubscriptionClass>
	  <SubscriptionClass>
		  <SubscriptionClassName>ScheduledSubscriptions</SubscriptionClassName>
		  <Schema>
			  <Field>
				  <FieldName>DeviceName</FieldName>
				  <FieldType>nvarchar(255)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>SubscriberLocale</FieldName>
				  <FieldType>nvarchar(10)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>SourceID</FieldName>
				  <FieldType>int</FieldType>
			  </Field>
			  <Field>
				  <FieldName>ErrorCategoryID</FieldName>
				  <FieldType>int</FieldType>
			  </Field>
			  <Field>
				  <FieldName>Station</FieldName>
				  <FieldType>nvarchar(50)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>Description</FieldName>
				  <FieldType>nvarchar(max)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>Threshold</FieldName>
				  <FieldType>int</FieldType>
			  </Field>
			  <Field>
				  <FieldName>TimeOfDayRangeBegin</FieldName>
				  <FieldType>nvarchar(50)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>TimeOfDayRangeEnd</FieldName>
				  <FieldType>nvarchar(50)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>TimeBuffer</FieldName>
				  <FieldType>nvarchar(50)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>AlarmGrouping</FieldName>
				  <FieldType>bit</FieldType>
			  </Field>
			  <Field>
				  <FieldName>GroupingTime</FieldName>
				  <FieldType>nvarchar(50)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>ElementContainer</FieldName>
				  <FieldType>nvarchar(MAX)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>AcknowledgeNotification</FieldName>
				  <FieldType>bit</FieldType>
			  </Field>
			  <Field>
				  <FieldName>SecondaryNotification</FieldName>
				  <FieldType>nvarchar(MAX)</FieldType>
			  </Field>
			  <Field>
				  <FieldName>TertiaryNotification</FieldName>
				  <FieldType>nvarchar(MAX)</FieldType>
			  </Field>
		  </Schema>
		  <ScheduledRules>
			  <ScheduledRule>
				  <RuleName>ScheduledSubscriptionsRule</RuleName>
				  <ConditionAction>
					  <SqlLogin>NSRulesEvaluator</SqlLogin>
					  <SqlUser>NSRulesEvaluator</SqlUser>
					  <InputName>NOCMonMessageEventsChronicle</InputName>
					  <SqlExpression>
						  INSERT INTO ScheduledNotifications(
						  SubscriberId,
						  DeviceName,
						  SubscriberLocale,
						  Description,
						  SourceID,
						  ErrorCategoryID,
						  Date,
						  Station,
						  Message,
						  StatusID,
						  [nUser],
						  nSubject)
						  SELECT r.[Subscription.SubscriberId],
						  r.[Subscription.DeviceName],
						  r.[Subscription.SubscriberLocale],
						  r.[Subscription.Description],
						  r.[Input.SourceID],
						  r.[Input.ErrorCategoryID],
						  r.[Input.Date],
						  r.[Input.Station],
						  r.[Input.Message],
						  r.[Input.StatusID],
						  r.[Input.nUser],
						  r.[Input.nSubject]
						  FROM ScheduledSubscriptionsRule r;
					  </SqlExpression>
				  </ConditionAction>
			  </ScheduledRule>
		  </ScheduledRules>

	  </SubscriptionClass>
  </SubscriptionClasses>

  <!-- Notification Classes -->
  <NotificationClasses>
    <NotificationClass>
      <NotificationClassName>
        MessageAlerts
      </NotificationClassName>
      <Schema>
        <Fields>
          <Field>
            <FieldName>SourceID</FieldName>
            <FieldType>int</FieldType>
          </Field>
		  <Field>
			<FieldName>Source</FieldName>
			<FieldType>varchar(max)</FieldType>
		  </Field>
		<Field>
			<FieldName>ErrorCategoryID</FieldName>
			<FieldType>int</FieldType>
		</Field>
			<Field>
				<FieldName>ErrorText</FieldName>
				<FieldType>varchar(max)</FieldType>
			</Field>
          <Field>
            <FieldName>Date</FieldName>
            <FieldType>datetime</FieldType>
          </Field>
          <Field>
            <FieldName>Station</FieldName>
            <FieldType>nvarchar(50)</FieldType>
          </Field>
          <Field>
            <FieldName>Message</FieldName>
            <FieldType>nvarchar(max)</FieldType>
          </Field>
          <Field>
            <FieldName>StatusID</FieldName>
			  <FieldType>int</FieldType>
          </Field>
			<Field>
				<FieldName>Status</FieldName>
				<FieldType>varchar(max)</FieldType>
			</Field>
		  <Field>
			<FieldName>nUser</FieldName>
			<FieldType>nvarchar(50)</FieldType>
		  </Field>
			<Field>
				<FieldName>nSubject</FieldName>
				<FieldType>nvarchar(max)</FieldType>
			</Field>
		  <Field>
			<FieldName>Description</FieldName>
			<FieldType>nvarchar(max)</FieldType>
		  </Field>
        </Fields>
      </Schema>
      <ContentFormatter>
        <ClassName>XsltFormatter</ClassName>
        <Arguments>
          <Argument>
            <Name>XsltBaseDirectoryPath</Name>
            <Value>%_AppPath_%</Value>
          </Argument>
          <Argument>
            <Name>XsltFileName</Name>
            <Value>MessageTransform.xslt</Value>
          </Argument>
        </Arguments>
      </ContentFormatter>
      <Protocols>
        <Protocol>
          <ProtocolName>SMTP</ProtocolName>
          <Fields>
            <Field>
              <FieldName>Subject</FieldName>
              <SqlExpression>'NOCMon Notification: '+CONVERT (NVARCHAR(30), GETDATE())</SqlExpression>
            </Field>
            <Field>
              <FieldName>BodyFormat</FieldName>
              <SqlExpression>'html'</SqlExpression>
            </Field>
            <Field>
              <FieldName>From</FieldName>
              <SqlExpression>'[e-mail]'</SqlExpression>
            </Field>
            <Field>
              <FieldName>Priority</FieldName>
              <SqlExpression>'Normal'</SqlExpression>
            </Field>
            <Field>
              <FieldName>To</FieldName>
              <SqlExpression>DeviceAddress</SqlExpression>
            </Field>
          </Fields>
        </Protocol>
      </Protocols>
    </NotificationClass>
	  <NotificationClass>
		  <NotificationClassName>ScheduledNotifications</NotificationClassName>
		  <Schema>
			  <Fields>
				  <Field>
					  <FieldName>SourceID</FieldName>
					  <FieldType>int</FieldType>
				  </Field>
				  <Field>
					  <FieldName>ErrorCategoryID</FieldName>
					  <FieldType>int</FieldType>
				  </Field>
				  <Field>
					  <FieldName>Date</FieldName>
					  <FieldType>datetime</FieldType>
				  </Field>
				  <Field>
					  <FieldName>Station</FieldName>
					  <FieldType>nvarchar(50)</FieldType>
				  </Field>
				  <Field>
					  <FieldName>Message</FieldName>
					  <FieldType>nvarchar(max)</FieldType>
				  </Field>
				  <Field>
					  <FieldName>StatusID</FieldName>
					  <FieldType>int</FieldType>
				  </Field>
				  <Field>
					  <FieldName>nUser</FieldName>
					  <FieldType>nvarchar(50)</FieldType>
				  </Field>
				  <Field>
					  <FieldName>nSubject</FieldName>
					  <FieldType>nvarchar(max)</FieldType>
				  </Field>
				  <Field>
					  <FieldName>Description</FieldName>
					  <FieldType>nvarchar(max)</FieldType>
				  </Field>
			  </Fields>
		  </Schema>
		  <ContentFormatter>
			  <ClassName>XsltFormatter</ClassName>
			  <Arguments>
				  <Argument>
					  <Name>XsltBaseDirectoryPath</Name>
					  <Value>%_AppPath_%</Value>
				  </Argument>
				  <Argument>
					  <Name>XsltFileName</Name>
					  <Value>MessageTransform.xslt</Value>
				  </Argument>
			  </Arguments>
		  </ContentFormatter>
		  <DigestDelivery>true</DigestDelivery>
		  <Protocols>
			  <Protocol>
				  <ProtocolName>SMTP</ProtocolName>
				  <Fields>
					  <Field>
						  <FieldName>Subject</FieldName>
						  <SqlExpression>'NOCMon Notification: '+CONVERT (NVARCHAR(30), GETDATE())</SqlExpression>
					  </Field>
					  <Field>
						  <FieldName>BodyFormat</FieldName>
						  <SqlExpression>'html'</SqlExpression>
					  </Field>
					  <Field>
						  <FieldName>From</FieldName>
						  <SqlExpression>'[e-mail]'</SqlExpression>
					  </Field>
					  <Field>
						  <FieldName>Priority</FieldName>
						  <SqlExpression>'Normal'</SqlExpression>
					  </Field>
					  <Field>
						  <FieldName>To</FieldName>
						  <SqlExpression>DeviceAddress</SqlExpression>
					  </Field>
				  </Fields>
			  </Protocol>
		  </Protocols>
		  <ExpirationAge>PT2H</ExpirationAge>
	  </NotificationClass>
  </NotificationClasses>

  <!-- Event Providers -->
  <Providers>
    <NonHostedProvider>
      <ProviderName>MessageSPEventProvider</ProviderName>
    </NonHostedProvider>
    <NonHostedProvider>
      <ProviderName>SQLTriggerEventProvider</ProviderName>
    </NonHostedProvider>
  </Providers>

  <!-- Generator -->
  <Generator>
    <SystemName>%_NSServer_%</SystemName>
  </Generator>

  <!-- Distributors -->
  <Distributors>
    <Distributor>
      <SystemName>%_NSServer_%</SystemName>
      <QuantumDuration>PT15S</QuantumDuration>
    </Distributor>
  </Distributors>

  <!-- ApplicationExecutionSettings -->
  <ApplicationExecutionSettings>
    <QuantumDuration>PT15S</QuantumDuration>
    <DistributorLogging>
      <LogBeforeDeliveryAttempts>false</LogBeforeDeliveryAttempts>
      <LogStatusInfo>false</LogStatusInfo>
      <LogNotificationText>false</LogNotificationText>
    </DistributorLogging>
	  <PerformanceQueryInterval>PT5S</PerformanceQueryInterval>
	  <SubscriptionQuantumLimit>1</SubscriptionQuantumLimit>
	  <ChronicleQuantumLimit>1</ChronicleQuantumLimit>
    <Vacuum>
      <RetentionAge>P1D</RetentionAge>
      <VacuumSchedule>
        <Schedule>
          <StartTime>23:00:00</StartTime>
          <Duration>P0DT02H00M00S</Duration>
        </Schedule>
      </VacuumSchedule>
    </Vacuum>
  </ApplicationExecutionSettings>

</Application>

The NOCMonMessageEvents Condition Action stored procedure looks like this:

ALTER PROCEDURE [dbo].[stp_Insert_Message_Alerts]

AS

DECLARE @matchID int

BEGIN

	SET NOCOUNT ON;

	INSERT INTO MessageAlerts(SubscriberId, DeviceName, SubscriberLocale, [Description], SourceID, Source, ErrorCategoryID, ErrorText, Date, Station, [Message], StatusID, [Status], nUser, nSubject)
	SELECT r.[Subscription.SubscriberId],
	r.[Subscription.DeviceName],
	r.[Subscription.SubscriberLocale],
	r.[Subscription.Description],
	r.[Input.SourceID],
	SRC.SourceName,
	r.[Input.ErrorCategoryID],
	ERR.ErrText,
	r.[Input.Date],
	r.[Input.Station],
	r.[Input.Message],
	r.[Input.StatusID],
	STAT.[LongDesc],
	r.[Input.nUser],
	r.[Input.nSubject]
	FROM MessageEventRule r
	INNER JOIN tblErrCategory_Sup ERR
		ON ERR.ErrorCategoryID = r.[Input.ErrorCategoryID]
	INNER JOIN tblSource_Sup SRC
		ON SRC.SourceID = r.[Input.SourceID]
	INNER JOIN tblStatus_Sup STAT
		ON STAT.StatusID = r.[Input.StatusID]

	SELECT @matchID = ErrorCategoryID 
	FROM MessageEventRule MER
	INNER JOIN ErrorMatchClearings EMATCH
		ON EMATCH.errorCategoryID = MER.[Input.ErrorCategoryID]

	IF @matchID IS NOT NULL
		BEGIN

			INSERT INTO ErrorMatches(SubscriberId, DeviceName, SubscriberLocale, [Description], SourceID, ErrorCategoryID, Date, Station, [Message], StatusID, nUser, nSubject)
			SELECT r.[Subscription.SubscriberId],
			r.[Subscription.DeviceName],
			r.[Subscription.SubscriberLocale],
			r.[Subscription.Description],
			r.[Input.SourceID],
			r.[Input.ErrorCategoryID],
			r.[Input.Date],
			r.[Input.Station],
			r.[Input.Message],
			r.[Input.StatusID],
			r.[Input.nUser],
			r.[Input.nSubject]
			FROM MessageEventRule r
			WHERE r.[Input.ErrorCategoryID] = @matchID

		END


END

The vb.net I am using to create the subscription (and I suspect this is where I'm doing something wrong?):

        Private Function GetSubscriptionData(ByRef e As Telerik.Web.UI.GridCommandEventArgs) As EventSubscription

            Dim editedItem As GridEditableItem = CType(e.Item, GridEditableItem)
            Dim s As New EventSubscription(EventSubscription.UserName)


            Dim svr As sqlsmo.Server = New sqlsmo.Server("MyServer")
            svr.ConnectionContext.LoginSecure = False

            svr.ConnectionContext.Login = "mylogin"
            svr.ConnectionContext.Password = "mypassword"

            Dim nocmonDB As sqlsmo.Database = svr.Databases("NOCMonMessages")
            Dim errorMatchClearings As sqlsmo.Table = nocmonDB.Tables("ErrorMatchClearings")
            Dim errorMatches As sqlsmo.Table = nocmonDB.Tables("ErrorMatches")
            Dim eventView As sqlsmo.View = nocmonDB.Views("NOCMonMessageEvents")


            Dim sourceDropDown As DropDownList = e.Item.FindControl("sourceDropdown")
            Dim errorDropDown As DropDownList = e.Item.FindControl("errorDropdown")
            Dim stationDropDown As DropDownList = e.Item.FindControl("stationDropdown")
            Dim txtDescription As TextBox = e.Item.FindControl("txtDescription")
            Dim txtThreshold As TextBox = e.Item.FindControl("txtThreshold")
            Dim txtTimeOfDayRangeBegin As TextBox = e.Item.FindControl("txtTimeOfDayRangeBegin")
            Dim txtTimeOfDayRangeEnd As TextBox = e.Item.FindControl("txtTimeOfDayRangeEnd")
            Dim txtTimeBuffer As TextBox = e.Item.FindControl("txtTimeBuffer")
            Dim txtGroupingTime As TextBox = e.Item.FindControl("txtGroupingTime")
            Dim txtElementContainer As TextBox = e.Item.FindControl("txtElementContainer")
            Dim txtSecondaryNotification As TextBox = e.Item.FindControl("txtSecondaryNotification")
            Dim txtTertiaryNotification As TextBox = e.Item.FindControl("txtTertiaryNotification")

            Dim strSource As String = sourceDropDown.SelectedItem.Value
            Dim strErrorText As String = errorDropDown.SelectedItem.Value
            Dim strStation As String = stationDropDown.SelectedItem.Text

            s.Station = strStation
            s.ErrorCategoryID = strErrorText
            s.SourceID = strSource

            Dim sourceOperator As nsr.SimpleOperator = New nsr.SimpleOperator
            Dim errorOperator As nsr.SimpleOperator = New nsr.SimpleOperator
            Dim stationOperator As nsr.SimpleOperator = New nsr.SimpleOperator

            If Trim(strSource) = "ANY" Then
                strSource = ""
                sourceOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.NotEquals
            Else
                sourceOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.Equals
            End If

            If Trim(strErrorText) = "67" Then
                strErrorText = ""
                errorOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.NotEquals
            Else
                errorOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.Equals
            End If

            If Trim(strStation) = "ANY" Then
                strStation = ""
                stationOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.NotEquals
            Else
                stationOperator = Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.Equals
            End If

<br/><br/><br/>            If txtTimeBuffer.Text <> "00:00:00" Then

                Dim clearingFilter As nsr.FilterRule = New nsr.FilterRule( _
                    New nsr.TableInputType(nocmonDB.Tables("ErrorMatches")), _
                        New nsr.FilterStatement( _
                            New nsr.SimpleLeafCondition( _
                                New nsr.FieldValue("Date"), _
                                    Microsoft.SqlServer.NotificationServices.Rules.SimpleOperator.LessThan, _
                                        New nsr.FunctionValue(nocmonDB.UserDefinedFunctions("TIMEBUFFER", "dbo"), _
                                        txtTimeBuffer.Text, New nsr.FieldValue("Date"))), _
                                            Microsoft.SqlServer.NotificationServices.Rules.FilterAction.Exclude))


                s.UserCondition = New nsr.AndCondition( _
                                    New nsr.SimpleLeafCondition( _
                                        New nsr.FieldValue("SourceID"), _
                                            sourceOperator, strSource), _
                                    New nsr.SimpleLeafCondition( _
                                        New nsr.FieldValue("ErrorCategoryID"), _
                                            errorOperator, strErrorText), _
                                    New nsr.SimpleLeafCondition( _
                                        New nsr.FieldValue("Station"), _
                                            stationOperator, strStation), _
                                    New nsr.LinkLeafCondition( _
                                          Microsoft.SqlServer.NotificationServices.Rules.LinkLeafOperator.Any, _
                                          clearingFilter, _
                                          New nsr.JoinClause( _
                                            eventView.Columns("ErrorCategoryID"), _
                                            errorMatches.Columns("ErrorCategoryID")), _
                                          New nsr.JoinClause( _
                                            errorMatches.Columns("ErrorCategoryID"), _
                                            errorMatchClearings.Columns("clearingErrorCategoryID"))))

            End If

            s.DeviceName = "myDevice"
            s.Locale = "en-us"
            s.Description = txtDescription.Text
            s.Threshold = txtThreshold.Text
            s.TimeOfDayRangeBegin = txtTimeOfDayRangeBegin.Text
            s.TimeOfDayRangeEnd = txtTimeOfDayRangeEnd.Text
            s.TimeBuffer = txtTimeBuffer.Text
            s.GroupingTime = txtGroupingTime.Text
            s.ElementContainer = txtElementContainer.Text
            s.SecondaryNotification = txtSecondaryNotification.Text
            s.TertiaryNotification = txtTertiaryNotification.Text

            Return s

        End Function

Note that where I am creating the clearingFilter, I specify ErrorMatches as the input table, yes ErrorMatches contains a field called Date, I tried changing the name of the field to fooDate, still I got the error, but updated to say could not find 'fooDate'.


When I use the debugging stored procedures and fire the rule I get:

Msg 207, Level 16, State 1, Line 16
Invalid column name 'Date'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'Date'.

Here is the XML for the Condition:

<NSRulesObject xmlns="http://www.microsoft.com/MicrosoftNotificationServices/RulesSchema">
<Condition>
	<And>
		<SimpleLeaf Operator="=">
			<Left>
				<Field Name="SourceID" />
			</Left>
			<Right>
				<Constant Type="System.String" Value="2" />
			</Right>
		</SimpleLeaf>
		<SimpleLeaf Operator="=">
			<Left>
				<Field Name="ErrorCategoryID" />
			</Left>
			<Right>
				<Constant Type="System.String" Value="51" />
			</Right>
		</SimpleLeaf>
		<SimpleLeaf Operator="&lt;&gt;">
			<Left>
				<Field Name="Station" />
			</Left>
			<Right>
				<Constant Type="System.String" Value="" />
			</Right>
		</SimpleLeaf>
		<LinkLeaf Operator="Any">
			<FilterRule>
				<InputType>
					<Table Database="NOCMonMessages" Schema="dbo" Name="ErrorMatches" />
				</InputType>
				<Statements>
					<FilterStatement Action="Exclude">
					<Condition>
						<SimpleLeaf Operator="&lt;">
							<Left>
								<Field Name="Date" />
							</Left>
							<Right>
								<Function Db="NOCMonMessages" Schema="dbo" Name="TIMEBUFFER">
									<Parameters>
										<Constant Type="System.String" Value="5" />
										<Field Name="Date" />
									</Parameters>
								</Function>
							</Right>
						</SimpleLeaf>
					</Condition>
					</FilterStatement>
				</Statements>
			</FilterRule>
			<LinkChain>
				<JoinClause SourceDb="NOCMonMessages" SourceSchema="dbo" SourceView="NOCMonMessageEvents" SourceColumn="ErrorCategoryID" 						TargetDb="NOCMonMessages" TargetSchema="dbo" TargetView="ErrorMatches" TargetColumn="ErrorCategoryID" />
				<JoinClause SourceDb="NOCMonMessages" SourceSchema="dbo" SourceView="ErrorMatches" SourceColumn="ErrorCategoryID" 							TargetDb="NOCMonMessages" TargetSchema="dbo" TargetView="ErrorMatchClearings" TargetColumn="clearingErrorCategoryID" />
			</LinkChain>
		</LinkLeaf>
	</And>
</Condition>
</NSRulesObject>

You can see the Date field referenced in the FilterRule.

The subscription saves just fine, so this all really boils down to the rule firing error.

I hope someone can help me or point me in the right direction.

Regards,
Trevor

Answer 1

Found the issue.

You can look at the NSFire2 stored procedure and run this line of code:

SELECT CommandText, ConditionSignatureId FROM [dbo].[NSConditionSignatures] WHERE RuleId=2

This will show you the CommandText.

In that command text I found the problem. ErrorMatches and ErrorMatchClearings aliases were reversed.

ErrorMatchClearings was not a necessary join so I removed it and that solved the problem!

Hope this helps someone else!

Regards,
Trevor