endpoint already registered to another service

I am unable to create endpoint in the Windows 2003 server using SQL 2005. I also tried reserving the namespace before CREATE but i still end up with this error

Msg 7806, Level 16, State 1, Line 1
The URL specified by endpoint 'zzzzzz' is already registered to receive requests or is reserved for use by another service.

Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint zzzzzz.

My code looks loke this:

IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = 'zzzzzz' )
DROP ENDPOINT zzzzzz
GO

CREATE ENDPOINT zzzzzz

STATE = STARTED AS HTTP (
site='servername',
path='/sql/zzzzzz',
AUTHENTICATION=(INTEGRATED),
PORTS = (CLEAR) )
FOR SOAP(
WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'), WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),
WSDL = DEFAULT,
BATCHES=ENABLED)
GO
-- End of Script --

The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005. If anyone has an idea about my problem, please help me !!!
Thank You
Zee.

This evening I received a very similar response to the following T-SQL execution while under MS Server 2003 and SQL Server 2005;

CREATE ENDPOINT [xxx] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

An endpoint already exists with the bindings specified. Only one endpoint supported for a specific binding. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.

Msg 7807, Level 16, State 1, Line 9

An error ('0x800700b7') occurred while attempting to register the endpoint 'xxx'.

sql

endpoint already registered to another service

I am unable to create endpoint in the Windows 2003 server using SQL 2005. I also tried reserving the namespace before CREATE but i still end up with this error

Msg 7806, Level 16, State 1, Line 1
The URL specified by endpoint 'zzzzzz' is already registered to receive requests or is reserved for use by another service.

Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint zzzzzz.

My code looks loke this:

IF EXISTS ( SELECT name from sys.http_endpoints WHERE name = 'zzzzzz' )
DROP ENDPOINT zzzzzz
GO

CREATE ENDPOINT zzzzzz

STATE = STARTED AS HTTP (
site='servername',
path='/sql/zzzzzz',
AUTHENTICATION=(INTEGRATED),
PORTS = (CLEAR) )
FOR SOAP(
WEBMETHOD 'http://servername/' . 'sp1' (NAME = 'dbName.dbo.sp1'), WEBMETHOD 'http://servername/' . 'sp2' (NAME = 'dbName.dbo.sp2'),
WSDL = DEFAULT,
BATCHES=ENABLED)
GO
-- End of Script --

The script that I use is correct and it works fine on my local machine (Windows XP). The user that I used to crete an endpoint on the server has 'sysadmin' level. The IIS was already turned off. Also I run the script by using the RemoteDesktop to connect to the server (Windows 2003) that has SQL Server 2005. If anyone has an idea about my problem, please help me !!!
Thank You
Zee.

This evening I received a very similar response to the following T-SQL execution while under MS Server 2003 and SQL Server 2005;

CREATE ENDPOINT [xxx] STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATABASE_MIRRORING (ROLE = ALL)

An endpoint already exists with the bindings specified. Only one endpoint supported for a specific binding. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.

Msg 7807, Level 16, State 1, Line 9

An error ('0x800700b7') occurred while attempting to register the endpoint 'xxx'.

Ending a Conversation

I am attempting to learn Service Broker from Bob Beauchemin's book "A Developer's Guide to SQL Server" - Chapter 11. I'm finding it to be very good but I'm confused over the concept of closing a conversation. Could someone answer the following questions for me?

When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

Thanks very much.

Amos

I find Bob's book a real treasure chest, covering so much of the new SQL features.

Amos wrote:

1. When a conversation is ended, can the conversation handle that was created when the conversation was created still be used? (I assume not)

