MS SQL Server Notification Services - Question about event generation on insert/update/delete

Author

Question about event generation on insert/update/delete

Franz

2006-01-18, 7:23 am

Hi all,

I've got some questions about using the Notification Services with Sql
Server 2000.
The problem is that I've to notify a remote Application about changes
(insert, update, delete) in a certain set of database-tables via network.

As far as I know, I've to use/implement IDeliveryProtocol:
In the Initialize-Method I create the connection to the remote host.
In DeliverNotification I'll send the name of the change table and the
performed operation.
In the Close-Method I close the Connection.

But how do I have to define the events and the tables to get notifications
about?

I've checked the corresponding samples, but they do not help me any further.

Thanx in advance

Franz

Joe Webb

2006-01-24, 8:24 pm

If you're using 2005, it sounds like sqldependency may be a better
solution for you. Have you check into that?

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 Wed, 18 Jan 2006 04:59:02 -0800, "Franz"
<Franz@discussions.microsoft.com> wrote:

>Hi all,
>
>I've got some questions about using the Notification Services with Sql
>Server 2000.
>The problem is that I've to notify a remote Application about changes
>(insert, update, delete) in a certain set of database-tables via network.
>
>As far as I know, I've to use/implement IDeliveryProtocol:
>In the Initialize-Method I create the connection to the remote host.
>In DeliverNotification I'll send the name of the change table and the
>performed operation.
>In the Close-Method I close the Connection.
>
>But how do I have to define the events and the tables to get notifications
>about?
>
>I've checked the corresponding samples, but they do not help me any further.
>
>Thanx in advance
>
>Franz

Franz

2006-01-25, 3:27 am

Hi,

I'm bound to SQLServer 2000 as a requirement of my customer - not possible
to switch to SQLServer 2005.

Are there any tools for SQLServer 2000 I haven't found yet to do the
messaging?

thanks,

Franz

"Joe Webb" wrote:

> If you're using 2005, it sounds like sqldependency may be a better
> solution for you. Have you check into that?
>
> 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)

Joe Webb

2006-01-28, 7:24 am

I completely misread your original post. Sorry about that.

As I understand it, you question is how to recognize changes in a
database table so that these changes can become events for the SQLNS
instance, right?

If that's the case, the you can use 3 or 4 part names to reference the
table that will be the source of your events. In your SQL Server Event
Provider, write a query that can be used to identify changes in the
table. You can use views that cover the table to assist. You can also
use Chronicle tables in the SQLNS app to keep track of the records
you've already recognized.

There's another possibility, too. There are some sprocs available in
the SQLNS database that you may be able to call from with a trigger on
your data database to submit event info.

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 Wed, 25 Jan 2006 00:52:03 -0800, "Franz"
<Franz@discussions.microsoft.com> wrote:
[color=darkred]
>Hi,
>
>I'm bound to SQLServer 2000 as a requirement of my customer - not possible
>to switch to SQLServer 2005.
>
>Are there any tools for SQLServer 2000 I haven't found yet to do the
>messaging?
>
>thanks,
>
>Franz
>
>"Joe Webb" wrote:
>

Franz

2006-01-30, 3:23 am

Hello,

thanks for your helpful reply. In all that hectics I forgot to inform you
about my solution off the problem. I solved it that way that I wrote a
extended stored procedure and attatched a trigger to the tables. So everytime
on insert, update or delete the corresponding message is generated.

I'm not to happy with my solution, but it works.

Thanks again for your answers

Franz

Sponsored Links