SQL Service Broker Forum

Calling SSIS packages from a Service Broker Queue

Mon, 26 Feb 2007 06:08:22 Z

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

Problem with sending mail On Database Mail SQL Server 2008

Mon, 23 Nov 2009 07:59:50 Z

I want to send a simple mail using DATABASE MAIL feature in SQL SERVER 2008.

I've defined a public profile.

but I can't send a mail with sp_send_dbmail stored procedure in 'msdb' database .

when I execute sp_send_dbmail in the Managment Studio the message is
"Mail queued" but the mail is not sent.

here is the log file after executing sp_send_dbmail:

1) "DatabaseMail process is started"

2)The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2009-11-22T16:17:17). Exception Message: Cannot send mails to mail server. (Failure sending mail.).

please help me.

SamiraEm

SqlDependency OnChange Event Fires repeatedly

Thu, 31 Jul 2008 14:27:42 Z

there is similar thread about SQL dependency....

I have sqldep. on one form in my app...it looks like when user close form and reopen Onchange event fires repeatedly....

Do I need to close whole application...

Is Service Broker Enabled?

Wed, 20 Dec 2006 16:48:55 Z

Hello,

I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?

Mike

Service Broker and MSMQ

Thu, 19 Nov 2009 15:57:19 Z

One of our developers thinks that Service Broker is "built on" or relies on MSMQ. Is that correct?

TIA,

barkingdog

Service Broker availability

Thu, 19 Nov 2009 15:55:26 Z

Is Service Broker available in Sql 2008 Standard, Enteprrise edition only, or both editions?

TIA,

Barkingdog

How to know what BRKR TASK (or any background task for that matter) is doing?

Thu, 03 Apr 2008 21:48:54 Z

Sometimes we have some pretty serious CPU spikes and I would like to be able to pinpoint the exact issue. I have been able through the DMVs to associate a session with an operating system thread. That's great if I have a batch that's causing the problem with a non NULL sql_handle. However, how do I further narrow down what is going on with the background service broker tasks using the DMVs? Or am I just at the blind mercy of SQL Server at this point? What can I query to know what the heck is going on?

Memory issues with SERVICE_BROKER

Wed, 11 Nov 2009 17:02:28 Z

Hi

We are getting "Insufficient memeory available buffer pool" alerts in sql Error log. I looked into the error log and found there is a memory dump created with it.

This server is runnign service broker on it.

Error:
2009-11-11 05:08:02.10 spid19s     Error: 802, Severity: 17, State: 17.
2009-11-11 05:08:02.10 spid19s     There is insufficient memory available in the buffer pool.
2009-11-11 05:08:02.14 spid23s      Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2009-11-11 05:08:02.14 spid58      Error: 701, Severity: 17, State: 171.
2009-11-11 05:08:02.14 spid58      There is insufficient system memory in resource pool 'default' to run this query.
2009-11-11 05:08:02.19 Server      Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.19 Server      Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.19 Server      Error: 17053, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.19 spid53      Error: 701, Severity: 17, State: 89.
2009-11-11 05:08:02.19 spid53      There is insufficient system memory in resource pool 'default' to run this query.
2009-11-11 05:08:02.19 spid16s     Error: 802, Severity: 17, State: 17.
2009-11-11 05:08:02.19 spid16s     There is insufficient memory available in the buffer pool.
2009-11-11 05:08:02.20 spid19s     Error: 9646, Severity: 16, State: 3. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.21 spid19s     Error: 701, Severity: 17, State: 123.
2009-11-11 05:08:02.21 spid19s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.23 spid19s     Error: 9646, Severity: 16, State: 3. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.28 spid16s     Error: 9645, Severity: 16, State: 4. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.28 spid16s     Error: 701, Severity: 17, State: 178.
2009-11-11 05:08:02.28 spid16s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.29 spid16s     Error: 9645, Severity: 16, State: 4. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.29 spid16s     Error: 701, Severity: 17, State: 178.
2009-11-11 05:08:02.29 spid16s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.30 spid16s     Error: 9645, Severity: 16, State: 4. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.30 spid16s     Error: 701, Severity: 17, State: 178.
2009-11-11 05:08:02.30 spid16s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.30 spid16s     Error: 9645, Severity: 16, State: 4. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.30 spid16s     Error: 701, Severity: 17, State: 178.
2009-11-11 05:08:02.30 spid16s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.30 spid16s     Error: 9645, Severity: 16, State: 4. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-11-11 05:08:02.31 spid19s     Error: 701, Severity: 17, State: 123.
2009-11-11 05:08:02.31 spid19s     There is insufficient system memory in resource pool 'internal' to run this query.
2009-11-11 05:08:02.37 spid55

Server Memrory Configuration:
Physical Memory - 16GB
SQL max mem - 14GB

OS: WIN2008SP1 - Build 6.1.10.1
SQL 100.2520.0 (x64)

Please help me on figuring out the root cause for this error

thanks
Jyothi

Jyothi

Service Broker set up between two sql server 2005 instances on non trusted networks

Mon, 16 Nov 2009 17:53:39 Z

Hello,

I would like to know whether service broker can be set up to exchange messages between two sql server 2005 instances running on two non-trusted networks. We have ability to open up tcp ports on the both networks. Any info or link to it as to how to do it will be helpful.

Thanks,
tshah.

QUEUE ACTIVATION - CPU 100%

Thu, 12 Nov 2009 09:03:37 Z

Hi all,

When I set the Queue Activation Status = ON , the Procedure can be activation, but the Target Server CPU still used 100%. even if not exists message receive.

It looks like just Queue monitor process running. when I killed that process the CPU rate normal.

[single-core cpu 100%,dual-core cpu 50%]

Thanks

Service broker - What are the implications of leaving messages in a converation target queue ?

Mon, 09 Nov 2009 11:25:41 Z

Hi,
I developed an application that uses service broker for asynchronous long batch processing.
It has 2 queues :
1. Queue1 that gets the command to run with activation to run a stored procedure
2. Queue2 that receiving the message.
What are the implications of leaving messages in queue2 and use it as a "log" for all the activated commands ?
Is it recommended to empty the queue after the process (the SP) is done ?
What is the recommended way to do so ? Does "RECEIVE TOP(1) conversation_handle FROM Queue2" will do the work ?