Yes and no. After ending a conversation the handle can no longer be used to SEND (or MOVE), but it can still be used to END again (a no-op) or to END ... WITH CLEANUP (e.g. in case messages are stuck in transmission_queue and you know the're never going to get trough). Both sites have to END the conversation and once the EndDialog message is received from the other side, the conversation endpoint handle is deleted so all verbs (SEND,END, MOVE) will fail because the conversation handle value is invalid. the target side might keep around a handle for up to 30 minutes after it was closed (ENDed by both sides) for security reasons (prevent a replay attack).

Amos wrote:

2. Beauchemin says, on page 511, that when a conversation ends, "Any messages still in the queue from the other end of the conversation are deleted with no warning." Does this mean that if I send a message that expects a reply, but I end the conversation, the message is still sent, it is still received by the other endpoint, the other endpoint processes it, but I'll never receive the reply?

Yes, you'll not receive any reply, since you already declared that you're no longer interested in the result of this conversation (by ENDing it). Even worse, if the conversation is ended with an error, you'll never know that about error, including system errors like 'SEND permission denied'. See http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx for more on this.

Amos wrote:

3. Beauchemin says that if no lifetime is specified, the conversation is active for the number of seconds which can be represented by the maximum size of an integer. Does this mean that if I don't specify a lifetime, a conversation is active for many, many years?

That is correct. In theory, your conversation will expire after many, many years (68, I believe). In practice, I doubt any application written today will be around after 60 years...

HTH,
~ Remus

end conversation causing blocks

Hi

I was trying to clean up some conversation in Service Broker and caused alot of blocking that I seem to unable to kill. there was 1 conversation that I was not able to end, so I wanted to restart sql service, But I can't even restart the SQL service. I get the following in Event Viewer

Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_INIT', id 00000001A2B03540, type 2, Task 0x0000000000C2EDA8 : 0, waittime 5400, flags 0xa, owning task 0x00000002DEBCA5C8. Continuing to wait.

Has anyone come across this

thanks

Paul

have you tried to clear the conversation?

declare @.conversation uniqueidentifier

while exists (select 1 from sys.conversation_endpoints )

begin

set @.conversation = (select top 1 conversation_handle from sys.conversation_endpoints )
print @.conversation
end conversation @.conversation with cleanup

end

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

end conversation : security context could not be retrieved

HI

I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found

On the Target Server:

on Broker: Message undeliverable This message could not be delivered because it is a duplicate

On the Source Server

on Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved,

I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this

conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL

I reinstalled the broker several times, but always get this problem.

thanks

Paul

Each Broker: Message undeliverable This message could not be delivered because the security context could not be retrieved event should also be accompanied by an event of type Security Audit/Audit Broker Conversation. That info should explain why the 'security context could not be retrieved'.|||

Hi

I did trace with audit broker conversation, there is this event after it, but there are not error message? I recently ungraded to sp3, could this be a problem

thanks

Paul

|||

HI

I was reading the other thread about no security header and check the time in the enqueue time, which was ahead of the server time

the last enqueue_time on the target was

2007-04-13 22:27:16

but when I did a select getdate()

I get 2007-04-13 20:24:03

how can the enqueue time be in the future?

thanks

Paul

|||what is the event subclass?|||Because of the distributed nature of messaging, all SSB times are UTC|||

it is : no security header. but I check the time on both machines, they are the same. If they were different, the send message would have the same error as the end dialog message?

Paul

|||Check if UTC time is the same, using getutcdate(). A problem could be if latest daylight savings patch was applied on one machine and not on other.|||

hum? I did a getutcdate() on both machines, they show the same time. If the time was the problem, wouldn't the sent message fail too? But I am unable to verify whether KB931836 was installed on one of the machines. I'll need to ask the SA on Monday. But the date change patch you bring up is an interesting point. If one of the machine does not have the patch, it would explain another problem we had last week regarding insert records from another machines where its was 1 hr late. thanks for the help. we'll see monday

Paul

|||

HI

I still have this problem, I was told that the servers where both patched with sp2 several weeks ago. I did more tracing on the sender and target. It seems the end dialog message from the target was successfully pocessed by the sender queue. Only after when the queue process the end dialog do I see "security context could not be retrieved" in the trace. Does Service Broker send another message to the the target after the sender ends the conversation?

Paul

|||

I met the same problem in our environment. our environment is:

Sender: SQL 2005 SP1, 64Bit CPU, windows 2003, 32G memory (currently only has SSB run on it, seems use more than 20g memory and about 40 % cpu resource)

Target: SQL 2005 SP1 ,32bit CPU, widnows 2003, 16G? memory.

the same situation, when the sender END CONVERSATION... i get the "security context could not be retrieved" from profiler90.

sql

Encrypting mdf files

Hi,

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.

Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

You can access 'service account's cert store through mmc -

Quote:

Originally Posted by

>Certificates Snap-in -Service account

Thanks,
rsm
---rsm (prakandapandit@.yahoo.com) writes:

Quote:

Originally Posted by

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

No.

If you are using SQL 2005, there are encryption routines builtin,
so that you encrypt some columns. Keep in mind that encrypting key
columns will have a very serious impact on performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Feb, 04:16, "rsm" <prakandapan...@.yahoo.comwrote:

Quote:

Originally Posted by

Hi,
>
We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?
>

No. Assuming you are using SQL Server 2005 you should read the
encryption topics in Books Online.

It is in principle possible to encrypt every bit of user data in a
database, but I can't think of any good reasons for wanting to do that
- and there are many good reasons why NOT to do it. Could you explain
a bit more about your requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||We are using SQL Server 2000.

We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.

Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.|||"rsm" <prakandapandit@.yahoo.comwrote in message
news:1172172849.993451.142190@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

We are using SQL Server 2000.
>
We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.
>
Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.
>

There is no built-in encryption in SQL 2000, so I'm 99% sure the answer is
no.

Simple answer; the user SQL Server runs under shouldn't be changing its
password often and when it does, should go through a normal change
procedure.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Enabling the Service Broker

I'm trying to enable the Service Broker for Sql Server 2005 because I want to be able to use a SqlDependency object.

I ran the following query to see if my local sql server service broker was enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname';

It came back with a value of 0 (which means it is not enabled).

I tried executing the following sql command to enable it:
ALTER DATABASE dbname SET ENABLE_BROKER;

The query has been running for over 5 mins and just keeps spinning (should it take this long to enable the Service brfoker), so I cancel it.

I even try to issue the command to see if the service broker is enabled after I cancel the query and it is not enabled.

How can I properly enable the Service Broker?

You need exclusive access to the database. See http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||

Thanks for the tip. It resolved my issue of enabling the service broker.

Now that I have it enabled, I am testing an application, and have code set up which is supposed to test whether a table got updated or not and am not haveing any luck with the Haschanges method changing to TRUE.

I have the following sql set up in my stored procedure:

<code>

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
GO
ALTER PROCEDURE [dbo].[SelectAllQCCSwitch]
AS
SET NOCOUNT ON;
SELECT GateWay, Description, CDRTable, Priority
FROM QCCSwitch

</code>

Here is my vb.net code that I have inside a class:

<code>

Public blnQCCSwitch As Boolean = False

Dim depQCCSwitch As New SqlDependency()

.

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try

If blnQCCSwitch = False Then

depQCCSwitch.AddCommandDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)

SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)

blnQCCSwitch = True

End If

If (depQCCSwitch.HasChanges) OrElse (blnQCCSwitch = False) Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

Else

If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

End If

End If

Return DstFindBatch2

Catch ex As Exception

Throw ex

Finally

clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()

clsSelectAllQCCSwitch.Dispose()

End Try

End Function

.</code>

Notice the BOLDFACED text above which is what specifically concerns this post.

During debugging, I simply manually added a record to the table which is included in the "clsSelectAllQCCSwitch.cmdSelectQCCSwitch" Select command object.

I was expecting the HasChanges method of the Dependency object to change to TRUE and read the DB again to refresh the data.

What am I missing that I need to do?

.

|||

After you started the subscription, you are going to receive the notifications on the OnChangeEventHandler callback. You shouldn't explictily check for changes, you should simply continue and rely on the callback to notify you when a change occured.

HTH,
~ Remus

|||

Remus, thanks for the info...

I took that example logic and applied it to my app. I'm still expecting the Onchange event to occur (which only happens the first time the table is hit in my code) after I manually change a record in the table. However, the OnChange event is not firing when I do this. Is this because of the ConnectionString (meaning the table would have to be changed via the same connectionstring other than me doing it manually in order for it to fire)?

Here is my middle tier logic code. If you can verify that is seems okay, I would appreciate it very much. Or if I need to change something else.....

<code>

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
SqlDependency.Stop(clsSelectAllQCCSwitch.Connection.ConnectionString)
SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)
Dim depQCCSwitch As New SqlDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)
AddHandler depQCCSwitch.OnChange, AddressOf QCCSwitchDependency_OnChange
If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
End If
Return DstFindBatch2
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
End Try

End Function

Private Sub QCCSwitchDependency_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
Dim dependency As SqlDependency = CType(sender, SqlDependency)
RemoveHandler dependency.OnChange, AddressOf QCCSwitchDependency_OnChange
End Try

End Sub
</code>

|||

Have a look at the MSDN SqlDependency sample at http://msdn2.microsoft.com/en-us/a52dhwx7.aspx

The updates that trigger the notification can happen on any session and under any settings, it is not related whatsoever to the connection string of the SqlDependency.

Each time the OnChange is fired, the underlying Query Notification gets torn down and you have to subscribe again to get a new notification next time a change occurs. In the MSDN sample, the client calls again GetData at the end of it's dependency_OnChange method. Inside the GetData method, it sets up again a notification.

If you believe you've set up the notification correctly and applied an update yet the notification does not fire here are some steps to investigate:

- make sure the notification is created, see it in select * from sys.dm_qn_subscriptions
- make sutre the notification is fired. The Profiler will show this as an Broker:Broker Conversation event with the subclass SEND.
- make sure the notification message is delivered. Look in the database sys.transmission_queue, if the notification is still pending there it should have a transmission_status explaining why it cannot be delivered. My blog has a troubleshooting guide, at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx. While is generic for Service Broker, it applies just as well to Query Notification messages.

If the notification is successfuly delivered, then the OnChange method should fire.

HTH,
~ Remus

enabling service broker in sql server 2005

hello

i use Visual Web Developer and SQL server 2005 express , how can i enable service borker of my sql server ?

thaks for any help

derin

It is not part of SQL Server Express you need the developer edition to run it. Try the links below for two articles about it. Hope this helps.

http://www.ftponline.com/vsm/2006_06/magazine/features/rjennings/

http://msdn.microsoft.com/msdnmag/issues/06/07/InsideMSDN/default.aspx

Enabling service broker

SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this morning.
>> Am I missing something in my install? Has anyone ever seen this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help is
>> graciously appreciated!!
>>
>|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
--
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>>
>|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> 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
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from one
>> server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
--
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Moving system datbases ([master], [model], [temp] and [msdb]) between
>> instances of the is never proper, no matter the steps involved.
>> --
>> 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
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from
>> one server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to
>> run the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>>
>

Enabling service broker

SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/a.../30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>

Enabling service broker

SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!
See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>
|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>
|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>
|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> 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
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>
|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
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
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>

Enabling Fulltext Service on database

Hi guys,
one of my customers moved a SQL Database from one server
to another. The database had 2 fulltext catalogs associated.
On the new DB Server the fulltext service doesn't start using
sp_fulltext_database 'enable'.
QA shows "Executing Query Batch..." but never returns.
I googled on the newsgroups and found the solution to update the
path in the sysfulltextcatalogs to a physical existent folder on the
new server. I tried again with sp_fulltext_database but again it
doesn't work.
The only thing I need is to clean/remove the two catalogs, rebuild
them and enabling the fulltext. What can I do ?
I read also the support ms doc #240867.
So, what am I missing ? Thank you all.
David De Giacomi
Microsoft MVP
Blog @. http://blogs.dotnethell.it/david/
http://www.dotnethell.it
sp_fulltext_database 'disable' in your problem database and run profiler to
see where it is getting stuck (if it does at all). Then I would try to
reenable it (sp_fulltext_database 'enable') and again run profiler to see
where it is getting stuck.
Make sure you kick all users out of this database when you run this command.
I would also bounce the box if you have this luxury before running this
command.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David De Giacomi [dotNetHell.it]" <david_NOSPAM@.dotnethell.it> wrote in
message news:uv%23YZTGOGHA.812@.TK2MSFTNGP10.phx.gbl...
> Hi guys,
> one of my customers moved a SQL Database from one server
> to another. The database had 2 fulltext catalogs associated.
> On the new DB Server the fulltext service doesn't start using
> sp_fulltext_database 'enable'.
> QA shows "Executing Query Batch..." but never returns.
> I googled on the newsgroups and found the solution to update the
> path in the sysfulltextcatalogs to a physical existent folder on the
> new server. I tried again with sp_fulltext_database but again it
> doesn't work.
> The only thing I need is to clean/remove the two catalogs, rebuild
> them and enabling the fulltext. What can I do ?
> I read also the support ms doc #240867.
> So, what am I missing ? Thank you all.
> --
> David De Giacomi
> Microsoft MVP
> Blog @. http://blogs.dotnethell.it/david/
> http://www.dotnethell.it
>

Enabling Full-Text search for SQL 2000 Personal Edition

I am having trouble Enabling Full-Text search for SQL 2000 Personal Edition.
I have installed the Microsoft Search service and it is running. When I look
in enterprise manager the Full-Text Search option does not appear in the
Support Services folder. How can I accomplish this?
SQL FTS is not installed by default on the personal edition of SQL Server
You will need to re-run setup to install this component.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David O. Thomas" <David O. Thomas@.discussions.microsoft.com> wrote in
message news:7DFCF7F9-8AD8-4635-83DB-29AB6F0B9241@.microsoft.com...
> I am having trouble Enabling Full-Text search for SQL 2000 Personal
Edition.
> I have installed the Microsoft Search service and it is running. When I
look
> in enterprise manager the Full-Text Search option does not appear in the
> Support Services folder. How can I accomplish this?

Enable SQL Server 2005 Remote Connections in Windows 2000

Hi,
I setup SQL Server 2005 on a Windows 2000 Professional machine. The
MSSQLSERVER service seems to be running fine in the beginning but does
not restart if I enable connections via TCP/IP.
I have this problem only on a Windows 2000 machine and it works fine if
I setup SQL Server 2005 on a Windows XP machine.
- ramadu
:
> Thanks for your response Sri,
> Hope that will help you resolve the problem. If you meet any further
> problem, please feel free to post here.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>Hi Ramadu,
Thank you for your post.
Since Steven is Out of Office, I will take response of this issue.
Would you please provide some detailed information for me to troubleshoting?
1. Please let me know if there is any error in Event log when you start SQL
Server.
2. Please let me know if there is any error message in the SQL error log.
By default, the error log is located at Program Files\Microsoft SQL
Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier