MS SQL Server Notification Services - Integrating an existing application with SQLNS

Author

Integrating an existing application with SQLNS

Priya

2005-10-27, 8:00 am

Hi

I am facing a problem similar to a previous post in this forum.

I have an existing application and we need to integrate SQLNS inorder to
cater our Notification requirements. I'm not able to figure out how the
appADF.xml file has to be configured when the database to be queried is
different from the database SQLNS creates by itself for storing subscriber
and event details.

Can you please guide me with some examples as to how this can be done.
As I'm a novice in this field, will be quite helpful if u could suggest some
sites where i can find examples other than the samples provided during
installation.

Thanks & Regards
Priya

Joe Webb

2005-10-27, 8:00 am

Hi Priya -

Welcome to the SQLNS world!!! This newsgroup is a great place to learn
and ask questions. Come often.

Using the built-in SQL Server Event Provider, you can use 3 or 4 part
names to query other tables in other databases in the same SQL Server
instance or in linked servers, respectively.

To query a table in another database in the same SQL Server instance,
you could do something like this:


<Providers>
<HostedProvider>
<ProviderName>SqlPrEP</ProviderName>
<ClassName>SQLProvider</ClassName>
<SystemName>%_NSSystem_%</SystemName>
<Schedule>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
<Argument>
<Name>EventsQuery</Name>
<Value>SELECT PrId,
PrTitle,
PdfFileName
, PrDate
, PrType
FROM
ABCMotorworks.dbo. vwCurrentPressReleas
es
WHERE
PrId NOT IN (SELECT
PrId FROM
PrChron)
</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>PressRelease</Value>
</Argument>
</Arguments>
</HostedProvider>
</Providers>

To query a table in a database in another SQL Server instance, add a
linked server and then use 4 part names. In the prior example, you'd
replace
ABCMotorworks.dbo. vwCurrentPressReleas
es
with
MyLinkedServer.ABCMotorworks.dbo. vwCurrentPressReleas
es

As for examples and where to learn more...Shyam Pather has written an
excellent book on SQLNS. I also have a book out there on it. Both may
be found on Amazon by searching for SQL Server Notification Services.

I'm not aware of sites dedicated to SQLNS (although that's on my list
of things to do), but I do have a blog dedicated to the technology. I
just started it last month, but intend to update it at least weekly.
Here it is: http://www.sqlns.com. I'd love to hear comments on what
you'd like to see on it.

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)On Mon, 10 Oct 2005 03:33:03 -0700, "Priya"
<Priya@discussions.microsoft.com> wrote:

>Hi
>
>I am facing a problem similar to a previous post in this forum.
>
>I have an existing application and we need to integrate SQLNS inorder to
>cater our Notification requirements. I'm not able to figure out how the
>appADF.xml file has to be configured when the database to be queried is
>different from the database SQLNS creates by itself for storing subscriber
>and event details.
>
>Can you please guide me with some examples as to how this can be done.
>As I'm a novice in this field, will be quite helpful if u could suggest some
>sites where i can find examples other than the samples provided during
>installation.
>
>Thanks & Regards
>Priya
>

Priya

2005-10-27, 8:00 am

hi Joe,

That was quite clear and informative. I will try and implement the same and
get back to you in case of any doubts.

Thanks a bunch !

Regds
Priya.

"Joe Webb" wrote:

> Hi Priya -
>
> Welcome to the SQLNS world!!! This newsgroup is a great place to learn
> and ask questions. Come often.
>
> Using the built-in SQL Server Event Provider, you can use 3 or 4 part
> names to query other tables in other databases in the same SQL Server
> instance or in linked servers, respectively.
>
> To query a table in another database in the same SQL Server instance,
> you could do something like this:
>
> 
> <Providers>
> <HostedProvider>
> <ProviderName>SqlPrEP</ProviderName>
> <ClassName>SQLProvider</ClassName>
> <SystemName>%_NSSystem_%</SystemName>
> <Schedule>
> <Interval>P0DT00H00M60S</Interval>
> </Schedule>
> <Arguments>
> <Argument>
> <Name>EventsQuery</Name>
> <Value>SELECT PrId,
> PrTitle,
> PdfFileName
> , PrDate
> , PrType
> FROM
> ABCMotorworks.dbo. vwCurrentPressReleas
es
> WHERE
> PrId NOT IN (SELECT
> PrId FROM
> PrChron)
> </Value>
> </Argument>
> <Argument>
> <Name>EventClassName</Name>
> <Value>PressRelease</Value>
> </Argument>
> </Arguments>
> </HostedProvider>
> </Providers>
>
>
> To query a table in a database in another SQL Server instance, add a
> linked server and then use 4 part names. In the prior example, you'd
> replace
> ABCMotorworks.dbo. vwCurrentPressReleas
es
> with
> MyLinkedServer.ABCMotorworks.dbo. vwCurrentPressReleas
es
>
>
>
> As for examples and where to learn more...Shyam Pather has written an
> excellent book on SQLNS. I also have a book out there on it. Both may
> be found on Amazon by searching for SQL Server Notification Services.
>
> I'm not aware of sites dedicated to SQLNS (although that's on my list
> of things to do), but I do have a blog dedicated to the technology. I
> just started it last month, but intend to update it at least weekly.
> Here it is: http://www.sqlns.com. I'd love to hear comments on what
> you'd like to see on it.
>
> HTH...
>
> --
> Joe Webb
> SQL Server MVP
> http://www.sqlns.com
>
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)On Mon, 10 Oct 2005 03:33:03 -0700, "Priya"
> <Priya@discussions.microsoft.com> wrote:
>
>

Rieske

2005-10-27, 8:00 am

Dear Priya,

You can do this by making your query join the subscribers database.
Look at the Realtor sample from the Microsoft site. you can see that there
are three databases used.
- RealtorDB
- RealtoInstanceNSMain

- RealtoInstanceRealto
r

The first is a different one.

Richard

"Priya" <Priya@discussions.microsoft.com> schreef in bericht
news:3E474B74-8EE0-4694-9733- A9B8BFBC42DE@microso
ft.com...
> Hi
>
> I am facing a problem similar to a previous post in this forum.
>
> I have an existing application and we need to integrate SQLNS inorder to
> cater our Notification requirements. I'm not able to figure out how the
> appADF.xml file has to be configured when the database to be queried is
> different from the database SQLNS creates by itself for storing subscriber
> and event details.
>
> Can you please guide me with some examples as to how this can be done.
> As I'm a novice in this field, will be quite helpful if u could suggest
> some
> sites where i can find examples other than the samples provided during
> installation.
>
> Thanks & Regards
> Priya
>
>

Sponsored Links