Thanks,
Nir

Service Broker

Tue, 16 May 2006 06:01:27 Z

My service broker seems to be broken... The database was restored from another crashed server but i have tried the

ALTER AUTHORIZATION ON DATABASE::[SPYDERONTHEWEB] TO [SA];

The error i'm getting is

Service Broker needs to access the master key in the database 'SpyderOnTheWeb'. Error code 25. The master key has to exist and th service master key encryption is required.

Error: 28054, Severity 11, State: 1.

External Activator 2008 - I need a C# example of the message processor

Mon, 09 Nov 2009 21:07:31 Z

Hello, I was able to install the EA service for SQL 2008 and have created the Service Broker queue and notification services. Looks like the configuration works because the EA service log shows it "sees" the notification services. What I am confused about is how to create the executable that the EA service calls ultimately to process the message. I first created a simple C# console app referecing the ServicebrokerInterface.dll but the EA log was showing :

11/9/2009 1:58:28 PM INFO The External Activator service is running.
11/9/2009 1:58:28 PM WARNING An event notification has been dropped
11/9/2009 1:58:28 PM EXCEPTION ERROR = 32, No enabled application monitor is on behalf of queue D197378.CDCTrainingExchange.dbo.OutgoingQueue.

Hence, I pressumed that I needed to use the old EA code example that does contain a ApplicationMonitor class in it. However, I now consistently get:

11/9/2009 3:31:01 PM INFO Initializing configuration manager completed
11/9/2009 3:31:01 PM INFO The External Activator service is running.
11/9/2009 3:32:48 PM WARNING An event notification has been dropped
11/9/2009 3:35:43 PM WARNING An event notification has been dropped

Unfortunately, the code is not producing any logs so I must pressume it is not being called. Could someone please point me to a simple code example that would work with the new EA service for SQL 2008? I downloaded the latest EA code examples but could not find any for this piece.

Target sys.conversation_endpoints not purged of closed conversations

Thu, 07 Dec 2006 19:01:50 Z

I hope someone can help me with this as we plan on using Service Broker in a high volume production environment. The script that builds everything is available if it's needed to diagnose the problem I'm having.

I'm having an issue where sys.conversation_endpoints on the target side of a conversation is never getting purged of closed conversations even after the 30 minute delay. The view is filled with closed conversations and database size is growing every day. I'm aware I can end conversation with cleanup on these conversations, but I would prefer that Service Broker behaves as expected. I'm also aware of the problems with the fire and forget model, but my model is request/response/end between 2 databases on the same server instance. Here's the typical series of events:

Initiator sends request
Target receives request
Target processes request
Target sends response
Initiator receives response
Initiator processes response
Initiator ends conversation
Target receives EndDialog message
Target ends conversation

Occasionally during the target's processing of a request, an exception is caught and the Target ends the conversation with an error:

Initiator sends request
Target receives request
Target processes request and recognizes error
Target ends conversation with error
Initiator receives EndDialog message
Initiator ends conversation

Here's the trace where Database ID 23 is initiator and 24 is target, no error:

EventClass	DatabaseID	TextData	EventSubClass
Broker:Conversation Group	23		1 - Create
Broker:Conversation	23	STARTED_OUTBOUND	11 - BEGIN DIALOG
Broker:Conversation	23	CONVERSING	1 - SEND Message
Broker:Message Classify	23		1 - Local
Broker:Conversation Group	24		1 - Create
Broker:Conversation	24	STARTED_INBOUND	12 - Dialog Created
Broker:Conversation	24	CONVERSING	6 - Received Sequenced Message
Broker:Activation	24		1 - Start
Broker:Conversation	24	CONVERSING	1 - SEND Message
Broker:Message Classify	24		1 - Local
Broker:Conversation	23	CONVERSING	6 - Received Sequenced Message
Broker:Activation	23		1 - Start
Broker:Conversation	23	DISCONNECTED_OUTBOUND	2 - END CONVERSATION
Broker:Conversation Group	23		2 - Drop
Broker:Message Classify	23		1 - Local
Broker:Conversation	24	DISCONNECTED_INBOUND	7 - Received END CONVERSATION
Broker:Conversation	23	CLOSED	10 - Received END CONVERSATION Ack
Broker:Conversation	24	CLOSED	2 - END CONVERSATION
Broker:Conversation Group	24		2 - Drop
Broker:Activation	23		2 - Ended
Broker:Activation	24		2 - Ended

Here are the typical records in the target sys.conversation_endpoints. These records never disappear:

	Normal	With Error
conversation_handle	3FE27EE5-1E86-DB11-B009-000BDB714730	53E17EE5-1E86-DB11-B009-000BDB714730
conversation_id	0A432392-55F5-461B-87D5-0058795BC3AE	BCCDFA85-86A3-43B8-9648-24FFE5C0ED3F
is_initiator	0	0
service_contract_id	0	0
conversation_group_id	00000000-0000-0000-0000-000000000000	00000000-0000-0000-0000-000000000000
service_id	0	0
lifetime	2074-12-25 21:29:28.640	2074-12-25 21:29:28.000
state	CD	CD
state_desc	CLOSED	CLOSED
far_service	http://my.domain.com/schemas/test/Initiator/2006-12-07	http://my.domain.com/schemas/test/Initiator/2006-12-07
far_broker_instance	227D0898-0399-40E0-954B-C8B685EE415A	227D0898-0399-40E0-954B-C8B685EE415A
principal_id	5	5
far_principal_id	6	6
outbound_session_key_identifier	DEBEB4DB-D186-410B-9555-A34F8F5C9FE2	B82BB074-5AE5-4164-9D0B-53E364B0B52B
inbound_session_key_identifier	1DBAE307-5DFF-4050-9D94-71003D8BD058	57B5C7D8-9E8B-4614-9325-5AA30AED3670
security_timestamp	2006-12-07 18:45:52.763	1900-01-01 00:00:00.000
dialog_timer	1900-01-01 00:00:00.000	1900-01-01 00:00:00.000
send_sequence	1	1
last_send_tran_id	0x550800000000	0x700700000000
end_dialog_sequence	-1	1
receive_sequence	2	1
receive_sequence_frag	0	0
system_sequence	0	0
first_out_of_order_sequence	-1	-1
last_out_of_order_sequence	0	0
last_out_of_order_frag	0	0
is_system	0	0

