Help: How to detect inserts, updates, deleted on a table from within C++ application?

[L. Blunt]

Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was just
dumping data into a database using ODBC, and that was some years ago so now
mostly forgotten.

I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.

My application needs know when new row are inserted, or updated so it can to
read this data (needs to be quick/timely so hopefully no polling) to then
interface with other remote proprietary systems.

T.I.A.

PS: If you can recommend appropriate books on SQL Server 2000 that would
also be useful.

[Chuck Conover]

L,
There isn't an easy way to do this. You don't say how many tables you
need info for, but I think the best way to do this is to create and insert
and update trigger on the rows which would insert the primary key for that
table into another table or tables. This way, your application could poll
the new table and grab the primary keys that were inserted or changed, then
delete all data in the table.
Or, you could add a last_modified field which would be updated by
trigger anytime the row was inserted or updated. This might be a little
slower for your application, but easier to maintain data integrity. The
problem with the first solution is you need to be careful if an insert or
update is happening at the same time that you poll your new table.

I hope this helps.
Better than books on MS SQL Server 2000, you might take a look at our
video series at www.TechnicalVideos.net. In just a few hours, you can go
from newbie to expert. Download and watch the .wmv videos on your computer
and our experts will show you how to do things that we really use; things
you won't find in books. There's nothing like having an expert show you
first hand.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"L. Blunt" <wi******@my.choice> wrote in message
news:40***********************@news.dial.pipex.com ...

Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was just
dumping data into a database using ODBC, and that was some years ago so now mostly forgotten.

I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.

My application needs know when new row are inserted, or updated so it can to read this data (needs to be quick/timely so hopefully no polling) to then
interface with other remote proprietary systems.

T.I.A.

PS: If you can recommend appropriate books on SQL Server 2000 that would
also be useful.

[Erland Sommarskog]

L. Blunt (wi******@my.choice) writes:

Hopefully someone can at least point me in the right direction for more
research (e.g.: correct terminology). My only previous experience was
just dumping data into a database using ODBC, and that was some years
ago so now mostly forgotten.

I need to write an NT Service/Application (in C/C++) that will be getting
data sent to it via SQL Server 2000. The data will arrive in my SQL Server
(read-only access), via replication of tables from another remote SQL
Server.

My application needs know when new row are inserted, or updated so it
can to read this data (needs to be quick/timely so hopefully no polling)
to then interface with other remote proprietary systems.

Chuck suggested triggers, and that is about the only way to go in SQL
2000, if you don't want to poll. The trigger would in that case invoke
an exentended stored procedure, or an OLE object to alert your application
that there is data to find.

But there might be a catch, since you are using replication. I don't relly
know what happens with triggers on tables in a subscriber database when
you set up replication. There may be a risk that all at a sudden the
triggers are gone.

So intensive polling might be better. Then there is a question on how to
poll, but knowing nothing about the database, this is a little difficult
to discuss.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp