Retrieving New Insert Row in SQL table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Dear Friends

I have a SQL DB, in which we have 1 table called as events. This table gets populated with lots of events within each sec.

I need something like file-watcher to watch this table as soon as new row(event) is inserted i want to pull it and display it in my application.

I don't want to do it with triggers and if i am running query in each sec. the query is returning the value after 39 sec. so this is not helping.

I appreciate if anyone can help me in this.

Thanks in advance !!!

Have a good day.

updated
Still Struck with this.... Please anyone can advice on this....

Posted 14-Jun-13 20:22pm

Amaan23

Updated 17-Jul-14 3:48am

v2

Add a Solution

Comments

Mike Meinz 15-Jun-13 6:34am

If the query of an "events" table takes 39 seconds, you've got a performance issue in the design of the table and its indexes or in the hardware used.

I think polling the SQL Server the way that you are doing now is the way to go if the data must be in SQL Server and you are unable to use MSMQ.

Although I am not an expert in this area, I have seen other developers use <a href="http://msdn.microsoft.com/en-us/library/windows/desktop/ms711472(v=vs.85).aspx" target="XX">MSMQ</a> to send event messages to a "watcher" program rather than put them into a database table. The "watcher" program could put the event messages into the database if you really need them archived.

Amaan23 15-Jun-13 14:52pm

Thanks for your reply.

I have no control over the table or hardware thats why i cannt use MSMQ :(

My scope is only to connect and get the newly row(event) and display it on web page. -- Live events

Thanks

Rob Branaghan 17-Jun-13 4:19am

Do you have access to the stored procedures that write to the database?

Amaan23 17-Jun-13 7:25am

Yes i can write Stored procedure to the database

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nayan Ambaliya · Answer 1 · 2013-06-17T13:43:00

Hi,

You can do something like this:

Create a stored procedure as:

SQL

select     top 1 e.EventID, e.EventName, ....
from       dbo.events e
order by   e.EventID desc

and call this stored procedure every 2 seconds and check in the front end whether this event is fetched. If not, then add it to display otherwise ignore it.

I still don't think this is a good idea but since you do not have control over the insert stored procedure, this could be the only way you can do it.

If you have control over the insert stored procedure, then just insert the following just after the insert statement:

SQL

declare @newEventID bigint
set @newEventID = @@IDENTITY

select     e.EventID, e.EventName, ....
from       dbo.events e
where      e.EventID = @newEventID

Hope this helps.

Regards,
Nayan

Maciej Los · Answer 2 · 2014-07-17T09:25:00

Solution 2

All what you want is: SqlDependency class[^]. Here is interesting implementation: Using SqlDependency for data change events[^]

Posted 17-Jul-14 9:25am

Maciej Los

Comments

Amaan23 18-Jul-14 3:32am

Thanks, this is something new which i was not aware. Let me check and try out.

Still have doubts on above as my table(Time Attendance Table In and Out info) will have approx 50 events getting inserted in table in 2 min.

Well lets see. Thank you for your help.

Maciej Los 18-Jul-14 16:05pm

You're welcome ;)