Service Broker Eventdata() store xml

Fri, 06 Nov 2009 04:07:31 Z

I'm using service broker to send messages across multiple databases on many servers to one "central" database that will be recording any changes made(triggers/functs/procs/views/ and tables). Tables is giving me a hard time because SMO is not compatible with CLR. The way my project is working is that everytime a change is made, a ddl trigger will fire and service broker will send everything to a row in table in the central database using EVENTDATA(). From this table, it is being scripted out using a CLR and then written to file.

For tables, I am not using SMO. I have a UDF that will allow me to get the full script of the table after the ddl is fired. Now I'm having issues with sending this message from the "child" server to the "master" server. My UDF returns the script of the table as an xml with the 'drops' and 'creates'. I was thinking to store this xml into a variable within EVENTDATA() before sending it to the master server using service broker. This way everything is still available(login time, name, etc)within the message after it has reached the master database. How can this be done?

Also, are function calls allowed in a stored procedure?

Thanks in advance for any assistance or comments.

Query Notifications

Wed, 04 Nov 2009 18:47:09 Z

Hi,

I am new to Query notifications. Studying the literature, I can not figure out the connection between
ContactChangeMessages and ContactChangeNotifications on the one hand in
ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/wd_adonet/html/a5333e19-8e55-4aa9-82dc-ca8745e516ed.htm

AND

Example in
ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/wd_adonet/html/88bf9b56-5769-4595-98c4-80285d812a5e.htm

Nowhere in the example are the service broker queue and / or service broker service from the top of this page mentioned.

Where is the connection?

Thanks in advance!

SQL2005 Service Broker on 64bit active/passive cluster & .Net Framework 3.5sp1

Tue, 27 Oct 2009 13:19:41 Z

Hi
We are currently running Service Broker on one of our SQL2005 64bit Enterprise servers. We recently upgraded some 3rd party software which has s SQL backend database and one of the requirements was for .Net Framework 3.5sp1 to be installed on both nodes of the SQL cluster. Being cautious about what impact this would have, we installed it on our passive node and failed over. However over a 12 hour period we then hit some problems with messages queues on our Service Broker application. We assumed these were related to the .Net Framework 3.5sp1 install, so failed back to our node without this and all has been working well for 72hours.

So I'm trying to establish if there are any known issues installing .Net Framework 3.5sp1 on a SQL2005 bit cluster. Or if anyone has experience similair problems. It could just be a coincidence we had issues, or there is something else on the passive node that was causing the problem.

Many Thanks in advance.
Darren

WAITFOR in External activator does not allow to shrink logfile

Mon, 12 Jan 2009 22:12:35 Z

I am using External activator to notify that a message is available in the Queue to process. My External app picks up the message and process it successfully. Everything works fine.

The issue is that due to the WAITFOR that is indefinate (without timeout) in the activator, I am not able to shrink log file.
I backed up my database and ran Shrink database using SSMS. The log file did not shirnk. So I tried

DBCC SHRINKFILE(db_Log, 1)
BACKUP LOG xw1_1_Core WITH TRUNCATE_ONLY
DBCC SHRINKFILE(db_Log, 1)

But could not shrink the file.
I think the reason is because of the log_reuse_wait, log_reuse_wait_desc in sys.databases is set to 4 (ACTIVE_TRANSACTION), and that's due to the active transaction by WAITFOR in Activator.

Has anyone come across similar problem and if anyone has a solution or suggest a solution to shrink the file. I would prefer that I don't have to stop the Activator service (i mean WAITFOR).

Not sure whether this question belong to Service broker or SQL server engine.

thanks in advance.

How to Pass Message From Service Broker to Custom Windows Service Application

Tue, 10 Feb 2009 11:11:54 Z

Hi,

Is it possible to send message to a custom developed windows service using service broker message queues?

Scenario is that I want to notifiy my windows service (devleoped in c#) when ever a new record is inserted into the database.

Is the Service Broker's Message Queue available outside the Database say in a windows service?

ARaheem Awan

Conversation Group Locking

Thu, 29 Oct 2009 17:18:33 Z

Hello,

I have a conversation group which contains a conversation identified by conversation handle @ch,

In one process, I do a

RECEIVE ...

, thereby locking the conversation group until the transaction is committed.

What happens if, in a separate process, I try to do a

SEND ON CONVERSATION @ch

while the conversation group is still locked?

Does it raise an error, or does it just assign a new conversation group, or does it do something else?

Thanks,
Mark

Once for all right permissions for SQLDependency PLEASE?

Fri, 30 Oct 2009 15:53:41 Z

I took this description out of my search engine because it is exactly what is happening to us, so why bother writing it again.

The original post is unanswered here (http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic30682.aspx)

but I hope that it will be solved here.

The blog entry posted by Sushil Chordia at
http://blogs.msdn.com/dataaccess/archive/2005/09/27/474447.aspx describes
the minimum requirements a Sql user should have for SqlDependency to work. I
have tried to apply them, but I still have some problems.

I am trying to create an Application Role in Sql Server 2005 RTM that
contains all the necessary rights for SqlDependency to work. I'm doing this
based on the information specified in the blog mentioned earlier.

Here's the script that creates a test-databases and the application-role:
CREATE DATABASE [TestDb] ON PRIMARY
( NAME = N'TestDb', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TestDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestDb_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TestDb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB
, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AI
GO

USE TestDb
GO

CREATE TABLE [dbo].[tblPRODUCTS](ID INT NOT NULL, strNAME NVARCHAR(50) NOT
NULL)
GO

CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDb],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [test] FOR LOGIN [test]
GO

EXEC sp_addrole 'sql_dependency_subscriber'
GO

CREATE ROLE [sqldependency_user]
GO

GRANT CREATE PROCEDURE to [sqldependency_user]
GRANT CREATE QUEUE to [sqldependency_user]
GRANT CREATE SERVICE to [sqldependency_user]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification ] to [sqldependency_user]
GRANT VIEW DEFINITION TO [sqldependency_user]
GRANT SELECT to [sqldependency_user]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sqldependency_user]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sqldependency_user]
GRANT REFERENCES on
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification ] to [sqldependency_user]

