Query Notifications/ Service Broker problem

Hey , I encountered a problem using SqlDependency and notification services.

I’ve created a database with tools from c# Express 2005 and attached it to the sql server express 2005. And it turned out that ‘dependency_OnChange’ in my application is triggered each time I execute the command.ExecuteReader(); and it shouldn’t, because the command is just the select statement of columns and rows for which I wait to be modified.

SqlDependency dependency = new SqlDependency(command);

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

command.ExecuteReader();//when the program gets here dependency_OnChange is called

So I Used the example from here :

http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

and with AdventureWorks it behaves as it should , but when I change the connection string and select statement in the example to work with MyDatabase, the problem appears again.

So there is something wrong with MyDatabase ?

The select statement I used was “select Name, Pass from Users where Name like ‘L%’”;

So theoretically this should work but it does not and I end up in an infinite loop with

dependency_OnChange called cyclically …

I’d be grateful for any advice !

What are the dependency_OnChange paramater values passed in when the callback is notified?

I suspect the SqlNotificationEventArgs argument has a value of 'Subscribe' for the notification type, indicating an error in setting up the notification. If the statement you included is the exact statement you'd tried, then the problem is that your query is not respecting the Query Notifications restrictions. The query select Name, Pass from Users where Name like ‘L%’ is not properly schema bound, the Users table name should be bound with a schema name (e.g. dbo.Users).

HTH,
~ Remus

Query Notifications in SQL Express?

I am finding conflicting information about enabling query notifications for SQL Express. One book says it can be done and shows an example but most places on MSDN claim it requires the Service Broker. I am using Management Studio for SQL Express which does not show the Service Broker while an installation with SQL Server 2005 Dev Edition does show the Service Broker.

This is all very confusing. I want to be able to develop locally with SQL Express with Query Notifications and later deploy to a server which is more capable. Previously I have read that only real difference between SQL Server 2005 and SQL Express is a 2gb memory limit.

How would I go about setting up Query Notifications for SQL Express? Where would I find such documentation on MSDN?

I am partially past this problem. Upgrading SQL Express to SP1 has fixed this command from handing in Management Studio.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Now I can see the Service Broker is enabled, but I guess I cannot really manage it, which is fine as long as I can still use the default queue for notifications. This query confirms it is on.

SELECT name,is_broker_enabled FROM sys.databases

By default the Service Broker is on for new databases but when I load the AdventureWorks database it is not enabled, so I had to get this working. Now I am stuck on these commands...

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO brennan
GRANT SEND ON SERVICE::SqlQueryNotificationService TO brennan

The first one works but the second one fails. It says it does not recognize that service. I would like to know if there is a query I can run to see the available services.

Any help is appreciated.

Query Notifications filling up the transmission queue

I have a web application that sets up a query notification on a queue. Normal operation works fine, but for some reason the queue being monitored disables occasionally (even with the transaction, error message, and end dialog message) pattern set up as described in Remus' blog post. When the queue disables, everything goes insane and the transmission queue, left unchecked, fills up the transmission queue with hundreds of thousands of PostQueryNotification messages. Am I doing something wrong? What should I do to mitigate this because it IS possible that the queue might disable sometimes, and I don't want drive space eaten up on a low-end SQL Express machine, if it does. Also - second question - where is the transmission queue storing all these messages? TempDB?

I still haven't found a solution to this problem - even after applying normal poison message handling within the .NET code. Anyone? What to do about these query notifications filling up the xmit queue (and therefore the disk) when the queue disables?|||

One more question with regard to this behavior:

Why does service broker start a new conversation for each one of these notifications? This seems to compound the problem by not reusing a valid conversation_handle. I hope someone can provide an answer or solution to this soon because its close to holding up an intended launch at this point. Basically, I need to know what to do when a queue becomes disabled that is being monitored by a .NET application for query notifications. It seems like maybe I have to set up the architecture to receive the event notification in the .NET application and try to recover from code, rather than receiving it in SQL Server and recovering, which is what I would rather do.

|||

Even if it would reuse the conversations, the same number of notifications (messages) would be fired, so the transmission_queue would grow to the same size.

1) Why does the queue get disabled in the first place? In a development environment this occurs more often than normal because developers tend to debug applications and abruptly intrerupt execution, thus causing rollbacks that eventually disable the queue, but in a normal/test environment this should be a rare event. The fact that the queue gets disabled too often might indicate an application problem.

2) How many notifications are fired that the rate of growth for transmission queue is a issue of concern? Normally applications subscribe to a notification and then re-post the query when they are notified. So there should be a natural limit on the number of notifications fired and not delivered, since applications would not post new queries until the previous ones are notified. From your description it seems that new quesries are posted continuosly, which would indicate a problem in the application.

3) How did you set up and process the queue_diabled notification? Why did you had problems with this?

|||

Well, here is the setup of the notification, started on a separate thread from the main thread of the web application:

using (_command =

new SqlCommand("usp_ProcessMessage", _connection))

{

_command.CommandType = CommandType.StoredProcedure;

_command.CommandTimeout = 0;

string service = "service=myService" ;

_sqlNotificationRequest = new SqlNotificationRequest(new Guid().ToString(), service, 0);

_command.Notification = _sqlNotificationRequest;

while (true)

{

...

// BeginTransaction

// ExecuteReader

// Do Processing on Message

...

}

}

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[usp_ProcessMessage]

AS

BEGIN

WAITFOR ( RECEIVE TOP ( 1 ) conversation_handle, message_type_name, message_body

FROM [tcp://MyQueue] ) ;

END

When [tcp://MyQueue] becomes disabled, the problems start. The question as to why the queue is becoming disabled is still under investigation and, I agree that this happens more in development. But, I am seeing it regularly, even with measures in place to dequeue the message and try to recover. In any case, even if I can isolate what is causing the queue to disable, I still need to have a recovery model in place - JUST IN CASE. So, is there something intrinsically wrong with the code above? Should I be doing something else? The number of PostQueryNotifications is definitely unmanageable when the queue disables....let me put it this way: they are filling up the sys.conversation_endpoints and sys.transmission_queue faster than I can end and delete them gracefully. A count yielded somewhere between 800,000 and 1M generated in the last 12 hours.

|||I don't really understand your code. The SqlNotificationRequest should be attached to a SqlCommand that retrieves the data to be notified from an user table (eg. a SELECT) and the RECEIVe statement should get the notifications when the content of the SELECT changes.
Your code attaches the SqlNotificationRequest to a SqlCommand that invokes the stored procedure containing the RECEIVE. I guess you get back immedeatly the notification that the subscription query is incorrect, not an actual notification.|||

Now I don't understand. I am trying to get an application notification whenever a message arrives in a queue. When I get the notification that something is there (in the queue), I want to RECEIVE and process the message(s). So can you help me understand how the commands are to be structured to achieve that?

Thanks again.

|||

Simply issue a SqlCommand.ExecuteReader() with the statement "WAITFOR (RECEIVE ...)" without setting up any SqlNotificationRequest. The RECEIVE statement will block until a message is available to be dequeued and processed by the application. Query Notifications is a generic mechanism for detecting changes on any data table, it does not work with message queues.

|||

OK, so can you explain what is happening in the code that I submitted? By attaching that notification to the command object that is calling the stored procedure with the WAITFOR(RECEIVE...), I am getting notifications for what? Or is it that I am simply getting a bunch of errors from the notification framework, as you mentioned?

If I understand you correctly, I don't need query notifications at all - just call that stored procedure normally; its on a different thread so it will block, but not the application, until a message arrives, at which time, the reader will RECEIVE and process the message? Is that correct?

|||

You should always check the XML payload of the received notifications and see wether is an actual notification or an error emssage. In your case you probably get similar to this: <qn:QueryNotification mlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0" type="subscribe" source="statement" info="invalid" database_id="0" sid="..."><qn:Message>00000000-0000-0000-0000-000000000000</qn:Message>
</qn:QueryNotification>
The info="invalid" is the attribute that tells you that the submited query is not a valid one for notifications.

Query Notifications filling up the transmission queue

I have a web application that sets up a query notification on a queue. Normal operation works fine, but for some reason the queue being monitored disables occasionally (even with the transaction, error message, and end dialog message) pattern set up as described in Remus' blog post. When the queue disables, everything goes insane and the transmission queue, left unchecked, fills up the transmission queue with hundreds of thousands of PostQueryNotification messages. Am I doing something wrong? What should I do to mitigate this because it IS possible that the queue might disable sometimes, and I don't want drive space eaten up on a low-end SQL Express machine, if it does. Also - second question - where is the transmission queue storing all these messages? TempDB?

I still haven't found a solution to this problem - even after applying normal poison message handling within the .NET code. Anyone? What to do about these query notifications filling up the xmit queue (and therefore the disk) when the queue disables?|||

One more question with regard to this behavior:

Why does service broker start a new conversation for each one of these notifications? This seems to compound the problem by not reusing a valid conversation_handle. I hope someone can provide an answer or solution to this soon because its close to holding up an intended launch at this point. Basically, I need to know what to do when a queue becomes disabled that is being monitored by a .NET application for query notifications. It seems like maybe I have to set up the architecture to receive the event notification in the .NET application and try to recover from code, rather than receiving it in SQL Server and recovering, which is what I would rather do.

|||

Even if it would reuse the conversations, the same number of notifications (messages) would be fired, so the transmission_queue would grow to the same size.

1) Why does the queue get disabled in the first place? In a development environment this occurs more often than normal because developers tend to debug applications and abruptly intrerupt execution, thus causing rollbacks that eventually disable the queue, but in a normal/test environment this should be a rare event. The fact that the queue gets disabled too often might indicate an application problem.

2) How many notifications are fired that the rate of growth for transmission queue is a issue of concern? Normally applications subscribe to a notification and then re-post the query when they are notified. So there should be a natural limit on the number of notifications fired and not delivered, since applications would not post new queries until the previous ones are notified. From your description it seems that new quesries are posted continuosly, which would indicate a problem in the application.

3) How did you set up and process the queue_diabled notification? Why did you had problems with this?

|||

Well, here is the setup of the notification, started on a separate thread from the main thread of the web application:

using (_command =

new SqlCommand("usp_ProcessMessage", _connection))

{

_command.CommandType = CommandType.StoredProcedure;

_command.CommandTimeout = 0;

string service = "service=myService" ;

_sqlNotificationRequest = new SqlNotificationRequest(new Guid().ToString(), service, 0);

_command.Notification = _sqlNotificationRequest;

while (true)

{

...

// BeginTransaction

// ExecuteReader

// Do Processing on Message

...

}

}

The stored procedure looks like this:

CREATE PROCEDURE [dbo].[usp_ProcessMessage]

AS

BEGIN

