Im trying to get SQL Notifications to work with BizTalk, but im struggling a one point.
The Binding of the Receivelocation is the following:
The SQL Server is supporting Notifications, and the connection string is correct.
When i start the Receivelocation it is working exactly one time in a correct way, but when i disable it and start it again, i get the following error in the eventlog.
- The Messaging Engine failed to add a receive location "RL.MDM.SQL" with URL "mssql://.//Database?InboundId=GetNewMDMChanges" to the adapter "WCF-SQL". Reason: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: The notification callback returned an error. Info=Invalid. Source=Statement. Type=Subscribe.
I cant start the Receivelocation again till i Execute the following command on the Database to enable the Broker.
alter database MDMDEV set enable_broker with rollback immediate;
The strange thing here is when i check if the broker is still enabled before i execute the command above, i see that the broker is indeed still enabled.
So the command to enable the broker fixes my problem for exactly one other notification and than i have to do this again.
Has anybody ever had this problem or can tell me what im doing wrong? Thanks in advance.
Regarding the Notifications feature in general, my recommendation is to not use it.
With both SQL Server and Oracle, the Notifications feature is quite fragile and will stop receiving event with no warning or error. When this happens, the only way to recover is Disable/Enable the Receive Location.
Basically, I have found it not reliable enough to use in production apps.
If you or your organization own the database, Polling [+ Triggers if needed] are 100% reliable.
This article describes some different Polling scenarios: BizTalk Server: SQL Patterns for Polling and Batch Retrieve