StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » SQL Notification Services

SQL Notification Services

Aaron Young — Thu, 01 May 2008 19:12:07 GMT

I haven't used SQL Notification Services before so this is all new to me.

I have notification services installed, the broker service is enabled on the database and I have set the BO ReceiveQueryNotifications to True. When I run a form containing the BO I get the error:-

"When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance"

I have done nothing in SQL Notification Services itself as I have assumed SF handles this for me. Are there any additional steps I have missed in Notification Services itself?

Thanks in advance,

Aaron

RE: SQL Notification Services

Trent L. Taylor — Thu, 01 May 2008 19:12:07 GMT

The user to which is trying to subscribe has to have those permissions as well. StrataFrame does everything that has to be done on the .NET side in regards to setting up the SqlDependency and then notifying you of the changes. However, the part that you are having the issue with is more than likely the SQL side...and getting it setup. This is one reason that we elect not to use Query notifications in the field as you can fight this issue often...so we generally take another aproach.

But back to the point of the matter, to grant a subscription permission to a user you would do this:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sqldependency_user]

RE: SQL Notification Services

Aaron Young — Thu, 01 May 2008 18:12:05 GMT

I have tried "SELECT " + AllFieldNames + " FROM " + TableNameAndSchema and I get the same error. I have looked at your sample and I think I am doing the same.

Did you have to setup or initialise SQL Notification Services itself other than the ALTER DATABASE xxxxx SET ENABLE_BROKER? My SQL Notification Services was simply installed out of the box with no post configuration beyond the intial setup.

I will be away for a couple of days so thanks in advance.

Aaron

RE: SQL Notification Services

Trent L. Taylor — Thu, 01 May 2008 08:33:40 GMT

Sounds good.

RE: SQL Notification Services

Aaron Young — Thu, 01 May 2008 05:35:19 GMT

Ah, I don't think I have the dbo in my query either. I am using

"SELECT " + AllFieldNames + " FROM " + TableName

and I think the latter doesn't have the dbo. I will change this and try again.

RE: SQL Notification Services

Trent L. Taylor — Wed, 30 Apr 2008 19:16:08 GMT

Did you run the sample and look at some of my notes?

[quote]Have I done something wrong?[/quote]

This is a tough question to answer :) There are a lot of "gotchas" when using Query Notifications. You have to have properly formatted queries with explicit field names, schema names provided for tables, etc.

Last night when I was messing with the sample I chased my tail for a while because I left the dbo off of the Customers table in the query. There are quite a few rules when using the SqlDependency...but I did get it working and that is why I provided the sample and some of my notes to get it to work as SQL Server is very strict when subscribing to a notification.

RE: SQL Notification Services

Aaron Young — Wed, 30 Apr 2008 18:43:35 GMT

I have installed the latest beta and updated the base DLL but I am afraid I still get the error.

Have I done something wrong?

Thanks

RE: SQL Notification Services

Trent L. Taylor — Tue, 29 Apr 2008 22:30:28 GMT

Two things...first, there was a bug on how the connection string was being registered. Also, I created a sample that shows how to use this type of service. Use the attached assembly to replace your Base that will resolve the error. This will be in the next build that I post (I am assuming that you are on the 1.6.6 beta). This should get you going. :)

Here is the sample: http://forum.strataframe.net/FindPost16012.aspx