<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
	<channel>
		<atom:link href="https://www.xlayer.co.za/forum/extern.php?action=feed&amp;tid=102&amp;type=rss" rel="self" type="application/rss+xml" />
		<title><![CDATA[SXI Forum / Another way to handle DELAYs for events]]></title>
		<link>https://www.xlayer.co.za/forum/viewtopic.php?id=102</link>
		<description><![CDATA[The most recent posts in Another way to handle DELAYs for events.]]></description>
		<lastBuildDate>Mon, 28 Oct 2019 13:19:06 +0000</lastBuildDate>
		<generator>FluxBB</generator>
		<item>
			<title><![CDATA[Re: Another way to handle DELAYs for events]]></title>
			<link>https://www.xlayer.co.za/forum/viewtopic.php?pid=163#p163</link>
			<description><![CDATA[<h5>Jobs in SQL Express</h5><p>The functionality of creating SQL Agent Jobs is not available in SQL Server Express Edition. An alternative is to execute a batch file which executes a SQL script using Windows Task Scheduler.</p><p>In order to do this first create a batch file named sqljob.bat</p><div class="codebox"><pre><code>sqlcmd -S servername -U username -P password -i path/of/sqljob.sql</code></pre></div><p>Replace the servername, username, password and path with the relevant values.</p><p>Then create the SQL Script file named sqljob.sql</p><div class="codebox"><pre><code>USE [databasename]
--T-SQL commands go here
GO</code></pre></div><p>Replace the [databasename] with your database name. The USE and GO is necessary when you write the SQL script.</p><p>sqlcmd is a command line utility to execute SQL scripts. After creating these two files execute the batch file using Windows Task Scheduler set to run every minute.</p>]]></description>
			<author><![CDATA[dummy@example.com (SeanR)]]></author>
			<pubDate>Mon, 28 Oct 2019 13:19:06 +0000</pubDate>
			<guid>https://www.xlayer.co.za/forum/viewtopic.php?pid=163#p163</guid>
		</item>
		<item>
			<title><![CDATA[Re: Another way to handle DELAYs for events]]></title>
			<link>https://www.xlayer.co.za/forum/viewtopic.php?pid=162#p162</link>
			<description><![CDATA[<h5>Add Delay events to MSSQL</h5><p>We can delay events in MSSQL in a very similar fashion as what was describes in the original post.&#160; The steps to perform are as follows:</p><ol class="decimal"><li><p>Make sure that <strong>SQL Server Agent(MSSQLSERVER)</strong> windows service is running.</p></li><li><p>In Microsoft SQL Server Management Studio expand the SQL Server Agent option at the very bottom of the &quot;Object Explorer&quot; pane.</p></li><li><p>Right Click on Jobs and select New Job... from the dropdown menu<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/JobsNew.png" alt="Create New Job" /></span></p></li><li><p>The following window will appear. Give your new Job a Name (E.g. SCOMDelay) - You can include a description at this point as well if required.<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_1.png" alt="New Job Window" /></span></p></li><li><p>Next, Click on the Steps Page (in the top left panel) and then Click on the &#039;New&#039; button at the bottom of the window.<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_2.png" alt="Create New Step" /></span></p></li><li><p>In the &#039;New Job Step&#039; window Provide a &#039;Step Name&#039; (E.g. CheckDelay).&#160; <strong>NOTE:</strong> Make sure you select the correct database from the &#039;Database:&#039; dropdown menu. In the Command textarea paste the following:</p><div class="codebox"><pre><code>UPDATE sigreg SET procFlag = &#039;0&#039; 
WHERE procFlag = &#039;999&#039; 
AND (SELECT DATEADD(minute, 5, epochTime)) &lt; GETDATE();</code></pre></div><p><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_3.png" alt="New Step Details" /></span><br /><strong>NOTE:</strong> this code uses a 5 min delay, however, you can change that to any number of minutes you require.<br />Then Click on &#039;OK&#039; - You will be taken back to the &#039;New Job&#039; window</p></li><li><p>Now select the &#039;Schedules&#039; Page (in the top left panel) abd Click on the &#039;New&#039; button at the bottom of the window.&#160; A &#039;New Job Schedule&#039; window will popup.</p></li><li><p>Give this new schedule a &#039;Name&#039; (E.g. SCOMDelaySchedule)</p></li><li><p>Make sure you Select Frequency to &#039;<em>Daily</em>&#039;</p></li><li><p>Under &#039;Daily Frequency&#039; Click the &#039;Occurs every:&#039; radio button and change the drop down menu from &#039;Hour(s)&#039; to &#039;Minute(s)&#039;<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_4.png" alt="New Job Schedule Window" /></span><br />Then Click on &#039;OK&#039;</p></li><li><p>Nothing further NEEDS to be done however should you wish to add Alerts or Notifications you can.&#160; These can also be added at a later time as well.</p></li></ol><p>You should now see the Job you just created under the &#039;Jobs&#039; menu which is under &#039;SQL Server Agent&#039;.<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_5.png" alt="NewJob_5.png" /></span></p><p>Should you wish to check the status of your Job and when it will run again (as well as what the Last Outcome was for the last time you job ran) you can open the &#039;Job Activity Monitor&#039; window.<br /><span class="postimg"><img src="https://www.xlayer.co.za/forum/img/NewJob_6.png" alt="Job Activity Monitor" /></span></p>]]></description>
			<author><![CDATA[dummy@example.com (SeanR)]]></author>
			<pubDate>Wed, 23 Oct 2019 09:12:48 +0000</pubDate>
			<guid>https://www.xlayer.co.za/forum/viewtopic.php?pid=162#p162</guid>
		</item>
		<item>
			<title><![CDATA[Re: Another way to handle DELAYs for events]]></title>
			<link>https://www.xlayer.co.za/forum/viewtopic.php?pid=161#p161</link>
			<description><![CDATA[<p>So what should happen if the X-DEC we received has a &#039;Repaired&#039; state in it?</p><p>Well I think we have one of 2 options that we need to test in a busy environment:</p><ol class="decimal"><li><p>We could simply change the sigreg record&#039;s &#039;<em>processedFlag</em>&#039; to 1.&#160; That way the 2nd contract would never process that record.</p></li><li><p>We could delete the record from the sigreg if the &#039;<em>processedFlag</em>&#039; is still &#039;999&#039;.&#160; This will not cause X-ServiceBroker to crash as the 2nd contract will never read that record in the first place.</p></li></ol>]]></description>
			<author><![CDATA[dummy@example.com (SeanR)]]></author>
			<pubDate>Tue, 22 Oct 2019 16:50:55 +0000</pubDate>
			<guid>https://www.xlayer.co.za/forum/viewtopic.php?pid=161#p161</guid>
		</item>
		<item>
			<title><![CDATA[Another way to handle DELAYs for events]]></title>
			<link>https://www.xlayer.co.za/forum/viewtopic.php?pid=160#p160</link>
			<description><![CDATA[<p>We currently have a very convoluted way to handle the processing of delays on events that are sent to us from an EMS system which involves 2 database tables and writing and later deleting signal registry records etc etc.</p><p>A delay process can EASILY be accomplished using MSSQL JOBS or MYSQL Events.&#160; At this point I have only tested this in MYSQL (I will post follow up on how to create MSSQL Jobs in a future comment)</p><p>The theory behind this method is as follows:</p><ol class="decimal"><li><p>A first contract reads the X-DEC file which is sent to us from the EMS tool.</p></li><li><p>This first contract simply writes a record into the signal registry (if the state is not Repaired). However, it writes the record with a &#039;<em>processedflag</em>&#039; value of something other than 0,1 or 2 (Let&#039;s use 999 in this example)</p></li><li><p>A second contract only processes this signal registry.&#160; It is configured to only process records with a &#039;<em>processedflag</em>&#039; of &#039;0&#039;</p></li><li><p>We create a MYSQL Event that will run every minute.&#160; When this event runs it will check all the records in the signal registry that have a &#039;<em>processedflag</em>&#039; of &#039;999&#039; and that are older than the records &#039;<em>epochtime</em>&#039; plus a specified delay interval. </p></li><li><p>On finding a matching record it will change this &#039;<em>processedflag</em>&#039; to &#039;0&#039; so that the second contract can process it.</p></li></ol><p>To create the MYSQL Event to the following:</p><ol class="decimal"><li><p>Enable the MYSQL Event_Scheduler</p><div class="codebox"><pre><code>set global event_scheduler = on;</code></pre></div></li><li><p>Create an Event which will run every minute.</p><div class="codebox"><pre><code>CREATE EVENT scomdelay
ON SCHEDULE EVERY 1 MINUTE
DO
  update sigreg set processedflag = 0 where processedflag = &#039;999&#039; and (select Date_add(epochtime, interval 5 minute)) &lt; now();</code></pre></div></li></ol><p>The above will simply run the update query every minute.&#160; When the delay has expired (in the code example above we are delaying for 5 minutes) the record will be updates setting it&#039;s processedflag to 0.</p><p><strong>NOTE:</strong> you can create multiple MYSQL Events for different &#039;<em>actionName</em>&#039; in the signal registry simply by changing the update line in the above code block.<br />E.G. </p><div class="codebox"><pre><code>update sigreg set processedflag = 0 where processedflag = &#039;999&#039; and actionName = &#039;ACME_Create&#039; and (select Date_add(epochtime, interval 5 minute)) &lt; now();</code></pre></div><p>In this above example ONLY records that have the &#039;<em>actionName</em>&#039;&#160; of &#039;Acme_Create&#039; and a &#039;<em>processedflag</em>&#039; of &#039;999&#039; will be modified.</p><p>To check if your Event has been added correctly to can run:</p><div class="codebox"><pre><code>show processlist;</code></pre></div><p>which will show you the process &#039;User&#039; as <strong>event_scheduler</strong>.&#160; If you do not see your event in that list simply run the </p><div class="codebox"><pre><code>set global event_scheduler = on;</code></pre></div><p> command again.</p>]]></description>
			<author><![CDATA[dummy@example.com (SeanR)]]></author>
			<pubDate>Tue, 22 Oct 2019 16:36:54 +0000</pubDate>
			<guid>https://www.xlayer.co.za/forum/viewtopic.php?pid=160#p160</guid>
		</item>
	</channel>
</rss>