WAITFOR ( RECEIVE TOP ( 1 ) conversation_handle, message_type_name, message_body

FROM [tcp://MyQueue] ) ;

END

When [tcp://MyQueue] becomes disabled, the problems start. The question as to why the queue is becoming disabled is still under investigation and, I agree that this happens more in development. But, I am seeing it regularly, even with measures in place to dequeue the message and try to recover. In any case, even if I can isolate what is causing the queue to disable, I still need to have a recovery model in place - JUST IN CASE. So, is there something intrinsically wrong with the code above? Should I be doing something else? The number of PostQueryNotifications is definitely unmanageable when the queue disables....let me put it this way: they are filling up the sys.conversation_endpoints and sys.transmission_queue faster than I can end and delete them gracefully. A count yielded somewhere between 800,000 and 1M generated in the last 12 hours.

|||I don't really understand your code. The SqlNotificationRequest should be attached to a SqlCommand that retrieves the data to be notified from an user table (eg. a SELECT) and the RECEIVe statement should get the notifications when the content of the SELECT changes.
Your code attaches the SqlNotificationRequest to a SqlCommand that invokes the stored procedure containing the RECEIVE. I guess you get back immedeatly the notification that the subscription query is incorrect, not an actual notification.|||

Now I don't understand. I am trying to get an application notification whenever a message arrives in a queue. When I get the notification that something is there (in the queue), I want to RECEIVE and process the message(s). So can you help me understand how the commands are to be structured to achieve that?

Thanks again.

|||

Simply issue a SqlCommand.ExecuteReader() with the statement "WAITFOR (RECEIVE ...)" without setting up any SqlNotificationRequest. The RECEIVE statement will block until a message is available to be dequeued and processed by the application. Query Notifications is a generic mechanism for detecting changes on any data table, it does not work with message queues.

|||

OK, so can you explain what is happening in the code that I submitted? By attaching that notification to the command object that is calling the stored procedure with the WAITFOR(RECEIVE...), I am getting notifications for what? Or is it that I am simply getting a bunch of errors from the notification framework, as you mentioned?

If I understand you correctly, I don't need query notifications at all - just call that stored procedure normally; its on a different thread so it will block, but not the application, until a message arrives, at which time, the reader will RECEIVE and process the message? Is that correct?

|||

You should always check the XML payload of the received notifications and see wether is an actual notification or an error emssage. In your case you probably get similar to this: <qn:QueryNotification mlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" id="0" type="subscribe" source="statement" info="invalid" database_id="0" sid="..."><qn:Message>00000000-0000-0000-0000-000000000000</qn:Message>
</qn:QueryNotification>
The info="invalid" is the attribute that tells you that the submited query is not a valid one for notifications.

Query notifications and replication

Anyone using query notifications with replication? We have a replicated db
(transactional one way) and I have to turn service broker on for an app that
will use query notifications and I was wondering what effect that will have
on replication.
Thanks in advance.
According to my understanding of NS, apart from the resultant use of system
resources, there isn't really any overlap. There could be an impact if
Notification Services is polling the same tables on the subscriber that are
being replicated to - the usual blocking that would occur with any such
queries.
Paul Ibison

Query notifications (service broker) and mirroring

Hi,
I'm trying to turn on service broker so we can use query notifications in a
mirrored database. However I get the following when I try to run ALTER
DATABASE <db>
SET ENABLE_BROKER:
Msg 9778, Level 16, State 1, Line 2
Cannot create a new Service Broker in a mirrored database "testDB".
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Can you use query notifications with a mirrored db?
Thanks in advance.
You must enable broker before the mirroring session is enabled. You should
first check if the broker is enabled in the first place
(sys.databases.is_broker_enabled). If is not, then you must stop the
mirroring session, enable the broker and re-establsih the mirroring session
from scratch.
HTH,
~ Remus
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
> Hi,
> I'm trying to turn on service broker so we can use query notifications in
> a
> mirrored database. However I get the following when I try to run ALTER
> DATABASE <db>
> SET ENABLE_BROKER:
> Msg 9778, Level 16, State 1, Line 2
> Cannot create a new Service Broker in a mirrored database "testDB".
> Msg 5069, Level 16, State 1, Line 2
> ALTER DATABASE statement failed.
>
> Can you use query notifications with a mirrored db?
> Thanks in advance.
|||Remus ,
Thanks for your reply. Will the service broker failover along with the
database?
"Remus Rusanu [MSFT]" wrote:

> You must enable broker before the mirroring session is enabled. You should
> first check if the broker is enabled in the first place
> (sys.databases.is_broker_enabled). If is not, then you must stop the
> mirroring session, enable the broker and re-establsih the mirroring session
> from scratch.
> HTH,
> ~ Remus
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
>
>
|||Yes
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4B43EA5F-3340-41DD-9407-A0ED711F2726@.microsoft.com...[vbcol=seagreen]
> Remus ,
> Thanks for your reply. Will the service broker failover along with the
> database?
>
> "Remus Rusanu [MSFT]" wrote:

Query notifications (service broker) and mirroring

Hi,
I'm trying to turn on service broker so we can use query notifications in a
mirrored database. However I get the following when I try to run ALTER
DATABASE <db>
SET ENABLE_BROKER:
Msg 9778, Level 16, State 1, Line 2
Cannot create a new Service Broker in a mirrored database "testDB".
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Can you use query notifications with a mirrored db?
Thanks in advance.You must enable broker before the mirroring session is enabled. You should
first check if the broker is enabled in the first place
(sys.databases.is_broker_enabled). If is not, then you must stop the
mirroring session, enable the broker and re-establsih the mirroring session
from scratch.
HTH,
~ Remus
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
> Hi,
> I'm trying to turn on service broker so we can use query notifications in
> a
> mirrored database. However I get the following when I try to run ALTER
> DATABASE <db>
> SET ENABLE_BROKER:
> Msg 9778, Level 16, State 1, Line 2
> Cannot create a new Service Broker in a mirrored database "testDB".
> Msg 5069, Level 16, State 1, Line 2
> ALTER DATABASE statement failed.
>
> Can you use query notifications with a mirrored db?
> Thanks in advance.|||Remus ,
Thanks for your reply. Will the service broker failover along with the
database?
"Remus Rusanu [MSFT]" wrote:

> You must enable broker before the mirroring session is enabled. You should
> first check if the broker is enabled in the first place
> (sys.databases.is_broker_enabled). If is not, then you must stop the
> mirroring session, enable the broker and re-establsih the mirroring sessio
n
> from scratch.
> HTH,
> ~ Remus
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
>
>|||Yes
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4B43EA5F-3340-41DD-9407-A0ED711F2726@.microsoft.com...[vbcol=seagreen]
> Remus ,
> Thanks for your reply. Will the service broker failover along with the
> database?
>
> "Remus Rusanu [MSFT]" wrote:
>

Query notifications (service broker) and mirroring

Hi,
I'm trying to turn on service broker so we can use query notifications in a
mirrored database. However I get the following when I try to run ALTER
DATABASE <db>
SET ENABLE_BROKER:
Msg 9778, Level 16, State 1, Line 2
Cannot create a new Service Broker in a mirrored database "testDB".
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Can you use query notifications with a mirrored db?
Thanks in advance.You must enable broker before the mirroring session is enabled. You should
first check if the broker is enabled in the first place
(sys.databases.is_broker_enabled). If is not, then you must stop the
mirroring session, enable the broker and re-establsih the mirroring session
from scratch.
HTH,
~ Remus
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
> Hi,
> I'm trying to turn on service broker so we can use query notifications in
> a
> mirrored database. However I get the following when I try to run ALTER
> DATABASE <db>
> SET ENABLE_BROKER:
> Msg 9778, Level 16, State 1, Line 2
> Cannot create a new Service Broker in a mirrored database "testDB".
> Msg 5069, Level 16, State 1, Line 2
> ALTER DATABASE statement failed.
>
> Can you use query notifications with a mirrored db?
> Thanks in advance.|||Remus ,
Thanks for your reply. Will the service broker failover along with the
database?
"Remus Rusanu [MSFT]" wrote:
> You must enable broker before the mirroring session is enabled. You should
> first check if the broker is enabled in the first place
> (sys.databases.is_broker_enabled). If is not, then you must stop the
> mirroring session, enable the broker and re-establsih the mirroring session
> from scratch.
> HTH,
> ~ Remus
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
> > Hi,
> > I'm trying to turn on service broker so we can use query notifications in
> > a
> > mirrored database. However I get the following when I try to run ALTER
> > DATABASE <db>
> > SET ENABLE_BROKER:
> >
> > Msg 9778, Level 16, State 1, Line 2
> > Cannot create a new Service Broker in a mirrored database "testDB".
> > Msg 5069, Level 16, State 1, Line 2
> > ALTER DATABASE statement failed.
> >
> >
> > Can you use query notifications with a mirrored db?
> >
> > Thanks in advance.
>
>|||Yes
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4B43EA5F-3340-41DD-9407-A0ED711F2726@.microsoft.com...
> Remus ,
> Thanks for your reply. Will the service broker failover along with the
> database?
>
> "Remus Rusanu [MSFT]" wrote:
>> You must enable broker before the mirroring session is enabled. You
>> should
>> first check if the broker is enabled in the first place
>> (sys.databases.is_broker_enabled). If is not, then you must stop the
>> mirroring session, enable the broker and re-establsih the mirroring
>> session
>> from scratch.
>> HTH,
>> ~ Remus
>> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
>> news:30FC2CF3-B85C-4B40-A4FD-D028F19CD6D8@.microsoft.com...
>> > Hi,
>> > I'm trying to turn on service broker so we can use query notifications
>> > in
>> > a
>> > mirrored database. However I get the following when I try to run ALTER
>> > DATABASE <db>
>> > SET ENABLE_BROKER:
>> >
>> > Msg 9778, Level 16, State 1, Line 2
>> > Cannot create a new Service Broker in a mirrored database "testDB".
>> > Msg 5069, Level 16, State 1, Line 2
>> > ALTER DATABASE statement failed.
>> >
>> >
>> > Can you use query notifications with a mirrored db?
>> >
>> > Thanks in advance.
>>

Query Notifications

Hi there!
Which edition of SQL Server 2005 includes the Query Notifications?
Thanks
Klaus Aschenbrenner
www.csharp.at.www.anecon.com
http://weblogs.asp.net/klaus.aschenbrennerGood news ! Query Notification is available in SQL Server Express as
Service Broker is the underlying mechanism.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Query Notification stops working after 10 minutes

Hi All

We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.

When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...

Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.

What can be the problem? is our Cache component object are being collected by GC?

Does IIS disposes the SQL Connection that the Query notification uses?

We are on a crisis...

Thanks in advance.

It sounds like there's something wrong with your server logic so this probably isn't the right newsgroup to ask. check the sys.dm_qn_subscriptions view to see if there is a subscription active. If not, then your service logic didn't create a new subscription after processing a notification. If it all worked in your test setup you might have an issue with re-entrancy in your component so maybe a lock is required to handle two notifications coming in so close together that only one suscription is registered.|||Could be the issue described in KB 913364: http://support.microsoft.com/Default.aspx?kbid=913364

HTH,
~ Remus|||

I dont see how can this be a problem on the server logic...

I am running the same code in my Console application and in my web application , the context is the difference.

I ran a check to see if the Cache object is disposed , and it doesnt, meaning the Cache component is alive , but notifications are not coming after 10 minutes,

I'll download the fix in the KB , and try it tomorrow morning...I hope this would do the trick.

Eden

|||

Well , Remus, you are right...

The FIX that i've installed on my station solved the problem (I Hope)

Notification events are coming after several hours without any problems...

Just to be sure I'll test it with a few days delay to see if every thing is ok....

I think that my issue is a big bug , and microsoft should publish it approprietly appropriately,

It took my several days to get to this forum , and to Remus answer,

Anyhow , thanks to all of you.

Query Notification question

Thanks for clarifying the destinction between query notification and event
notification.
While having the list of DO NOTS for query notifications is insightful it
doesn't really answer my question.
I suppose all that I can do is watch the entire table for change by
removing the max aggregate. I could then call a seperate refreshData routine
that utilizes the view containing the MAX aggregate to populate my datagrid
control.I'm not an expert in QN, but I believe creating a notification for the whole
table should work. If the notification would fire too often for data not
related to the current grid content, you can probably restrict the scope to
a subset of the table, using a WHERE clause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:FDE88837-BB28-47A2-928B-03EF80DCE36A@.microsoft.com...
> Thanks for clarifying the destinction between query notification and event
> notification.
> While having the list of DO NOTS for query notifications is insightful it
> doesn't really answer my question.
> I suppose all that I can do is watch the entire table for change by
> removing the max aggregate. I could then call a seperate refreshData
> routine
> that utilizes the view containing the MAX aggregate to populate my
> datagrid
> control.

Query Notification question

I am having issues getting a SqlDependency proof of concept app to work. I
took a step back and performed the MSDN lab titled SQL Server and ADO.NET
(LabB), located here:
http://msdn.microsoft.com/vstudio/t...l/default.aspx. It contain
s
an example using SqlDependency to monitor a simple SELECT statement on the
AdventureWorks Person.Contact table. This example works fine (after I add
the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
constructor and destructor, respectively. I have one issue: when I revise
the table involved in the SELECT statement to include a computed field (I
created a FullName column in the Person.Contact table), the SqlDependency's
OnChange event fires repeatedly. The following items show in the
SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source: Statement.
Subscription occurs repeatedly and I'm in an infinite loop.
I can't seem to find a rule that says I cannot use a computed field in a
table invloved in my SQL statement for use with SqlDependency. Can anyone
confirm that computed fields are NOT allowed when desinging a query for use
with query notification?SQL Server BOL has a list of what can't be used in a statement with Query
Notifications. I don't remember computed column, but do remember than all
aggregates other than SUM are forbidden. Don't forget that you must be two
part object names too. If you use an invalid query you should get a
notification with a reason of "invalid query". Have a look at the fields in
your notification when you receive it.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>I am having issues getting a SqlDependency proof of concept app to work. I
> took a step back and performed the MSDN lab titled SQL Server and ADO.NET
> (LabB), located here:
> http://msdn.microsoft.com/vstudio/t...l/default.aspx. It
> contains
> an example using SqlDependency to monitor a simple SELECT statement on the
> AdventureWorks Person.Contact table. This example works fine (after I add
> the SqlDependency.Start() and SqlSDependency.Stop() methods in the class's
> constructor and destructor, respectively. I have one issue: when I revise
> the table involved in the SELECT statement to include a computed field (I
> created a FullName column in the Person.Contact table), the
> SqlDependency's
> OnChange event fires repeatedly. The following items show in the
> SqlNotificationEventArgs - Type: Subscribe, Info: Query, Source:
> Statement.
> Subscription occurs repeatedly and I'm in an infinite loop.
> I can't seem to find a rule that says I cannot use a computed field in a
> table invloved in my SQL statement for use with SqlDependency. Can anyone
> confirm that computed fields are NOT allowed when desinging a query for
> use
> with query notification?|||Thanks for the reply. I've looked at the BOL, and I can find no specifc
mention of computed fields being "against the rules." The value of the Info
member in the SqlNotificationEventArgs is Query, which means "A SELECT
statement that cannot be notified or was provided." according to MSDN2. The
value of the Type member is Subscribe, which means "There was a failure to
create a notification subscription. Use the SqlNotificationEventArgs object'
s
SqlNotificationInfo item to determine the cause of the failure." This leads
me to believe the same SELECT statement which works on a table with no
computed field suddenly fails the criteria check after I add the computed
field. I guess at this point I am just looking for confirmation that
computed fields are not allowed if you want to use Query Notifications.
Thanks again,
Chris
"Bob Beauchemin" wrote:

> SQL Server BOL has a list of what can't be used in a statement with Query
> Notifications. I don't remember computed column, but do remember than all
> aggregates other than SUM are forbidden. Don't forget that you must be two
> part object names too. If you use an invalid query you should get a
> notification with a reason of "invalid query". Have a look at the fields i
n
> your notification when you receive it.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:71209A32-7B18-4D89-A204-551C9D185889@.microsoft.com...
>
>|||Hi Chris,
That would just about clinch it as an invalid query for me... I don't
remember computed field being against the rules, but you're being told that
it is. What's the computed field look like exactly?
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
> Thanks for the reply. I've looked at the BOL, and I can find no specifc
> mention of computed fields being "against the rules." The value of the
> Info
> member in the SqlNotificationEventArgs is Query, which means "A SELECT
> statement that cannot be notified or was provided." according to MSDN2.
> The
> value of the Type member is Subscribe, which means "There was a failure to
> create a notification subscription. Use the SqlNotificationEventArgs
> object's
> SqlNotificationInfo item to determine the cause of the failure." This
> leads
> me to believe the same SELECT statement which works on a table with no
> computed field suddenly fails the criteria check after I add the computed
> field. I guess at this point I am just looking for confirmation that
> computed fields are not allowed if you want to use Query Notifications.
> Thanks again,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Bob,
Here's the computed field formula for the FullName column I added to the
Person.Contact table:
(ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
')+isnull([LastName],'')))
I would like to add that the SELECT statement I'm using with the
SqlDependency object is NOT referencing the computed field. The statement i
s
(right out of the MSDN lab):
SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
What bothers me is that the computed field is deterministic. The only
variable changing in the scenario is the addition of this computed field,
which is NOT being referenced in my SELECT statement. If you remove the
computed field, the example again works flawlessly.
Thanks for the replies,
Chris
"Bob Beauchemin" wrote:

> Hi Chris,
> That would just about clinch it as an invalid query for me... I don't
> remember computed field being against the rules, but you're being told tha
t
> it is. What's the computed field look like exactly?
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:9D3DA5B1-411E-4DB6-8C9A-DCF8CEE4AA4C@.microsoft.com...
>
>|||Yep, at first glance I don't see anything wrong with this. Let me try it out
and experiment with some permutations. It may take a few minutes.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
> Bob,
> Here's the computed field formula for the FullName column I added to the
> Person.Contact table:
> (ltrim((rtrim((isnull([FirstName],'')+' ')+isnull([MiddleName],''))+'
> ')+isnull([LastName],'')))
> I would like to add that the SELECT statement I'm using with the
> SqlDependency object is NOT referencing the computed field. The statement
> is
> (right out of the MSDN lab):
> SELECT ContactID, FirstName, LastName, EmailAddress FROM Person.Contact
> What bothers me is that the computed field is deterministic. The only
> variable changing in the scenario is the addition of this computed field,
> which is NOT being referenced in my SELECT statement. If you remove the
> computed field, the example again works flawlessly.
> Thanks for the replies,
> Chris
>
> "Bob Beauchemin" wrote:
>|||Hi Chris,
I just tried this. Worked fine for me here (don't ya just hate that answer).
I tried with your expression, only thing I added was the alias "as Fullname"
but even without the alias, worked fine. Worked OK, I caught the
notification and processed it. Send me mail (by figuring out email from the
obfuscated version) and I'll send you the code.
What version are you using? I'm using the RTM version.
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
> Yep, at first glance I don't see anything wrong with this. Let me try it
> out and experiment with some permutations. It may take a few minutes.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "ChrisAtPhaseWare" <ChrisAtPhaseWare@.discussions.microsoft.com> wrote in
> message news:E31A1CFB-5225-4E18-A626-C16B1C6897C0@.microsoft.com...
>|||Bob,
If I had a dollar for every time I told QA "it works on my machine" I'd be
wildy rich.
I've read that the connection used to send the SQL statement must adhere to
some standards as well, this might be an issue as I can't think of what else
is different between my setup and yours. I am using the latest and greatest
releases of Visual Studio 2005 and SQL server 2005, no Beta stuff here. I'l
l
let you know how it works.
Thanks for the help,
Chris
"Bob Beauchemin" wrote:

> Hi Chris,
> I just tried this. Worked fine for me here (don't ya just hate that answer
).
> I tried with your expression, only thing I added was the alias "as Fullnam
e"
> but even without the alias, worked fine. Worked OK, I caught the
> notification and processed it. Send me mail (by figuring out email from th
e
> obfuscated version) and I'll send you the code.
> What version are you using? I'm using the RTM version.
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "Bob Beauchemin" <no_bobb_spam@.sqlskills.com> wrote in message
> news:u9ELrYU9FHA.2792@.TK2MSFTNGP11.phx.gbl...
>
>|||I need some help with a formula for the Formula Field(Computed Column)
In the Table Designer there is a column field called Formula. I have success
fully used this column on a couple of occasions, but I am stuck this time.
I have a column called Quantity (Char) and a column called NN (Char). When Q
uantity = 0 I want NN to = Complete. When Quantity is <> 0 I want NN to =
Incomplete.
My Formula for NN, in the Formula Field, is;
(CASE WHEN [Quantity] = 0 THEN Complete ELSE Incomplete END)
I have also tried ;
IIF([Quantity]="0", "Complete", "Incomplete")
Neither work.
Robert

Query notification problem

I've got an application that's using query notifications with a two-user setup (a user that starts the dependency running, and an application user that subscribes to it). I'm having problems getting the query notifications to go through however... according to the Profiler, when the application user does something that triggers a query notification, it causes this error:

This message could not be delivered because the user with ID [application user] in database ID [database] does not have permission to send to the service. Service name: SqlQueryNotificationService-[guid].

The problem is that [guid] is different each time, and there doesn't seem to be a generic "grant send on all services" statement, so I can't grant send on the particular service in question to the application user to get rid of the error. (If I grant db_owner to the application user, the problem goes away and everything works, but I'd rather not do that. The application user already has subscribe query notifications, receive on QueryNotificationErrorsQueue, and references on contract::...PostQueryNotification.)

Obviously I'm doing something wrong, but I am completely out of ideas and can't seem to find anything new to try. Any ideas? Thanks.

It is not clear to me how can an application separate the roles of creating the notification and getting the notification using SqlDependency (which is the component that creates the 'SqlQueryNotificationService-[guid]' named services). The SqlDependency is intended to be used by one appdomain that calls Start and then various callbacks (notification subscriptions) are being set up by the same appdomain.

Do you have a particular reason why you want to separate the two roles?

Utimately you can always go one level down and use the SqlNotification objects instead of SqlDependency. They are more primitive (no automated provisioning of a temp service/queue/procedure for each appdomain etc etc), but they allow more control as you can specify explicitly the service that is intended to receive the notifications and you can set up said service as your heart desires (including all the necessary GRANTs).

|||The reason for separating them is that the user starting the dependency has to have a bunch of create privileges in its default schema, and for security reasons we don't want the application user to have such access. By isolating the user doing the start in its own schema, we can keep the application user and the main schema locked down.

It turns out that there is a solution, however... by Reflecting through the SqlDependency class, I determined that it's hard-wired to look for a role called "sql_dependency_subscriber", and if that role exists it grants the necessary privileges to it upon startup. By creating the role and adding the application user to it, everything works. Seems like a major kludge on Microsoft's part to me, and it would have helped if it was documented somewhere, but it does the trick nonetheless.
|||Well, that is news for me as well. I never knew SqlDependency does that.

Query Notification not fire all subscriptions...

Hello all,

We have encountered a weird problem; we have an application that registers to several queries for notification.

For several weeks everything worked fine, any change to any query result in the database triggered the notification event, but since yesterday the mystery began… since yesterday not all changes triggers events , changing one table raise the notification event, changing the other doesn’t , all seems fine in the database , the application reports that all queries were registered successfully , in the subscription view in the database we can see that all the queries that the application subscribed to are present…

What can be the problem? Is it something in the database? Can it be a problem in our application, please keep in mind that everything worked until yesterday…

Thanks,

Eden

Check sys.transmission_queue in the database where the events are registered. If it contains undelivered notifications, check the transmission_status column, it should tell you the reason why the messages cannot be delivered.|||

Thanks for your answer Remus

Unfortunately our Dba’s deleted the transmission queue, and now it’s all working again,

The Dba’s said that the transmission queue is getting very large (even millions of records) with the Conversing status.

Can the queue overflow be the problem?

Thanks

|||

No, that's not the problem. Queues don't 'overflow'. The transmission queue cannot be deleted, so the dba must had eaither terminated the dialogs with cleanup or started a new broker in the database.

If millions of conversation in 'Conversing' state accumulate, it means you notification are not being delivered. Again, the transmission_status of column will indicate the cause.

Query Notification Error

Hi All

In Work , we created a Cache component that serves a large business application, we are subscribing to almost 200 queries , the component worked quite well in the first few months of the development, but lately i am getting notification events that contain "Error" in the SqlNotificationEventArgs of the event , the "Source" value is "Client".

I have no idea what might cause this error event , we are close to "go live" with our product and we cannot depend on the Query Notification mechanism until this problem is solved.

Can it be a DataBase problem? Application Problem?

Thanks in advance

Eden

Can you identify which queries contain errors, which don't? Is there a pattern?

Most likely cause is that your application is issuing queries that are not valid for Query Notifications, see http://msdn2.microsoft.com/en-US/library/ms181122.aspx

|||

Thanks Remus,

That was my initial suspicion too.

I'll update each table , and try to identify the bad query..

I'll update you soon.

|||

After installing Service Pack 2 on the SQL Server , i've encountered the Notification Error again but now , in my DbgView

windows i can see just before the notification error the following lines:

[1644] ReportingServicesService!library!e!5/7/2007-11:10:05:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
[2628] System.Transactions Critical: 0 :
[2628] <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>DefaultDomain</AppDomain><Exception><ExceptionType>System.Threading.ThreadAbortException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Thread was being aborted.</Message><StackTrace> at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
[2628] at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
[2628] at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
[2628] at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Threading.ThreadAbortException: Thread was being aborted.
[2628] at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
[2628] at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
[2628] at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
[2628] at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

I dont know if its realted to the problem , but its looking very suspicious.

|||This looks like an exception in the client, not in the server. You better ask this question on a CLR forum.

Query Notification / SQLDependency fails on table w/ computed column

I have a table with a computed column defined on it. I can't get SQLDependency to work with that table unless I remove the computed column definition. The select statement I am using does not include the computed column, it just exists on the table.

When I execute the SQLCommand, the SQLDependency immediately fires the OnChange event with a SQLNotificationEventArgs.Info of 8 {Query}. I can't find any documentation in SQL Books Online that the base table must not have a computed column, even if the select does not include that column. Is this a bug or by design? Thanks.

Here is a sample create table statement:

create table dbo.test

(id int,datechanged datetime

,mycomputedcolumn as (1+1)

)

go

insert into dbo.test (id,datechanged)

values (46,getdate())

And here is a sample VB program that duplicates my problem (the sub cb will be called immediately with a e.Info=8)

Module Module1

Sub Main()

Dim connString As String = "server=paulg\yukon;database=bbinfinity;integrated security=sspi"

SqlClient.SqlDependency.Start(connString)

Dim conn As New SqlClient.SqlConnection(connString)

conn.Open()

Dim cmd As SqlClient.SqlCommand = conn.CreateCommand

cmd.CommandText = "select id,datechanged from dbo.test;"

cmd.Connection = conn

Dim dep As New SqlClient.SqlDependency(cmd, Nothing, 500000)

AddHandler dep.OnChange, AddressOf cb

Dim o As Object = cmd.ExecuteScalar

Console.ReadLine()

End Sub

Sub cb(ByVal sender As Object, ByVal e As SqlClient.SqlNotificationEventArgs)

Debug.WriteLine(e.ToString)

End Sub

End Module

This is a behavior of the underlying notification engine in the server. I'm moving the thread to a server forum.|||

Paul,

Using a query notification on a table with a computed field is NOT supported by Microsoft. I was told it would be added to the BOL in the list of scenarios not supported, but only after I reported it as a bug.

Chris

|||

Thanks for the reply. It helps to know that it is officially not supported vs. something I was doing wrong.

Seems like a bizarre limitation to me. I always thought computed columns were just some extra metadata with very little cost that were convenient when you had common expressions you knew you might need frequently. Under that impression, I've used them all over the place where I know I'll often need an expression (such as a CASE..WHEN statement) to avoid having to repeat the expression in every TSQL statement that needs that logic. I know I can use UDFs but my experience shows that in-line expressions can be much faster than UDFs, so in simple cases where inline expressions work I try to use them. Computed columns seems like a great way to have the re-usability of UDFs with the performance of in-line expressions. But now I'm scared that computed columns are more "special" than one would think.

Obviously there are many workarounds so I'll get by. But this still smells to me like something that must have just been overlooked rather than a well reasoned design choice. Thanks again for the reply.

|||

No problem. It was pretty frustrating for me as well. It seemed that some people I talked to still using the Beta versions of Visual Studio could get this to work, but I never could. It took over a month for MS to tell me that it was not supported after I reported it as a bug. Luckily we didn't have too many computed fields in the tables we wanted to use for query notifications, but it was still a major annoyance.

Query Notification & Windows Service

I could get Query Notification working for a windows forms client using the
SQL Dependency object but the same code doesn't work from a simple windows
service.
The OnChange event doesn't seem to be raised up to the windows service.
Looking at the SQL trace, it doesn't look like the a notification is sent to
the windows service from the SQL server.
Appreciate your response.
RamaThe notifications are sent using Service Broker, try following the steps in
this post http://blogs.msdn.com/remusrusanu/a.../20/506221.aspx
to figure out the cause.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Rama" <rama.bhandaru@.eclipsys.com> wrote in message
news:en6X%23S$XGHA.1192@.TK2MSFTNGP03.phx.gbl...
>I could get Query Notification working for a windows forms client using the
> SQL Dependency object but the same code doesn't work from a simple windows
> service.
> The OnChange event doesn't seem to be raised up to the windows service.
> Looking at the SQL trace, it doesn't look like the a notification is sent
> to
> the windows service from the SQL server.
> Appreciate your response.
> Rama
>

Query Notification - Setup Problems

Hi Guys,

I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!

I've followed example found on the internet in the following steps:

1.

ALTER DATABASE <dbname> SET ENABLE_BROKER

2.

GRANT CREATE PROCEDURE TO <user>

GRANT CREATE QUEUE TO <user>

GRANT CREATE SERVICE TO <user>

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>

GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>

3.

ALTER DATABASE <dbname> SET TRUSTWORTHY ON

The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:

SELECT * FROM sys.dm_qn_subscriptions

There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.

Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:

exec sp_executesql N'END CONVERSATION @.p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @.p2;',N'@.p2 int,@.p1
uniqueidentifier',@.p2=60000,@.p1='BD98F6B4-A133-DC11-A751-0050568146E8'

This occurs when they refresh the page.

Any ideas?

Cheers!

Ian

You should place the SqlDependency.Start in the appdomain load event. From your description it seems to be placed on the page load.

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Query Notification - checking permissions

Hi all,

I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:

CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue

I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....

Finally, I have also run into the problem whereby SQL issues the following error:

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEA\DyerN', error code 0x54b.'

I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.

Many Thanks, Nick

The SQL Server instance in question cannot communicate with the Active Directory to validate the Windows accounts (like EMEA\DyerN) and hits error 0x54b, which is ERROR_NO_SUCH_DOMAIN. You should contact your network/security administrator to diagnose and troubleshoot the problem, as is unrelated to Service Broker or Query Notifications (are you working on a laptop disconnected from domain by any chance?) Alternatively, you could use SQL users/logins instead of Windows users/logins.|||

This was true, I was disconnected from the domain. My hope however was for the query notification to fire signally that an error had occurred and change notifcations could not be delivered. This would allow me to handle it programatically and re-register with different credentials or fall back to polling for data changes.

Currently as it stands if I get disconnected from the domain my query notifications will simply stop and I will receive no notification that an error has occurred. This does not leave me with a robust solution.

Thanks, Nick

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

|||

NickUk wrote:

BTW An idea on determine if I have the correct permissions to RECEIVE on QueryNotificationErrorsQueue ?

select * from sys.fn_my_permissions('dbo.QueryNotificationErrorsQueue','object')

|||

Query Notifications run 'execute as owner' so if a database is owned by a domain account, query notifications fail in the event a domain control cannot be contacted

Basically, change owner to SA to avoid the issue.

See this writeup

http://aspadvice.com/blogs/ssmith/archive/2006/11/06/SqlDependency-Issue-Resolved.aspx

Query Notification

Hi,
I am using .net 2.0 and sql server 2005.
Now i want to notify user Application (C# Application) about the change in certain table.
For Example: if tbl1 be any table and when insert operation is carried out in that table.
Then after 100 or multiple of 100 record insertion in tbl1 i want the notification to be given to user Application (C# Application).
Is this possible through Query Notification.?
I even dont know if this is the right forum for this question..

Please Help

It is a common mistake to confuse Query Notifications with Notification Services.

Query Notification

Hi,
I have problem with SqlDependency and could not use any sample in VB.NET
2005. Some samples I found in internet, use Start mehod that I don't have in
my SqlDependency instance. And some of them use Id and Servie properties of
SqlNotificationRequest that VS IDE suggests using UserData instead of them.
I would be most grateful if anybody could post a URL to VB sample so that I
can use.
I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
Thanks again,
Leila
BTW: Where is SQL-CLR group'!! :(You should upgrade to RTM if possible; changes to SqlDependency occurred in
the final CTP (September); I'm not sure if there were any more changes
between then and RTM.
Also, keep in mind that the Start method is static. You don't call it per
SqlDependency object, but rather for the entire application.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have problem with SqlDependency and could not use any sample in VB.NET
> 2005. Some samples I found in internet, use Start mehod that I don't have
> in
> my SqlDependency instance. And some of them use Id and Servie properties
> of
> SqlNotificationRequest that VS IDE suggests using UserData instead of
> them.
> I would be most grateful if anybody could post a URL to VB sample so that
> I
> can use.
> I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
> Thanks again,
> Leila
> BTW: Where is SQL-CLR group'!! :(
>|||Thanks indeed Adam,
Another question: Can I receive the notifications generated by EVENT
NOTIFICATION (such as DDL events) in my win app?
Finally I could do this for query notifications but I'd like to know if the
same thing can be done for other messages?
Leila
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23270H5m5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> You should upgrade to RTM if possible; changes to SqlDependency occurred
> in the final CTP (September); I'm not sure if there were any more changes
> between then and RTM.
> Also, keep in mind that the Start method is static. You don't call it per
> SqlDependency object, but rather for the entire application.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
>

Query Notification

Hi,
I have problem with SqlDependency and could not use any sample in VB.NET
2005. Some samples I found in internet, use Start mehod that I don't have in
my SqlDependency instance. And some of them use Id and Servie properties of
SqlNotificationRequest that VS IDE suggests using UserData instead of them.
I would be most grateful if anybody could post a URL to VB sample so that I
can use.
I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
Thanks again,
Leila
BTW: Where is SQL-CLR group?!!
You should upgrade to RTM if possible; changes to SqlDependency occurred in
the final CTP (September); I'm not sure if there were any more changes
between then and RTM.
Also, keep in mind that the Start method is static. You don't call it per
SqlDependency object, but rather for the entire application.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have problem with SqlDependency and could not use any sample in VB.NET
> 2005. Some samples I found in internet, use Start mehod that I don't have
> in
> my SqlDependency instance. And some of them use Id and Servie properties
> of
> SqlNotificationRequest that VS IDE suggests using UserData instead of
> them.
> I would be most grateful if anybody could post a URL to VB sample so that
> I
> can use.
> I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
> Thanks again,
> Leila
> BTW: Where is SQL-CLR group?!!
>
|||Thanks indeed Adam,
Another question: Can I receive the notifications generated by EVENT
NOTIFICATION (such as DDL events) in my win app?
Finally I could do this for query notifications but I'd like to know if the
same thing can be done for other messages?
Leila
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23270H5m5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> You should upgrade to RTM if possible; changes to SqlDependency occurred
> in the final CTP (September); I'm not sure if there were any more changes
> between then and RTM.
> Also, keep in mind that the Start method is static. You don't call it per
> SqlDependency object, but rather for the entire application.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
>

Query Not Working Right!

Hello All,
I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. ThesearchString,Typeand theLocation (where the user who is searching belongs to).
When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.

For example:
Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c.
So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters.
Any help would be greatfull.

Query:

SELECT
Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate

FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID

WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @.SR + '%' OR [Client.SSN] LIKE '%' + @.SR + '%' OR [Client.LastName] LIKE '%' + @.SR + '%' OR [Client.FirstName] LIKE '%' + @.SR + '%' OR [Client.MiddleName] LIKE '%' + @.SR + '%' )

The last search criteria in your query will return rows when anyone of the[ClientDocuments.LeafName]/[Client.SSN]/[Client.LastName]/ [Client.FirstName][Client.MiddleName] contains@.SR, right? Make sure you need to do such a search, because there are so many fields to be searched so maybe some field meets the criteria while you're not aware of this. BTW, LIKE '%' + @.SR + '%'means any string contains@.SR, not only string which starts with@.SR|||

Start by either deleting all your brackets or use them correctly. Brackets are for quoting identifiers. The period in Client.MiddleName separates two identifiers (Table Name, and Field Name). [Client.MiddleName] would be a field named... Client.MiddleName. It's incorrect usage, and while it might fly sometimes, you should fix it. It's possibly causing the SQL Parser to go a little crazy.

The brackets as is are not necessary since neither Client nor MiddleName, etc have any characters in it that would otherwise be illegal without. You specify it as either Client.MiddleName or [Client].[MiddleName], but not [Client.MiddleName].

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

|||

Motley:

You should note also that while in your example text, you specify there are 3 documents for LocationID of '2', your query says "Client.LocID = '3'". Typo?

Haha, Hey I did not even see that, yeah it is a typo. Anyways, I will try breaking out the brackets. But do you think that the brackets would have cause the query to act funny?|||Looking it over, it appears to me that everything else is ok, but then again, sometimes I miss the obvious. Your logic is sound atleast.|||

Hey Thanks everyone for there help. I got the query to work perfectly.

Thanks again.

query not working

Hi
look at the queries
1.
Examination_timetable consists of rows like this
ClassId ExamDate SubId Invigilation
CLD00001 3/12/2006 SUB00001 STA00001,STA00002
select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001'
then i got following correct answer which is correct.
invigilation
STA00001,STA00002
2.
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
('STA00001','STA00002')
then i didnt get any rows which is also correct, since except
STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
teaching staff's id's).
3.
now i joine teh both 1st and 2nd queries like this
select staffid from staff_details,faculty_type where facultytype='teaching'
and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
(select invigilation from Examination_timetable where examdate > '2/12/2006'
and subid='SUB00001' and classid='CLD00001')
then i'm getting result like this, whics is wrong result
StaffId
STA00001
STA00002
it shouldn't display any rows since no rows matches the given condition.
why it is displaying wrong answer when i mixed 2 separates queries in to
single query, where both individually giving correct answer?
hope you got my query.
can any one tell me where i went wrong?
thanx in advance
yoshithaHow about
select staffid from staff_details INNER JOIN faculty_type
ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
Examination_timetable ON staff_details.staffid NOT LIKE '%' +
Examination_timetable.Invigilation + '%'
where facultytype='teaching'
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||Your usage of IN is wrong. When you get the "invigilationID", it is a comma
separated string. However, for IN to search through this, you cannot
substitute this string directly for the IN clause, but rather, you need to
do it dynamically. Here is an example that shows how it does not work:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN 'Test1' IN (@.testString) THEN 1 ELSE 0 END
=====
When you execute the above snippet, you will always get 0 (although Test1 is
present inside the string). To get around this problem, you can do the
following
(1) Use dynamic SQL to build your query as shown:
=====
DECLARE @.testString VARCHAR(100)
DECLARE @.strSQL VARCHAR(8000)
SET @.testString = '''Test1'', ''Test2'''
SET @.strSQL = 'SELECT CASE WHEN ''Test1'' IN (' + @.testString + ') THEN 1
ELSE 0 END'
EXEC (@.strSQL)
=====
(2) Use CHARINDEX as shown:
=====
DECLARE @.testString VARCHAR(100)
SET @.testString = 'Test1, Test2'
SELECT CASE WHEN CHARINDEX('Test1', @.testString) > 0 THEN 1 ELSE 0 END
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Hi
> look at the queries
> 1.
> Examination_timetable consists of rows like this
> ClassId ExamDate SubId Invigilation
> CLD00001 3/12/2006 SUB00001 STA00001,STA00002
>
> select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001'
>
> then i got following correct answer which is correct.
> invigilation
> STA00001,STA00002
>
> 2.
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> ('STA00001','STA00002')
> then i didnt get any rows which is also correct, since except
> STA00001,STA00002 now rows satisfies that condition (STA00001,STA00002 are
> teaching staff's id's).
>
> 3.
> now i joine teh both 1st and 2nd queries like this
> select staffid from staff_details,faculty_type where
> facultytype='teaching'
> and staff_details.ftypeid=faculty_type.ftypeid and staffid not in
> (select invigilation from Examination_timetable where examdate >
> '2/12/2006' and subid='SUB00001' and classid='CLD00001')
>
> then i'm getting result like this, whics is wrong result
> StaffId
> STA00001
> STA00002
> it shouldn't display any rows since no rows matches the given condition.
> why it is displaying wrong answer when i mixed 2 separates queries in to
> single query, where both individually giving correct answer?
> hope you got my query.
> can any one tell me where i went wrong?
> thanx in advance
> yoshitha
>
>
>|||still it is displaying both ids.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
> How about
> select staffid from staff_details INNER JOIN faculty_type
> ON staff_details.ftypeid=faculty_type.ftypeid INNER JOIN
> Examination_timetable ON staff_details.staffid NOT LIKE '%' +
> Examination_timetable.Invigilation + '%'
> where facultytype='teaching'
>
>
> "yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
> news:OFIpxnGMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>|||Well, can you post DDL+ sample data + expected result for both tables?
"yoshitha" <gudivada_kmm@.yahoo.co.in> wrote in message
news:e9CoRrHMGHA.2992@.tk2msftngp13.phx.gbl...
> still it is displaying both ids.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eO5oVEHMGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Query not using the right index

Hi,
Since a day, Queries that run on a specific
table "Transit" are not using the right index.
I need to add "with (index=X2,readuncommitted)" to the
query to ensure it uses the right one.
The first thing i should do is to REINDEX the table.
BUT, is there something else (less long) i could do
instead of REINDEX ?
Thanks,
DonHi,
Verify before something if the order (ASC or DESC) of the index is attended.
If not, try using the following command: "dbcc freeproccache" that will
free your procedures cache.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Donald" <Donald.huppe@.bocenor.com> wrote in message
news:0d2301c3721e$c3577560$a101280a@.phx.gbl...
> Hi,
> Since a day, Queries that run on a specific
> table "Transit" are not using the right index.
> I need to add "with (index=X2,readuncommitted)" to the
> query to ensure it uses the right one.
> The first thing i should do is to REINDEX the table.
> BUT, is there something else (less long) i could do
> instead of REINDEX ?
> Thanks,
> Don|||If reindexing works, then most likely running UPDATE STATISTICS will
solve the problem as well.
Gert-Jan
Donald wrote:
> Hi,
> Since a day, Queries that run on a specific
> table "Transit" are not using the right index.
> I need to add "with (index=X2,readuncommitted)" to the
> query to ensure it uses the right one.
> The first thing i should do is to REINDEX the table.
> BUT, is there something else (less long) i could do
> instead of REINDEX ?
> Thanks,
> Don

Query not using index

Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:
> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> > Hi,
> >
> > I have a client who is running a query (generated from his ERP system)
> that
> > is taking 2-3 minutes to run, doing a full table scan. I tried the same
> > thing in my database with the same database (tables etc.) and it uses an
> > index and takes a couple of seconds to run. I have checked that he has
> the
> > same index as me in place and he has re-indexed the table.
> >
> > In addition he has Auto Create Statistics and Auto Update Statistics
> turned
> > on (same as me). I'm now running low on ideas. Can anybody give me a few
> > more pointers.
> >
> > Thanks in advance
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
--
(Please reply only to the newsgroup)

Query not using index

Hi,
I have a client who is running a query (generated from his ERP system) that
is taking 2-3 minutes to run, doing a full table scan. I tried the same
thing in my database with the same database (tables etc.) and it uses an
index and takes a couple of seconds to run. I have checked that he has the
same index as me in place and he has re-indexed the table.
In addition he has Auto Create Statistics and Auto Update Statistics turned
on (same as me). I'm now running low on ideas. Can anybody give me a few
more pointers.
Thanks in advanceHi,
Execute DBCC SHOW_STATISTICS on that table and check when the statistics was
last updated.
As well as, Could you please run a DBCC DBREINDEX on that table and check
the query execution plan.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP
> system) that is taking 2-3 minutes to run, doing a full table scan.
> I tried the same thing in my database with the same database (tables
> etc.) and it uses an index and takes a couple of seconds to run. I
> have checked that he has the same index as me in place and he has
> re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
> turned on (same as me). I'm now running low on ideas. Can anybody
> give me a few more pointers.
> Thanks in advance
If you can try running DBCC DBREINDEX on this table. This is an offline
operation and the table will not be available during the rebuild.
As an alternative, you could try running DBCC INDEXDEFRAG on the table
which is an online operation.
Or you could try running Update Statistics on the table.
Using an index (or not) also depends on server utilization and memory,
table fragmentation, statistics, etc.
How many rows are returned from the query on the table where you expect
the index to be used? How many rows are in the table?
David G.|||Thanks for that Hari,
I got him to run the show_statistics command and it was last updated two
days ago. I asked him to run a DBREINDEX today when everyone is off the
system
Thanks for your help
Michael
"Hari Prasad" wrote:

> Hi,
> Execute DBCC SHOW_STATISTICS on that table and check when the statistics w
as
> last updated.
> As well as, Could you please run a DBCC DBREINDEX on that table and check
> the query execution plan.
>
> Thanks
> Hari
> MCDBA
>
> "Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
> news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> that
> the
> turned
>
>|||Hi David,
Thanks for your comments. The table contains 19,000 rows. The statistics
were last updated two days ago. I have asked him to run a dbcc reindex when
everyone is off the system tonight.
Michael|||Hi,
Since there is only 19,000 records you could execute DBCC INDEXDEFRAG during
online as David recommended.
Thanks
Hari
MCDBA
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:20DF83D2-A9FE-4E74-90CF-BC73E9991DA5@.microsoft.com...
> Hi David,
> Thanks for your comments. The table contains 19,000 rows. The statistics
> were last updated two days ago. I have asked him to run a dbcc reindex
when
> everyone is off the system tonight.
> Michael
>|||I want to give more infromation to you.
Though the value of rowmodctr(column of sysindexes table) is smaller than
the threashold that statistics is updated automatically on, some performance
problem can occur on your system because of huge table on AUTO UPDATE
STATISTICS.
Then you may update statistics periodically.
Hanky
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||Need to view execution plans for both databases. BTW, check to see if the
servers have different parallism settings.
"Michael Gill" <Michael Gill@.discussions.microsoft.com> wrote in message
news:CFC35F6A-D45C-4875-868F-25E9F21D2040@.microsoft.com...
> Hi,
> I have a client who is running a query (generated from his ERP system)
that
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has
the
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics
turned
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance|||If the 'slow' server is an SMP system, try disabling parallel processing
for that query by adding the OPTION (MAXDOP 1).
Gert-Jan
Michael Gill wrote:
> Hi,
> I have a client who is running a query (generated from his ERP system) tha
t
> is taking 2-3 minutes to run, doing a full table scan. I tried the same
> thing in my database with the same database (tables etc.) and it uses an
> index and takes a couple of seconds to run. I have checked that he has th
e
> same index as me in place and he has re-indexed the table.
> In addition he has Auto Create Statistics and Auto Update Statistics turne
d
> on (same as me). I'm now running low on ideas. Can anybody give me a few
> more pointers.
> Thanks in advance
(Please reply only to the newsgroup)