Hi Christian,

Any possibility that that there is Nuget package with .Net Core?

Doe is support configuring to multiple table to watch instead of Type T . One at a time.

Hi Christien,

I've following error on OnError event handler. Can use please sort this out ?

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Good morning, we are using SqlTableDependency into an always running Windows Service like a "notifications centralized service"; after about two days of always running we get a sql transaction exception, i logged also the SqlTableDependency status change:

[21:02:33] Status changed: STOPDUETOERROR
[21:02:33] Error message: TableDependency stopped working
exception: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Rollback()
at TableDependency.SqlClient.SqlTableDependency`1.d__63.MoveNext()

Is maybe a sql transaction timeout problem or something like that in the sql server configuration?

Thanks very much in advance

Hi Christian,

we cannot find anymore the oracletabledependency - seems to be deleted everywhere. Could you please let me know why - any major issues you encountered with it?

Thanks in advance

Hey Christian,
I'm pretty new to SQL Server and databases in general.
I was wondering if it is possible to get SqlTableDependency to work with views instead of tables only. Currently it throws a TableWithNoColumnsException if I try this out.
So do the triggers and the notification pipeline theoretically work with Views?

Hi Christian,

I just wondered what the rationale was when deciding to force tearing the subscription setup down (i.e. the SqlTableDependency infrastructure such as Trigger, Service Broker service, the queue, etc.), whenever application stops (either gracefully or crashing).

Would it be at all possible to leave infrastructure intact thus subscribe back to it on next startup?
That way subscriber would not miss out any messages.

Many thanks

I'm getting this error: "Incorrect syntax near 'POISON_MESSAGE_HANDLING'."
We are using SQL 2008 (no R2).

Is there a minimal SQL version requirement?

When creating a queue in MSSQL2008 (non-r2), that parameter is causing an error:

SqlException: Incorrect syntax near 'POISON_MESSAGE_HANDLING'.
Incorrect syntax near ')'.

From a little bit of digging it looks like that was not added until R2.

Hi have you tried this with Azure? When I try I initialize the TableDependency object I get the error below, looks like 'sys.login_token' is not accessible from AzureSQL.

{System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sys.login_token'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at TableDependency.SqlClient.SqlTableDependency1.CheckIfUserHasPermissions(String connectionString)
at TableDependency.TableDependency1..ctor(String connectionString, String tableName, IModelToTableMapper1 mapper, IUpdateOfModel`1 updateOf, ITableDependencyFilter filter, DmlTriggerType dmlTriggerType, Boolean executeUserPermissionCheck)
at Server.Hubs.Challenge.DatabaseSubscriptions

Hi

I am using sqltabledependency to monitor change in my table and it works nicely apart from one column. It is of type sys.geometry and holds lat/lon points. When I update the column with a new point there is no change notification. I am trying to report on real-time location change.

I wonder if that is something you would consider supporting please?

Thanks

hi,
I get this message onError
Object of type 'System.Int32' cannot be converted to type System.Nullable`1[ .....MY ENUM TYPE...... ]'.

here is the stacktrace:
at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast)
at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr)
at System.Reflection.MethodBase.CheckArguments(Object[] parameters, Binder binder, BindingFlags invokeAttr, CultureInfo culture, Signature sig)
at System.Reflection.RuntimeMethodInfo.InvokeArgumentsCheck(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, BindingFlags invokeAttr, Binder binder, Object[] index, CultureInfo culture)
at System.Reflection.RuntimePropertyInfo.SetValue(Object obj, Object value, Object[] index)
at System.Reflection.PropertyInfo.SetValue(Object obj, Object value)
at TableDependency.EventArgs.RecordChangedEventArgs1.MaterializeEntity(List1 messages, IModelToTableMapper1 mapper) at TableDependency.EventArgs.RecordChangedEventArgs1..ctor(MessagesBag messagesBag, IModelToTableMapper1 mapper, IEnumerable1 userInterestedColumns, String server, String database, String sender, CultureInfo cultureInfo)
at TableDependency.SqlClient.EventArgs.SqlRecordChangedEventArgs1..ctor(MessagesBag messagesBag, IModelToTableMapper1 mapper, IEnumerable1 userInterestedColumns, String server, String database, String sender, CultureInfo cultureInfo) at TableDependency.SqlClient.SqlTableDependency1.GetRecordChangedEventArgs(MessagesBag messagesBag)
at TableDependency.TableDependency1.NotifyListenersAboutChange(Delegate[] changeSubscribedList, MessagesBag messagesBag) at TableDependency.SqlClient.SqlTableDependency1.d__63.MoveNext()

I get this error when I try to install this nuget package in my .Net Core project:

Package SqlTableDependency 6.1.0 is not compatible with netcoreapp1.1 (.NETCoreApp,Version=v1.1). Package SqlTableDependency 6.1.0 supports: net (.NETFramework,Version=v0.0)

Are there any plans for this package to support .Net Core?

Hi
I am using SqlTableDepndency in my code to monitor a table. Is there a way to mock SqlTableDepndency in my unit test code or the integration tests are the only way to test the code?
Could you please suggest.

Thanks
Rupendra

i have a column in database: "COM_TIME" and in model : "ComTime"

then use Custom Mapper

var mapper = new ModelToTableMapper<MyEvent>();
mapper.AddMapping(c => c.ComTime, "COM_TIME");

//Using SqlTableDependencyFilter
var dt = DateTime.Now;
Expression<Func<MyEvent, bool>> expression = p => p.ComTime == dt;
ITableDependencyFilter whereCondition = new SqlTableDependencyFilter<MyEvent>(expression);

it don't work ...

and i found error in sql profiler

CREATE TRIGGER [tr_dbo_SHIJIAN_e1647094-2b4a-44e6-9630-2ce4177d3f3d] ON [dbo].[SHIJIAN] AFTER update AS 
..........
 a WHERE ([ComTime] = '2018-04-24T03:55:47')
//there is ComTime and not COM_TIME ....

Hello,

I am trying to set the "teardown" to false so that objects are not removed every time service stops.
Does SqlTabledependancy support teardown functionality. It was mentioned in the release notes version 5 as a new feature. I looked at the code and don't see a constructor for tabledepenndancy which takes teardown as parameter

I'm getting an exception when using this when our Schema is keyword.

The Schema is Transaction and the Table is Item...so [Transaction].[Item].

Here is the model:
[Table("Item", Schema = "Transaction")]
public class TransactionItem
{
public Guid TransactionItemId { get; set; }
public string Description { get; set; }
}

Hi,

I created an asp.net application (w/ SignalR) that monitors a table. It all works fine, but in production, I see that the database triggers etc get cleared after some time. This may be related to the fact that IIS automatically suspends worker-processes after some idle time.

Do you have a suggestion which configuration / settings can be used in order to ensure that the SqlTableDependency will remain 'active', or resume?

Thanks,
Erik

Hey Christian,
I really love how this looks. I think I got a proj with all the current versions, but I get this error from PM:

Warning NU1701 Package 'SqlTableDependency 6.1.0' was restored using '.NETFramework,Version=v4.6.1' instead of the project target framework '.NETCoreApp,Version=v2.0'. This package may not be fully compatible with your project.

Also wondering on an approach to use the connection string or connection from existing DbContext? I pull the connection string from my config:

using (var dep = new SqlTableDependency<DwsFileExt>(config.GetConnectionString("DwsConnection"), "DwsFiles", mapper))
And I get an Exception message in my OutPut:

Exception thrown: 'TableDependency.SqlClient.Exceptions.UserWithNoPermissionException' in TableDependency.SqlClient.dll
An exception of type 'TableDependency.SqlClient.Exceptions.UserWithNoPermissionException' occurred in TableDependency.SqlClient.dll but was not handled in user code
User without permissions.

I'm working on implementing in a EF Repository and will happy share my results if I can get it all working.

Thanks,
Eric

Hi!

I'm using your component for a while now and I noticed that inbound disconnects aren't cleaned up, which slowed down my SQL server almost to a grinding halt (+600.000 half open connections) I guess this has todo with the fact that the conversations aren't ended in a proper manner. When I took a look at the generated triggers a couple of things got my attention...

1. You don't use any transactions when sending messages
2. END CONVERSATION doesn't include WITH CLEANUP

I guess that the latter is the culprit.

TIA
Bert

The filter is "ignored" when any table entry is updated and causes an OnChanged event. Only works with inserts/deletes.

Hi,
Every now-and-then I get the following error (in the OnError event).
Do you either have an idea where this comes from, or a suggestion where I can start debugging?
Thanks,
Erik

Queue containing a message type not expected [http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer].
Stacktrace: at TableDependency.Messages.MessagesBag.AddMessage(Message message)
atTableDependency.SqlClient.SqlTableDependency1.<>c__DisplayClass63_0.<WaitForNotifications>b__0(Message m) at System.Collections.Generic.List1.ForEach(Action1 action) at TableDependency.SqlClient.SqlTableDependency1.d__63.MoveNext()

I'm having trouble with using the sqltabledependency for tables that have at least one period (.) character on them i.e. "[dbo].[Stream.Users]". It seems that you're splitting the table name and only getting the string on the first index of the split array as the table name based on the code.

Hi
I get the error bellow when the SqlTableDependency start method is launched

**An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in the user code

Additional Information: Incorrect syntax near 'POISON_MESSAGE_HANDLING'.**

can you help me ?

It would be useful to use ColumnAttribute from DataAnnotations.
This would simplify setup and remove mapper dependency if not wanted.

hi good afternoom, morning, evening
I'm from Mexico so my english is bad, I had the problem with DataType 'text' with sql server 2008 r2 how I can get de data with a String Data in my Class Table Model ???

Hi, I'm having an issue trying to implement this with an MVC application against a SQL 2008 database. However it looks like the command/hint POISON_MESSAGE_HANDLING ( [ STATUS = { ON | OFF } ) is not supported on this version.

When the application tries to register the dependency it fails due to incorrect syntax. Is there anyway I can specify a parameter to exclude the poison message hint from the Queue create statement?

P.S. This is a great library, fantastic work!!

Many Thanks
Regards
Adam

I encountered two issue when try this solution.

1. Client has not responded after sometimes even table has changes (insert/update/delete). I need to stop the application pool at the web service server then restart, then the client will be working fine again.
2. Client program exit with code -1 every time when i stop the client program running.

Can you please help to advise where is going wrong? I followed the code in your website and has minor modification (eg table, connection string, etc). And also I change to simple window application instead of WPF application by writing the changes to the Debug output.

Thanks in advance! :)

p/s: wcf code and client code are attached as well.

wcf_client.zip

You might remember me in CodePlex as a Korean programmer(Kay Lee) with non-software related major.

In these days, Winter Olympic 2018 PyeongChang, Korea is taking place and hope your country's excellent achievements !

One of my free customer reported that suddenly, without any error message, he cannot receive notification any more from a few days ago after 2 other applications were updated as he remembers.

As you might know, it's very difficult to ask informations to some customers.

With very limited information like there was no error message, I assume the local SQL server was shut downed abnormally and some dependency objects were not removed automatically and interfering normal notification.

What I'd like to know are,

When dependency objects are abnormally existing after abnormal shut down, (after turning off the computer(SQL server) and turning on at next day), what if SQLTableDependency is started and terminated normally? the previous, abnormally existing dependency objects will be removed automatically together?
(My customers turn on and off their computer(SQL server) everday.)

Do the dependency objects have unique fingerprint in their name so that I can remove exact dependency objects only, not other objects by other applications?

Kind let me know the unique fingerprint if exists,

or I do appreciate if you provide the SQL code to remove dependency objects so that I can remove previously existing dependency objects for sure before customers start my application.
(Please kindly understand again that I'm a low level programmer with non-software related major.)

We're just finishing Lunar New Year Holidays and I'll try my best to communicate with the customer and check with various indirect ways like another SQL table and so on.

Sorry, I'm always too talkative, eager to solve my problem but sure it's very easy to answer.
(Many of my customers are still using SQL 2005, 2008, therefore, I must stay with old version of SQLTableDependency like 5.x.x.x)
(and my customers don't know about programming, software, SQL)

Always thank you so much for your excellent SQLTableDependency !

Sincerely,
Kay Lee.

Hello,

We developed a Windows Forms booking system some years ago.
This application manages booking of hotel rooms associated with tours organized by our customer.
While making a reservation, a map of all free rooms is shown to the user.
We developed the system so that while a user is booking a room for a certain tour, other users are not allowed to book on the same tour.
This is to avoid that two users occupy the same room.

This was acceptable when the system was used by emplyees only.
Now we're opening the booking system to Internet, using MVC, while keeping the Windows Forms application for reservations made by employees (WF App has some advanced functions not available to customers).

In order to manage concurrency, we were evaluating SqlTableDependency, so that both WF App users and MVC users are notified as soon as a room gets occupied.

But we are wondering if this component can be safely used on many (10-15) windows clients monitoring the same table at the same time.

Thank you!

I noticed that in release Release 5.0.0.0 the ability to prevent the teardown of database objects was added, however this feature seems to be gone in the latest release.

Is there any documentation on how to use this feature? Or was this feature removed in a later version and no longer supported now?

Thanks heaps!

Maybe my application crashs and because of the "WatchDogTimeout" the queues are remaining on SQL Server. Chnages to table are still logged to that Queues. It is now somehow possible to attach to an existing queue created by SQLTableDependency instead of creating a new one each time "Start" method is called?

Hi christian,
Is this possible to receive old row state with old values and also new row values both via triggers on OnChanged event ?

Thanks

Hi Christian,

I see on your repo that the version for SQL Server must be at least 2012.
Can you confirm it does not work on SQL Server 2008 R2?
If it does not work, could you be so kind to detail the reasons behind ?

Kind regards!

I have a problem with a double type column (this column stores a datetime as an SQL Format), when the insert was reported, the value was truncate to only 2 decimal digits.

hi,
On call of Start() i get this error
Invalid object name 'dbo_Customers_94173d13-e99c-48ad-9084-bdf2cfb80e79'.
Invalid object name 'dbo_Customers_94173d13-e99c-48ad-9084-bdf2cfb80e79'

I followed the working example and im stuck here for days! Please help thanks :)

I have started SQL table dependency in a table. I gave all the permission to database listed in your document. After some times ,may be in idle condition it is giving status as "Waiting for notification" .

When I change in table (inserting new record), status is not changing (From waiting for notification) and gives error as "The conversation handle "A705917C-4762-E711-9447-000C29C3FCF0" is not found."

Can you please help me to fix this issue?

not detecting any change in table when deployed on IIS 8 windows server, I made dependencies on two tables but none of them is working, but working perfectly on localhost

Passing in true means skip the check, this is not very intuitive. Is this intentional?

Hi,

I am tried to use SqlTableDependency approach. But i am getting this error.

at TableDependency.SqlClient.SqlTableDependency1.CheckIfUserHasPermissions(String connectionString) at TableDependency.TableDependency1..ctor(String connectionString, String tableName, IModelToTableMapper1 mapper, IUpdateOfModel1 updateOf, ITableDependencyFilter filter, DmlTriggerType dmlTriggerType, Boolean executeUserPermissionCheck)
at TableDependency.SqlClient.SqlTableDependency1..ctor(String connectionString, String tableName, IModelToTableMapper1 mapper, IUpdateOfModel`1 updateOf, ITableDependencyFilter filter, DmlTriggerType notifyOn, Boolean executeUserPermissionCheck)
at WindowsFormsApplicationTrack.Form1.Form1_Load(Object sender, EventArgs e) in

Can you help on this.

Thanks,
Niraj

sir,will you maintain this project persistantly ?

Hi. Have following compiler error on SqlTableDependencyFilter constructor:
The type 'IModelToTableMapper<>' is defined in an assembly that is not referenced. You must add a reference to assembly 'TableDependency, Version=5.0.0.0, Culture=neutral, PublicKeyToken=997305cc1a8bb802'.
When using version SQLTableDependency 6.1.0 no errors.
P.S. I have 2 questions (please forgive me if this is not a right place to ask):

1. How to configure IIS hosting WCF that uses SQLTableDependency? Perhaps set idle timeout to 0 and schedule app pool recycle to happen each night?
2. Is it possible to use SQLTableDependency on db being automatically backed up every night? Did not understand related remark completely.

After adding data in the monitored table, change in data is reflected in the event handler. But, after time of 2- 3 hrs, it stops working. I think problem might be due to lock on queue due to activation procedure. No data change is received, even though I add records on server side table. I can see queue filled with records but no change at client side. It keeps saying
Running waitfor command
after 2 mins timeout.
Can you help me with this? It is really necessary.

I have a client with a table named "Transaction.Header" and "Transaction.Item". I create my dependency with
_tableDependency = new SqlTableDependency(ConnectionString, TableName);
_tableDependency.Start();

Where TableName is "Transaction.Header" or "Transaction.Item". The exception occurs on Start()
System.Data.SqlClient.SqlException, Incorrect syntax near the keyword 'Transaction'.

Note I've tried the string "[Transaction].[Header]" and that also does not work. I see in your code you remove the []. Possibly they should not be removed?

By default the parameter MAX_QUEUE_READERS = 1 on the Service Broker que. Is there a way to set this value from C# code for the sqltabledependency ?

Is there some solution provide me to get the old values when ChangeType is Update?
I need to know which column update from what value.

After the application is restarted, all objects in the database are deleted and recreated.
How can I avoid this?

is there a configuration to specify amount of time before execution of cleanup SP.
the time out is a bit problematic because in some cases if the receiver application is down, detecting that might be impossible for database code.

Hi, I am using sqlTableDependency in a windows service. After a day of working properly, the changes in the table that I configured does not fire the event. Do you now the cause and how to fix it?
Regards

Is there a way to clean up the que status after messages have been processed by sqltabledependency? The following query shows the conversation state as DISCONNECTED_INBOUND for all messages, and it stays that way until the .Net program is terminated:

select lifetime, state_desc, security_timestamp from sys.conversation_endpoints;