EXEC sp_addrolemember 'sql_dependency_subscriber', 'test'
EXEC sp_addrolemember 'sqldependency_user', 'test'
GO

I have also written the following C# program that should work at this point
if I interpret that blog-entry correctly.
class Program
{
    private static string mConnectionString = "Data
Source=(local);Database=TestDb;Persist Security Info=false;Integrated
Security=false;User Id=test;Password=test";

    static void Main(string[] args)
    {
      using (SqlConnection oConnection = new SqlConnection(mConnectionString))
      {
        oConnection.Open();

        SqlDependency.Start(mConnectionString);

        ' there's some more code here...
      }
    }
}

However, if I now run this program, I get the following exception on
SqlDependency.Start:
Unhandled Exception: System.Data.SqlClient.SqlException: The specified
schema name "dbo" either does
not exist or you do not have permission to use it.

I partially resolved this problem by doing the following:
GRANT CONTROL ON SCHEMA::[dbo] TO [adam_service_user]

However, when I run the application again, it now fails with the following
error on SqlDependency.Start:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot find the
user 'owner', because it do
es not exist or you do not have permission.
Cannot find the queue
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206', because it
does not exist or you do not have permission.
Cannot find the service
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206', because
it does not exist or you do not have permission.
Invalid object name
'SqlQueryNotificationService-653e2c6f-51ff-488e-bca6-71be01a02206'.

Apparently, there's still some additional permission required to use
SqlDependency that is not listed in Sushil's blog. I can bypass this problem
by making my role a member of the dbo-role, but I would like to know the
minimum role required to make this thing work.

The activated proc [].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue [dbname].

Fri, 30 Oct 2009 18:19:54 Z

