Thursday, May 22, 2008

Why am i not getting any SQL notifications fired?

Why am i not getting any SQL notifications fired?
What are the constraints implementing SQL Server 7/2000 Dependency Caching? (see below...)

Here are few quick things you can check real quick
- Check your query string. Is it valid?
- Be sure you don’t have select *
- Be sure you do have two-part names for the tables e.g., dbo.countries
- Ensure that you have your broker service enabled: Run SET ENABLE_BROKER

- Check the compatibility mode of your database to be set as 90 at the database level. You can see and modify this setting in the Database Properties/Options/Compatibility level. *

- If you are want to use compatiblity mode 80 then you can still achieve it by setting ARITHABORT ON for the database properties or specifying at each individual connection as an example below -

Example:
--------------------------
Dim conn As SqlConnection = New SqlConnection(connectionString)
conn.Open()

Dim cmd1 As New SqlCommand("SET ARITHABORT ON", conn)
cmd1.ExecuteNonQuery()

Dim cmd As New SqlCommand("SELECT country, country_desc FROM dbo.COUNTRY", conn)

dep = New SqlDependency(cmd)
AddHandler dep.OnChange, AddressOf TableProxy.OnDependencyChanged
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader()
dtLocal.Load(rdr)
---------------------------------

There are several constraints as well in the way which query you can use for SQL notifications ....

Don’t use queries whose results will change frequently.
Don’t use Select * in queries.
Do select explicit columns.

Don’t use UNION, DISTINCT, or TOP.
Don’t use Aggregate functions such as AVG, MAX, etc...

Take a detailed peek at the do's and dont's here -
http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx

Take a look at ASP.NET’s Visual Web Developer Guided Tour for implementing SQL Server 7/2000 Dependency Caching at -
http://www.asp.net/Guided-Tour/s23.aspx

HTH, Thanks - Dipesh

No comments: