Accessing Initiating Event + Data From Existing External DB

Hello,
I have been exploring NS and I would like to use it as a rudimentary transfer tool. I have an existing database table from where I would to transfer a record to subscribers and then possibly move the records out of that table.

1. How do I set up the ..\SubscriptionClasses\SubscriptionClass\EventRules\EventRule so that it reads from another database? The TSQL Statement should probably work accross databases?

2. As I scroll through the sample ADFs I would think that ..\Providers\HostedProvider should also change too to another type.

Thank you very much,

Lubomir

Hi Lubomir -

Using the SQL Server Event Provider, you can collect events from tables in other databases or even other instances of SQL Server.

Configure the HostedProvider node to use the SQL Server Event Provider. Use the EventsQuery element to enter the T-SQL code that you will use to recognize new rows (or events of interest) in the table. Here's a sample.

<HostedProvider>
<ProviderName>SqlPrEP</ProviderName>
<ClassName>SQLProvider</ClassName>
<SystemName>%_NSSystem_%</SystemName>
<Schedule>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
<Argument>
<Name>EventsQuery</Name>
<Value>SELECT rowId, col1, col2, col3 FROM AnotherDb.dbo.vwCurrentRows WHERE rowId NOT IN (SELECT rowId FROM MyChron)</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>PressRelease</Value>
</Argument>
</Arguments>
</HostedProvider>

Next you can use the EventRule node of the SubscriptionClass to define your match rule; that is to write the T-SQL code that matches the events that you've collected to those subscribers who are interested in your events.

You EventRule would look something like this.

<EventRule>
<EventClassName>PressRelease</EventClassName>
<RuleName>PrEventRule</RuleName>
<Action>
INSERT INTO PrNotifications(
SubscriberId,
DeviceName,
SubscriberLocale,
col1,
col2)
SELECT
s.SubscriberId,
s.SubscriberDeviceName,
s.SubscriberLocale,
e.col1,
e.col2
FROM
PressRelease e,
PrSubscription s
WHERE
e.col3 = s.col3
</Action>
<ActionTimeout>P0DT00H00M45S</ActionTimeout>
</EventRule>

HTH...

Joe