Hi
Ive been working with one of our developers to setup QN by following the instructions on [http://www.code-magazine.com/articleprint.aspx?quickid=0605061&printmode=true]
He couldnt get the subscriptions to work and prompted me to have a look through the setup
Ive noticed that my errorlog is getting filled with the following msgs

The activated proc [XXX\XXXX].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue XXXXX.XXXX\XXXXX.SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269 output the following: 'The activated proc [XXXX\XXXXX].[SqlQueryNotificationStoredProcedure-c885a8fb-dc38-4c3d-806f-dd26467d9269] running on queue XXXX\XXXXX.SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269 output the following: 'Could not obtain information about Windows NT group/user 'XXXX\XXXXX', error code 0x5.

If I run

SELECT q.name, m.state, m.last_empty_rowset_time, m.tasks_waiting
FROM   sys.dm_broker_queue_monitors m
JOIN   sys.service_queues q
ON            m.queue_id = q.object_id

I get the following

name    state    last_empty_rowset_time    tasks_waiting
SqlQueryNotificationService-c885a8fb-dc38-4c3d-806f-dd26467d9269    INACTIVE    2009-10-30 16:58:23.510    0
SqlQueryNotificationService-5ab3904a-35db-4809-b332-137d3ecc0313    INACTIVE    2009-10-30 17:00:21.293    0
SqlQueryNotificationService-3060d8d1-a0fd-4783-a563-f016d144c7a1    INACTIVE    2009-10-30 17:00:48.937    0
SqlQueryNotificationService-d4be153d-cc34-4bd0-9ad0-26b333d0c21a    INACTIVE    2009-10-30 17:05:09.647    0
SqlQueryNotificationService-1e369bd6-2497-446e-b0c2-8049facea96f    INACTIVE    2009-10-30 17:07:58.697    0

Tried most suggestions posted on this forum but hasnt worked, even droped the database and recreated but these msgs are still filling up the errorlog.

Apprecitate if someone can assist

We simply want to remove the msgs that are coming out.

thank you

SQL Query Notifications and GetDate()

Fri, 30 Oct 2009 13:05:53 Z

I am currently working on a query that is registered for Query Notifications. In accordance w/ the rules of Notification Services, I can only use Deterministic functions in my queries set up for subscription. However, GetDate() (and almost any other means that I can think of) are non-deterministic. Whenever I pull my data, I would like to be able to limit the result set to only relevant records, which is determined by the current day.

Does anyone know of a work around that I could use that would allow me to use the current date to filter my results but not invalidate the query for query notifications?

Example Code:

SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
FROM dbo.bFiscalCalendar
WHERE fcDate >= GetDate() -- This line invalidates the query for notification...

Other thoughts:

We have an application controls table in our database that we use to store application level settings. I had thought to write a small script that keeps a record up to date w/ teh current smalldatetime. However, my join to this table is failing for notificaiton as well and I am not sure why. I surmise that it has something to do w/ me specifitying a text type (the column name), which is frustrating.

Example Code 2:

SELECT fcDate as RecordDate, fcYear as FiscalYear, fcPeriod as FiscalPeriod, fcFiscalWeek as FiscalWeek, fcIsPeriodEndDate as IsPeriodEnd, fcPeriodWeek as WeekOfPeriod
FROM dbo.bFiscalCalendar    
INNER JOIN dbo.xApplicationControls ON  fcDate >= acValue AND acName = N'Cache_CurrentDate'

Does anyone have any suggestions?

EDIT: Here is a link on MSDN that gives the rules for Notification Services

Database Engineer

SQL Service Broker Problem with date

Thu, 26 Mar 2009 14:40:01 Z

Hi All

I use the SQL Service Broker to notify me when a row is inserted in a SQL table.

The SqlDependency command text as follwing

SELECT Email from dbo.CEM where Date_CEM = '1/20/2008 12:00:00 AM'

As you see there is a column its datatype is datetime (Date_CEM), when I am using a select statement same as shown I am having a problem, which is : SqlDependency onchange event is invoked continuously and cannot be stopped wherever the value of the Email column is changed or not, but when I tried to use another select statement that not contains a datetime column in the condition the event is invoked correctly (only when the value is changed of the Email column)

Can anybody help me

Message not being sent while auto activating a procedure from a queue

Thu, 29 Oct 2009 08:11:34 Z

So...
I have a queue that activates a procedure when in it receives a message.
This procedures (let's call it procedure A) calls another procedure (let's call it B)
procedure B function is to log the activity of procedure A in another queue

the problem is... that process B isn't logging anything in the queue...
if I call procedure B by itself it does work like it should...
and if I take the code from procedure B and put in A... it also work (but I shouldn't do it this way)

So what's going on?
Is there a problem when an autoactivating procedure calls another?

BTW this is on sql 2005

Thanks,
Renato Fontes

SqlDependency not registering subscriptions only with MY database

Wed, 28 Oct 2009 16:05:21 Z

Hi,

I have been trying to use the SqlDependency class to register a subscription with my SQL database from .Net code. I have ensure that my login has all of the required permissions and that my DB has the Service Broker enabled. When my code runs, a new Queue and Service are created, but no subscription is registered (i.e. nothing new shows up in sys.dm_qn_subscriptions).

Out of desperation, I tried set up AdventureWorks and created the project detailed in the MSDN article 'Using SQLDependency in a Windows Application' (http://msdn.microsoft.com/en-us/library/a52dhwx7%28VS.80%29.aspx) and it works! However, when I modify the connection string to connect to my database, and change the SELECT statement to pull something from one of my tables, the data is retrieved but no subscription is registered!

This is driving me crazy. From my debugging, I have concluded that the problem must be something with my database, but i can't figure it out for the life of me.

My SQL Server is local and I am sa. I am using stock .Net front-end code : SqlDependency with the SqlCommand class.

Does anybody have any ideas?

Thanks in advance,

Chris

dependencies for the bulk inserting process

Mon, 26 Oct 2009 09:28:55 Z

HI All

There are lot of dependencies for the bulk inserting process in oracle like

Tablespace
Redo Log Size
Server size
PCT FREE
PCT USED
Log Switches
Undo Retention
Undo Tablespace Size.

Undo Size = Undo Retention * DB Block Size * Undo Block Per Sec

undo Block Per Sec
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;

INITIAL
INITRANS for the table from 1 to 5.
PCTINCREASE - 0
FREELISTS parameter. You want to have it set to AT LEAST the number of processes that you believe will be CONCURRENTLY inserting into your table.
multiple database writers (db_writers init.ora parameter),

PCT_USED is most important if records will ever be deleted from this table. If you don't plan to allow deletes, then set PCT_USED to a high value (like 80 or 90) but the total of PCT_FREE + PCT_USED must be less than or equal to 100.

Locally Managed / Dictionery Managed.

can any one suggest me for sql server...?

please

Regards
Yogananda

Is it possible to send a message from inside a function?

Wed, 28 Oct 2009 14:42:05 Z

I was trying to make a function to log some operations in my database...
The log was going to work by sending message trough the service broker and storing them in a queue.
The problem is that when I tried to make this function I got this error:

"Invalid use of side-effecting or time-dependent operator in 'BEGIN DIALOG' within a function."

So... is there a way to send a message via service broker using functions?

Thanks for your help,
Renato Fontes

SqlDependency works perfect but sometimes...

Wed, 28 Oct 2009 13:10:12 Z

I have a problem with the SqlDependency object.

It appears to be working for days and suddenly it stops working for some reason. When I restart the NT service which is waiting for the data it works again for days...

What is the possible reason?

1. Running SQL Server 2005 SP2 (Can't install SP3 due to other db's).

Service Broker and AS400 MQ Series

Tue, 13 Feb 2007 16:37:16 Z

Hi,

Does anyone using SQL Server broker to send message to a queue outside SQL Server. I mean to non-SQL environment like AS400 MQ Series.

Thanks

Shafiq

No enabled application monitor is on behalf of queue...

Tue, 27 Oct 2009 14:17:41 Z

Hello,

I am trying to set up external activation, and the external application doesn't ever seem to be launched. I get the following error, which shows up in the EATrace.log:

EXCEPTION ERROR = 32, No enabled application monitor is on behalf of queue [my target queue name].

Any ideas where I should be looking to find the source of the problem? I'm not sure what it means by 'no enabled application monitor...'

Thanks in advance.

Service Broker Application Design Question

Sun, 14 Jun 2009 23:10:51 Z

Hi. We're thinking of implementing Service Broker to expedite incoming traffic and user requested processing. The short of it is we have a process that needs to kick off when three associated files hit the database. Every time customer 1's files come in they are reviewed by the user and he/she requests the process to execute. There are n number of customers each having their own set of 3 files. The process should not be executed before all three files are present. We have to cover the fact that the files for a given customer can come in at any time and in any order. It maybe the case that a user has sent a message to execute the final process for a given customer before all the files for that customer are in. In which case we'd like to wait until all files are in and then process the user's request.

We're stumped about how to set up queues and conversations to manage these scenarios.

Anyone have any ideas about how to design server broker for this?

Thanks in Advance

Sql Dependency working procedure

Thu, 25 Jun 2009 11:40:15 Z

I am using sql dependency in my windows service code..So when ever there is a change in the table where my sql dependency is watch..onchange event is firing in windows service..

I am not able to get any where how actually it is working internally(I know just how it is working as a concept)

I like to know how actually it is working...?

My current issue is Event is not fired when db server is restarted where my service will be in different system and it will be always in started mode.

If any one has any idea on above issues please share here....

Thank you

rajesh

Is Sql 2005 Service Broker be enabled by default?

Mon, 29 Jun 2009 07:18:50 Z

We just installed OpsMgr 2007 on our sql 2005 database server and it complains that: "The SQL Server Service Broker or Database Mirroring transport is disabled or not configured Priority" We have not enabled Service Broker (and we don't use mirroring on this server) so I don't know what to make of that message. I read somewhere that Service Broker is enabled by default. Is that true? (In other words, does the OpsMgr message indicate a "real" problem or is it just saying it is desirable to enable Service Broker?) TIA, barkingdog (Despite the Title confusion, I am asking about a sql 2005, not sql 2008, server)

What is Service Broker?

Wed, 21 Oct 2009 01:46:09 Z

Can someone explain to me what Service Broker is please? And what is the purpose of it? How is it different to send mail? Ive read through alot of material, and I understand that its about sending messages, but I dont understand the point of writing code to send and receive messages on the same system. What is the real life use for this?

*sorry if this sounds idiotic.

Thanks!

You are so wise...like a miniature budha covered in fur. -Anchorman

Msg 9772 -- Service Broker in database "" cannot be enabled

Tue, 29 Nov 2005 15:18:32 Z

I backed up a database "Broker", and restored it on the same instance as "BrokerQA" (Broker db still exists, I need them both running on the same instance).

tried sending a few messages, sys.transmissions_queue says: The broker is disabled in the sender's database.

then tried to enable service broker for BrokerQA:

alter database BrokerQA set enable_broker

Msg 9772, Level 16, State 1, Line 2

The Service Broker in database "BrokerQA" cannot be enabled because there is already an enabled Service Broker with the same ID.

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

How do I reset the service broker ID on the newly restored database?
Thanks!

Troubleshooting SB: help me enhance a procedure

Thu, 15 Oct 2009 14:47:15 Z

Hi all,
I am not sure if this is the best place to post this but I couldn't think of anywhere else so here it is:
I'm new to Service Broker and I struggled every time something went wrong. There are quite a few things to check so I put all the scripts together to build a procedure which could help me diagnose the service broker situation at a given moment. It would be great if I could have your input on this and thought about how to improve it & make it more useful for everybody. It definitely needs improvement so don't hesitate to add your comments & point out nonsense:

CREATE  PROCEDURE dbo.SBStatus (
	@target_db1 varchar(100) = 'db1',
	@target_db2 varchar(100) = 'db2'
)
AS
BEGIN
	-- Proc to diagnose Service Broker
	-----------------------------------------------------
	SET NOCOUNT ON

	DECLARE @sql NVARCHAR(1000), @curdb VARCHAR(50), @target_db VARCHAR(50), @msg NVARCHAR(200)
	DECLARE @t_res TABLE (name VARCHAR(20), rows INT, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
	DECLARE @t_svcinfo TABLE (remote_service_name NVARCHAR(256), [broker_instance] NVARCHAR(128))
	
	DECLARE @sb_enabled BIT, @sb_src_guid UNIQUEIDENTIFIER, @sb_target_guid UNIQUEIDENTIFIER, 
			@queue VARCHAR(100), @queue_enabled BIT, @queue_active BIT 
	DECLARE	@proc VARCHAR(100), @servicename NVARCHAR(256), @count INT, @exists BIT
	DECLARE @lnk_server VARCHAR(100)
	DECLARE @links TABLE (target_db VARCHAR(20))
	DECLARE @send_q_threshold INT, @receive_q_threshold INT

	SET @curdb = DB_NAME()
	SET @send_q_threshold = 5000
	SET @receive_q_threshold = 10000
--	
	INSERT INTO @links
	SELECT @target_db1
	UNION
	SELECT @target_db2
--
	-----------------------------------------------------
	-- Test Source DB
	-----------------------------------------------------
	SELECT ' >>>>>> TESTING: ' + @curdb

	-- Check if SB is enabled	
	-----------------------------------------------------
	SELECT @sb_enabled = is_broker_enabled, @sb_src_guid = service_broker_guid 
	FROM sys.databases 
	WHERE name = @curdb
	
	IF @sb_enabled = 0
	BEGIN
		SET @msg = N'Service Broker is NOT enabled! Run ALTER DATABASE ' + @curdb + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE'
		RAISERROR(@msg, 11, 0) WITH NOWAIT
	END
	ELSE
	BEGIN
		SET @msg = N'Service Broker is enabled: OK'
		RAISERROR(@msg, 0, 0) WITH NOWAIT	
	END	

	-- Check if the queue(s) are enabled and active
	-----------------------------------------------------
	IF EXISTS (
		SELECT 1	
		FROM sys.service_queues 
		WHERE is_ms_shipped = 0
		AND (is_activation_enabled = 0 OR is_enqueue_enabled = 0)
	)
	BEGIN
		SET @msg = N'Some queues are disabled or inactive:'
		RAISERROR(@msg, 11, 0) WITH NOWAIT		
	
		SELECT name AS 'Queue', is_activation_enabled AS 'Active', is_enqueue_enabled AS 'Enabled'
		FROM sys.service_queues 
		WHERE is_ms_shipped = 0
	END
	ELSE
	BEGIN
		SET @msg = N'Queues are enabled and active: OK'
		RAISERROR(@msg, 0, 0) WITH NOWAIT	
	END	

	-- Check if messages are targeting undefined remote services
	-----------------------------------------------------	
	IF EXISTS (		
		SELECT tq.to_service_name
		FROM sys.transmission_queue AS tq
		WHERE NOT EXISTS(
			SELECT remote_service_name
			FROM sys.routes AS routes
			WHERE tq.to_service_name = routes.remote_service_name
		)
	)
	BEGIN
		SET @msg = N'Some messages might not be delivered because the remote service name is unknown:'
		RAISERROR(@msg, 11, 0) WITH NOWAIT
		
		SELECT tq.to_service_name AS 'Services with no Exact Match'
		FROM sys.transmission_queue AS tq
		WHERE NOT EXISTS(
			SELECT remote_service_name
			FROM sys.routes AS routes
			WHERE tq.to_service_name = routes.remote_service_name
		)
	END	

	-- Check if we have blocked messages
	-----------------------------------------------------	
	IF EXISTS (		
		SELECT 1
		FROM sys.transmission_queue WITH (NOLOCK)
		WHERE transmission_status IS NOT NULL
		UNION
		SELECT 1
		FROM sys.conversation_endpoints WITH (NOLOCK)
		WHERE State IN ('DI', 'DO', 'ER')
	)
	BEGIN
		SET @msg = N'Some messages couldn''t be sent. Some examples:'
		RAISERROR(@msg, 11, 0) WITH NOWAIT
		
		SELECT TOP 5 conversation_handle, from_service_name 'Source Service', to_service_name 'Target Service', message_type_name, transmission_status
		FROM sys.transmission_queue WITH (NOLOCK)
		WHERE transmission_status IS NOT NULL
		
		SELECT TOP 5 conversation_handle, state_desc
		FROM sys.conversation_endpoints WITH (NOLOCK)
		WHERE State IN ('DI', 'DO', 'ER')		
	END	
		
	-- Check the size of the transmission queue
	-----------------------------------------------------
	INSERT INTO @t_res (name, rows, reserved, data, index_size, unused)
	EXEC sp_spaceused 'sys.sysxmitqueue'

	SELECT @count = rows FROM @t_res

	IF @count < @send_q_threshold
	BEGIN
		SET @msg = N'Transmission queue length: ' + CONVERT(NVARCHAR(15),@count)
		RAISERROR(@msg, 0, 0) WITH NOWAIT	
	END
	ELSE
	BEGIN
		SET @msg = N'Transmission queue length: ' + CONVERT(NVARCHAR(15),@count)
		RAISERROR(@msg, 11, 0) WITH NOWAIT	
	END
	

	-- Show queue(s) detail if queues have a reasonable size (to avoid timouts during the COUNT())
	-----------------------------------------------------
	IF @count < 300000
	BEGIN	
		PRINT 'Detailed queue info:' + CHAR(13)
		SELECT 
			--t1.name AS [Service_Name],  
			t3.name + '.' + t2.name AS [Queue],  
			CASE 
				WHEN t4.state IS NULL THEN 'Not available' 
				ELSE t4.state 
			END AS [Queue_State],  
			CASE 
				WHEN t4.last_activated_time IS NULL THEN '--' 
				ELSE CONVERT(varchar, t4.last_activated_time) 
			END AS last_activated_time ,   
			( 
				SELECT COUNT(1) 
				FROM sys.transmission_queue t6 
				WHERE (t6.from_service_name = t1.name) 
			) AS [Tran_Message_Count] 
		FROM sys.services t1    
		INNER JOIN sys.service_queues t2 ON ( t1.service_queue_id = t2.object_id )   
		INNER JOIN sys.schemas t3 ON ( t2.schema_id = t3.schema_id )  
		LEFT OUTER JOIN sys.dm_broker_queue_monitors t4 ON ( t2.object_id = t4.queue_id  AND t4.database_id = DB_ID(@curdb) )  
		INNER JOIN sys.databases t5 ON ( t5.database_id = DB_ID(@curdb))
		WHERE t2.is_ms_shipped = 0
	END

	-- Show active conversations
	-----------------------------------------------------	
	IF EXISTS (
		SELECT 1 FROM sys.conversation_endpoints WITH (NOLOCK)
	)
	BEGIN
		SET @msg = N'Current conversations: There are active conversations: '
		RAISERROR(@msg, 0, 0) WITH NOWAIT		
		
		SELECT conversation_handle, state_desc, far_service, far_broker_instance 
		FROM sys.conversation_endpoints WITH (NOLOCK)
	END
	ELSE
	BEGIN
		SET @msg = N'Current conversations: There are no active conversations'
		RAISERROR(@msg, 0, 0) WITH NOWAIT	
	END	

	-- Get remote SB id's for later connectivity checks
	-----------------------------------------------------
	INSERT INTO @t_svcinfo (remote_service_name, [broker_instance])
	SELECT remote_service_name, [broker_instance]
	FROM sys.routes
	
	----------------------------------------------------- -----------------------------------------------------
	-----------------------------------------------------		
	-- Test Target server(s)
	-----------------------------------------------------	
	DECLARE cur_targets CURSOR LOCAL FAST_FORWARD 
	FOR
		SELECT target_db FROM @links
	
	OPEN cur_targets
	FETCH NEXT FROM cur_targets INTO @target_db
	
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SELECT ' >>>>>> TESTING: ' + @target_db

		-- target DB must be declared as default db in the linked server config
		SELECT @lnk_server = name FROM sys.servers WHERE catalog = @target_db

		-- Enable RPC on target server	
		-----------------------------------------------------		

		SET @sql = N'EXEC master.dbo.sp_serveroption @server=N'''+@lnk_server+''', @optname=N''rpc out'', @optvalue=N''true'''
		EXECUTE sp_executesql @sql		 
		 
		-- Check if SB is enabled	
		-----------------------------------------------------
		SET @sql = N'SELECT @sb_enabled = is_broker_enabled, @sb_target_guid = service_broker_guid 
					FROM ' + @lnk_server + '.'+ @target_db +'.sys.databases 
					WHERE name = ''' + @target_db + ''''
		
		EXECUTE sp_executesql @sql, 
			N'@sb_enabled BIT OUTPUT, @sb_target_guid UNIQUEIDENTIFIER OUTPUT',
			@sb_enabled = @sb_enabled OUTPUT, @sb_target_guid = @sb_target_guid OUTPUT
			
		IF @sb_enabled = 0
		BEGIN
			SET @msg = N'Service Broker is NOT enabled! Run ALTER DATABASE ' + @target_db + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE'
			RAISERROR(@msg, 11, 0) WITH NOWAIT
		END
		ELSE
		BEGIN
			SET @msg = N'Service Broker is enabled: OK'
			RAISERROR(@msg, 0, 0) WITH NOWAIT	
		END	
		
		-- Check if the queue(s) are enabled and active
		-----------------------------------------------------
		SET @sql = N'SELECT @exists = 1	
					FROM ' + @lnk_server + '.'+ @target_db +'.sys.service_queues 
					WHERE is_ms_shipped = 0
					AND (is_activation_enabled = 0 OR is_enqueue_enabled = 0)'

		EXECUTE sp_executesql @sql, 
			N'@exists BIT OUTPUT',
			@exists = @exists OUTPUT	
			
		IF @exists IS NOT NULL 
		BEGIN
			SET @msg = N'Some queues are disabled or inactive:'
			RAISERROR(@msg, 11, 0) WITH NOWAIT		
		
			SELECT name AS 'Queue', is_activation_enabled AS 'Active', is_enqueue_enabled AS 'Enabled'
			FROM sys.service_queues 
			WHERE is_ms_shipped = 0
		END
		ELSE
		BEGIN
			SET @msg = N'Queues are enabled and active: OK'
			RAISERROR(@msg, 0, 0) WITH NOWAIT	
		END	
					
		-- Check the size of the receiving queue(s)
		-----------------------------------------------------
				
		SET @sql = N'SELECT @servicename = s.name, @queue = parent.name, @count = [rows] 
					FROM ' + @lnk_server + '.'+ @target_db +'.sys.partitions p
					INNER JOIN ' + @lnk_server + '.'+ @target_db +'.sys.objects child ON p.object_id = child.object_id
					INNER JOIN ' + @lnk_server + '.'+ @target_db +'.sys.objects parent ON parent.object_id = child.parent_object_id 
					INNER JOIN ' + @lnk_server + '.'+ @target_db +'.sys.services s ON parent.object_id = s.service_queue_id 
					WHERE index_id in (0, 1)
					AND parent.is_ms_shipped = 0'


		EXECUTE sp_executesql @sql, 
			N'@queue VARCHAR(100) OUTPUT, @count INT OUTPUT, @servicename NVARCHAR(256) OUTPUT',
			@queue = @queue OUTPUT, @count = @count OUTPUT, @servicename = @servicename OUTPUT
/*			
		SET @SQL = N'EXEC ' + @lnk_server + '.'+ @target_db +'.dbo.sp_spaceused ''dbo.' + @queue + ''''				

		PRINT 'Current size of the '+ @queue + ' queue:'
		EXECUTE sp_executesql @sql
*/
		IF @count < @receive_q_threshold
		BEGIN
			SET @msg = N'' + @queue + ' (receiving queue) length: ' + CONVERT(NVARCHAR(15),@count)
			RAISERROR(@msg, 0, 0) WITH NOWAIT	
		END
		ELSE
		BEGIN
			SET @msg = N'' + @queue + ' (receiving queue) length: ' + CONVERT(NVARCHAR(15),@count)
			RAISERROR(@msg, 11, 0) WITH NOWAIT	
		END

		
		-- Check SB connectivity with this DB (doesn't work if more than 1 service links the source & target DB's)
		-----------------------------------------------------
		IF (
			SELECT CASE WHEN [broker_instance] = @sb_target_guid THEN 1 ELSE 0 END
			FROM @t_svcinfo
			WHERE remote_service_name =  @servicename
		) = 1
		BEGIN
			SET @msg = N'SB GUID matches between source and target DB'
			RAISERROR(@msg, 0, 0) WITH NOWAIT				
		END
		ELSE
		BEGIN
			SET @msg = N'SB GUID mismatch between source and target DB'
			RAISERROR(@msg, 11, 0) WITH NOWAIT				
		END
			
		FETCH NEXT FROM cur_targets INTO @target_db	
	END
			
END

Thank you!

How to delete rows from Queues

Fri, 16 Oct 2009 14:21:42 Z

Hello There,
I am having problem to delete rows/Messages from Queues in sql server 2008.

The problem is that , few weeks ago some installed sql monitoring software in UAT DATABASE SERVER. then after he probley uninstalle it.but that monitoring software has created some Queue,services in MSDB database. We deleted all table's and storeprocedure's related to that Activity Monitor Software.But now
this perticular Queue has 5,709,653 rows/messages in it. When I am trying to delete this Queue it gives message that there is "SERVICE" exist related to it.

When I am trying to delete this perticular SERVICE ,it taking log time and if i see result during this period it deleting rows but meanwhile my Transaction Log also getting fill up, and when Txn Log reach at maxium it kill my procosess and giving me message to increate transaction log size (During this period it has already used my 8GB Txns Log.)

so i want to get rid of this Queue,any how?

Here is my queue defination :
USE [msdb]
GO
/****** Object: ServiceQueue [LakeSideWaitsLogger].[_LakeSide_DbTools_Waits_LogOutQueue] Script Date: 10/16/2009 15:09:48 ******/
ALTER QUEUE [LakeSideWaitsLogger].[_LakeSide_DbTools_Waits_LogOutQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[Lakeside_Dummy] , MAX_QUEUE_READERS = 1 , EXECUTE AS N'dbo' )

And i tried all this statement , but still now succeed.

drop queue [msdb].[LakeSideWaitsLogger].[_LakeSide_DbTools_Waits_LogOutQueue];

Msg 8442, Level 16, State 1, Line 1

There is no Service Broker active in the database. Change to a database context that contains a Service Broker.

--------------------------------------------------------------------------------------------------------------------





declare @conversationHandle uniqueidentifier

select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints where service_id = 2

while @@rowcount = 1

begin

     end conversation @conversationHandle with cleanup

     select top 1 @conversationHandle = conversation_handle from sys.conversation_endpoints where service_id = 2

end

Thanks for any help ...

SEND MSMQ MESSAGE FROM SQL SERVER 2005 STORED PROCEDURES

Tue, 13 Oct 2009 10:02:45 Z

Hi,

How to send MSMQ message from stored procedure in sql server 2005 /2008?

Application Design Question

Wed, 14 Oct 2009 19:49:36 Z

We are designing weather applications that allow users to specify a city for weather notifications, such as temperature below 20 degree. Users will get alert message when the weather condition matches the condition. SQL notification service should be a good fit to use; however with the consideration of notification services is no longer in SQL server 2008, do we need to find an alternate way? Is service broker a good choice? Is there any limitation? Any input are highly appreciated.

Thanks,

Cici