The SQL Server Service Broker Or Database Mirroring Transport Is Disabled Or Not Configured

Jul 13, 2007

Hi,



I received an alert 'The SQL Server Service Broker or Database Mirroring transport is disabled or not configured'



How can I check the status of the two? and if they are disabled how do I enable them?

View 6 Replies


ADVERTISEMENT

Database Mirroring Transport Is Disabled In The Endpoint Configuration.

Aug 3, 2006

Hi.

I am getting this error.

Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.

Someone please help me.It's urgent.

I am using same PC with 2 different intsances.

This is how i have done it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@#56'

GO

-- CREATE CERTIFICATE PRINCIPAL_CERT

CREATE CERTIFICATE PRINCIPAL_CERT

WITH SUBJECT = 'PRINCIPAL CERTIFICATE',

START_DATE = '03/07/2006',

EXPIRY_DATE = '01/01/2010'

GO

-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM

CREATE ENDPOINT EPMIRRORING

STATE = STARTED

AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,

AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)

GO

Here is the of Event Log.

Date,Source,Severity,Message
08/03/2006 15:54:35,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 15:54:35,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 15:52:57,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 15:52:55,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:14:54,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:14:54,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 14:14:37,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 14:14:35,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:46,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:43,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:41,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:38,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:36,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:34,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:33,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:32,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:30,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:15,spid54,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:03:15,spid54,Unknown,Server is listening on [ 'any' <ipv4> 5022].

Any solution ?
Thanks
Mihir

View 5 Replies View Related

Database Mirroring Transport Is Disabled In The Endpoint Configuration.

Oct 16, 2007



Hi,

i have configured a mirroring database using three different server instances on same domain. i'm able to configure the principal, mirror and witness using the configure database mirroring security wizard. I leave the service accounts blank and complete the process successfully but when i hit on start mirroring button it gives me error i.e 'database Mirroring Transport is disabled in the endpoint configuration.' Kindly help.

Regards

View 3 Replies View Related

Service Broker And Database Mirroring

Jul 24, 2006

I read the blog about service broker and database mirroring. Have you actually gotten this to work? I have a database that is mirrored. A third party vendor's software is using query notification which in turn uses service broker for dirty cache notification. Based on your blog I created a route specifying a mirror address.

CREATE ROUTE ServiceBrokerMirrorRoute AUTHORIZATION dbo
WITH SERVICE_NAME = 'http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService', BROKER_INSTANCE = '70D4070D-C739-43B3-9167-7156209F87BA', ADDRESS = 'TCP://10.128.183.210:5100',
MIRROR_ADDRESS = 'TCP://10.128.183.211:5100'

And then I tried to ALTER DATABASE SET ENABLE_BROKER but it fails with a

Server: Msg 9778, Level 16, State 1, Line 1
Cannot create a new Service Broker in a mirrored database "mydatabase".
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Is there an order to this should I have enabled servicebroker on the database and then mirrored it?

Any help would be appreciated!

View 1 Replies View Related

Service Broker TO Service Could Not Be Found Message Origin: Transport

Mar 30, 2007

I am trying to send a message between to SQL Server 2005 instances on two different machines. I have checked all my routes and all my objects appear to be setup correctly. However, when running Profiler on the target machine, I receive the "This message has been dropped because the TO service could not be found. Service name: "[tcp://mydomain.com/TARGET/MyService]". Message origin: "Transport". This is my activated stored procedure that is sending the message to the target service. I am using certificate security. Any help appreciated....



CREATE PROCEDURE [usp_ProcessMessage]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @conversation_handle uniqueidentifier

DECLARE @message_body AS VARBINARY(MAX)

WHILE (1=1)

BEGIN

BEGIN TRANSACTION;

WAITFOR(RECEIVE TOP (1)

@conversation_handle = conversation_handle,

@message_body = message_body

FROM [tcp://mydomain.com/INITIATE/MyQueue]

), TIMEOUT 1000;

IF (@@ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

END CONVERSATION @conversation_handle

IF @message_body IS NOT NULL

BEGIN



BEGIN DIALOG CONVERSATION @conversation_handle

FROM SERVICE [tcp://mydomain.com/INITIATE/MyService]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

ON CONTRACT [tcp://mydomain.com/INITIATE/MyMessage/v1.0]

WITH ENCRYPTION = ON, LIFETIME = 600;

SEND ON CONVERSATION @conversation_handle

MESSAGE TYPE [tcp://mydomain.com/TARGET/VisitMessage]

(@message_body);

END

COMMIT;

END

END

GO



My endpoints are created like so:



CREATE ENDPOINT MyEndpoint

STATE = STARTED

AS TCP

(

LISTENER_PORT = 4022

)

FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE MasterCertificate)

GO



GRANT CONNECT TO CertOwner

GRANT CONNECT ON ENDPOINT::MyEndpoint TO CertOwner

GO



And my routes like so:



GRANT SEND ON SERVICE::[tcp://mydomain.com/INITIATE/MyService] TO CertOwner

GO

CREATE REMOTE SERVICE BINDING [MyCertificateBinding]

TO SERVICE '[tcp://mydomain.com/TARGET/MyService]'

WITH USER = CertOwner,

ANONYMOUS=OFF

CREATE ROUTE [tcp://mydomain.com/INITIATE/MyRoute]

WITH SERVICE_NAME = '[tcp://mydomain.com/TARGET/MyService]',

BROKER_INSTANCE = N'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',

ADDRESS = N'TCP://xxx.xx.xx.xx:4022'

GO

View 10 Replies View Related

WCF Transport Channel For Service Broker

Mar 7, 2006

I saw a posting or read a blog somewhere that mentioned custom WCF transport channels where created for interacting with ServiceBroker. Has anyone read or heard something similar? If so, where can I get them?

Thanks.

Phil

View 15 Replies View Related

SQL Service Broker - Transport Security

Feb 20, 2007

If all my SQL Server instances are mutually trusted, am planning to implement transport layer security with Windows authentication. My query is that if I'm using Windows authentication do I need certificates to be created? Though I am using Transport security, I can achieve encryption by ENCRYPTION - ON in the Begin Dialogue conversation. I assume Message integrity using MD5 signatures are by default provided by Service broker irrespective of whichever service options we choose?

Can I have some article references on how these security mechanisms will impact the performace of Service broker communications?



Thanks a lot,

View 5 Replies View Related

Service Broker Dialog And Transport Security

May 25, 2006

what is the difference between the Dialog security and the transport security?

If I disable the dialog security can the messages be sent to the a different SQL server instance. As the transport security will encrypt the messages.

If I don't create a certificate to be used by the transport security can the messages be sent to another SQL server instance?

Can a message be sent to another SQL Server instance without creating a REMOTE Service Binding?

View 1 Replies View Related

Disabled Service Broker

Aug 8, 2007

I have a windows service that uses SqlDependency. It has been working fine until today where it has died. I have traced the problem back to the service broker being disabled, however I have not disabled the service broker. Is there anyway that the service broker can become disabled ?

View 1 Replies View Related

The SQL Server Service Broker For The Current Database Is Not Enabled, And As A Result Query Notifications Are Not Supported. Please Enable The Service Broker For This Database If You Wish To Use Notifications.

Feb 16, 2008

Hello,          I receive this error  "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER β€˜''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' β€˜'''<<-------value is 1 Global.asax ...    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString)    End Sub...Web.config ...    <connectionStrings>        <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />        <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />    </connectionStrings>... Hope you could help.  cheers,imperialx 

View 1 Replies View Related

Error 8355 - Service Broker Is Disabled - How To Confirm?

Oct 26, 2007

During startup one of my servers, containing a default and a named instance of 2005, receives the following error.

Message
Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.

The system databases for both instances were restored using backups from another instance. How do I confirm if Service Broker is enabled or disabled and why would I get this message? Is it due to restoring master or msdb?

Thanks, Dave

View 5 Replies View Related

SQL 2012 :: Service Broker Endpoint Is In Disabled Or Stopped State

May 5, 2014

I have a SQL 2012 Enterprise edition HADR cluster with 2 nodes and my event log on the second node (current primary for the AG) is being filled with these messages

The Service Broker endpoint is in disabled or stopped state

I wasn't able to find too much on the issue. I did find this one open Microsoft connect bug report from back in April 2013 [URL] .....

O/S: Windows 2012
SQL Server 2012 v11.0.3412
Currently 1 AG with 1 DB

View 3 Replies View Related

First Service Broker Attempt, Prajdic's Example, Error: Queue Has Been Disabled

Sep 17, 2007



I am using the Centralized Asynchronous Auditing with Service Broker article example to set up my first Service Broker attempt. We want to start logging search criteria and search results for our product search page. We wanted it to be asynchronous and be stored in another dbase, this seemed like the perfect example.

I modified the example above to save into an Audit table we created and to read a custom message that I generated the XML for. I'm pretty certain that should all work. However, I didn't change much else but I can't get the message to send.

This is the error I'm getting in profiler:
This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742.

I am sending from one database to another within the same SQL server instance. Here are the profiler details
SQL:BatchStarting
EXEC dbo.usp_SendAuditData @X Microsoft SQL Server Management Studio - Query Larry APP1Larry
Broker:Conversation Group Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation STARTED_OUTBOUND Microsoft SQL Server Management Studio - Query Larry
Broker:Conversation CONVERSING Microsoft SQL Server Management Studio - Query Larry
Broker:Message Classify Microsoft SQL Server Management Studio - Query Larry
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. Microsoft SQL Server Management Studio - Query Larry APP1Larry
SQL:BatchCompleted
EXEC dbo.usp_SendAuditData @X Microsoft SQL Server Management Studio - Query Larry APP1Larry
Broker:Message Classify
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Remote Message Acknowledgement
Broker:Message Classify
Broker:Message Undeliverable This message could not be delivered because the destination queue has been disabled. Queue ID: 197575742. sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa
Broker:Message Undeliverable This message was dropped because it could not be dispatched on time. State: 2 sa


When I run SELECT * FROM sys.transmission_queue I get this in the transmission_status:
"One or more messages could not be delivered to the local service targeted by this dialog."


I'm hoping someone can point me in the right direciton, thanks.

View 11 Replies View Related

Service Broker Mirroring Problem

Oct 26, 2007

Hi,

My company has a system where we have a mirrored server and multiple clients communicating with that server using service broker. Up until now although we had mirroring set up, we weren't bothering to specify a mirror when creating routes from the clients to the server. Now that we've done this messages can be sent from the server to the clients, but not the other way round.

I suspect this is because the mirror database instance does not have the same certificates installed in its master database as the primary (we are using both transport and dialog security). I thought about manually copying the certificates over from backups, but I don't think this would work. Also, according to one of my colleagues, it isn't possible to mirror the master database (which makes sense).

I've tried altering the endpoints on each of the machines involved using the following to disable transport encryption, but maybe I'm not understanding something since this doesn't seem to work:





Code Block

use master
alter endpoint [EndpointName]
for service_broker (encryption = disabled)

My question is how can I either make sure that anything required for transport security is available to the mirror, or how do I disable transport security and simple use dialog security instead (and also if this is actually the problem).

Thanks in advance,

Adam Connelly

[edit] I think I've realised why disabling encryption doesn't do anything for me (i.e. the certificates are still used to sign the messages), but unfortunately knowing that doesn't solve my problem [/edit]

View 4 Replies View Related

'This Database Has Not Been Configured For Mirroring' But It Has

Jun 4, 2007

Ok i am very new to the word of mirroring in sql 2005, i am trying to get it to work, i have 3 servers

my witness server

mirror server and then of course the principal server, all are completely different servers.



I have a copy of the db on the princapal and the mirror server (excatly the same, snapshot)

right click on the princapal db and go to properties choose mirroring run through the' configure security' wiarzd

it resolves all my other DB server as it should. (using the same sql authentication also tried windows intergrated)



when i start the mirror i get unable to start

This database has not been configured for mirroring



what am i not doing???
how do i configure this damn thing?



thanks



Brad

View 2 Replies View Related

Database Mirroring Transport Error

May 24, 2006

Hi

I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.

After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing

alter database set partner="..."

Am I missing something?

Thanks

View 1 Replies View Related

Mirroring :: Can Database Mirror Configured On Single Stand Alone Installation?

Jul 15, 2015

I want my database to be mirrored . is it possible to mirror it on the same machine.

I have tried to create a named instance but while trying to configure its not visible.?

Am using SQL SERVER 2008

View 6 Replies View Related

Service Broker Is Disabled In MSDB Or MSDB Failed To Start

Nov 21, 2005

Whenever I start my SQL Express 2005 database, I get the following in the logs :

View 10 Replies View Related

SQL Server 2008 :: Service Broker Queue In Database

Feb 3, 2015

I had to restore a database in one of the staging servers the other day. However, upon completion, I found out that the service broker queue in the database is not working anymore. The service broker queue error was similiar to this error:
The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the following: '<error message>.'

View 0 Replies View Related

How To Prevent The Hang On The Initator Service Broker If The Target Service Broker Is Not Started?

Sep 10, 2007

How to prevent the hang on the initator service broker if the target service broker is not started?

Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?

View 3 Replies View Related

Service Broker On The Same Database.

Jul 11, 2006

I'm trying to create a service broker on the same database...with one service and one queue. But my queue ends up with nothing in it. Here is the source...what's wrong?

Alter Database adventureworks set ENABLE_BROKER;

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

create master key encryption by password = 'P@SSw0Rds';

CREATE MESSAGE TYPE CreateQBLetters VALIDATION = NONE

CREATE MESSAGE TYPE LetterResponse VALIDATION = NONE

CREATE CONTRACT BuildQBLetters (CreateQBLetters SENT BY INITIATOR, LetterResponse SENT BY TARGET)

CREATE QUEUE BuildLettersQueue with status=on

CREATE SERVICE CreateQBLetters ON QUEUE BuildLettersQueue

-- At this point, we can begin the conversation

DECLARE @conversationHandle UNIQUEIDENTIFIER

DECLARE @message NVARCHAR(max)

BEGIN

BEGIN TRANSACTION;

BEGIN DIALOG @conversationHandle

FROM SERVICE CreateQBLetters

TO SERVICE 'CreateQBLetters'

ON CONTRACT BuildQBLetters

-- Send a message on the conversation

SET @message = N'Hello World';

SEND ON CONVERSATION @conversationHandle MESSAGE TYPE CreateQBLetters (@message)

END CONVERSATION @conversationHandle

COMMIT TRANSACTION

END

GO

select * from sys.transmission_queue

select * from sys.conversation_endpoints

-- peek into the queue

select cast(message_body as nvarchar(max)) from BuildLettersQueue

GO

-- Receive a message from the queue

--RECEIVE CONVERT(nvarchar(max), message_body) AS message FROM BuildLettersQueue



-- Cleanup

DROP SERVICE CreateQBLetters

DROP QUEUE BuildLettersQueue

DROP CONTRACT BuildQBLetters

DROP MESSAGE TYPE CreateQBLetters

DROP MESSAGE TYPE LetterResponse

GO

-- you have to clean out the service broker first -- a conversation could be hanging around

alter database adventureworks set NEW_BROKER;

GO

DROP master key

go

View 3 Replies View Related

Restore Database With Service Broker

Feb 15, 2008

I have a database that we have running service broker. Its a web based application hitting the database. Occasionally I need to restore the database in the development environment. Every time I trie to restore I have issues because there is a service broker connection. I have tried various commands to stop it, and usually after 20 minutes I finally get it to stop and not recreate itself.

Can anyone point me to an article or provide information on the best way to stop/prevent service broker so that you can restore a database?

View 4 Replies View Related

Could Not Start Service Broker For Database Id

Feb 29, 2008

I am getting this message in the SQL Server error log:

Could not start Service Broker for database id: 10. A problem is preventing SQL Server from starting Service Broker. Check the SQL Server error log for additional messages.


It appears even when the broker is disabled on the database. How can I eliminate this error?

Thanks!

View 5 Replies View Related

Service Broker Mirror Database

Feb 14, 2007

Hi

I tried to setup service broker to fail over to a mirror database. After the target database failed over, the message I send stays in sys.transmission_queue with no error in transmission_status. I check the mirror server log and I see the following.

An exception occurred while enqueueing a message in the target queue. Error: 15581, State: 3. Please create a master key in the database or open the master key in the session before performing this operation.

The database has the master key because its a mirrored database? Did I have to do something extra here

Paul

View 5 Replies View Related

How To Enable Service Broker For Specified Database?

Dec 21, 2006

Hi All,

I am newbie in SQL Service broker. As we know, the is_broker_enabled can help us check whether the specified database is enabled for service broker. I would like to know

<>How can I enable servce broker for specified database if I see the value(is_broker_enabled) is 0?

<>How can I disable the service broker for specified database is I see the vlaue (is_broker_enabled) is 1? Do I need to dropped all service broker configration in the specified database?

Thanks in advance.

Michael

View 5 Replies View Related

Msg 9772 -- Service Broker In Database Cannot Be Enabled

Nov 29, 2005

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).

View 4 Replies View Related

Architectural (broker) Place Of SQL Service Broker

Apr 5, 2007

Hi,



I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.



I look forward to some responses.



Kind regards,

Neeva

View 2 Replies View Related

The SQL Server Service Broker Challange

Jun 19, 2005

Can't believe no one has commented on this in here yet. If you're into SQL Server Service Broker (and you are if you are reading this forum), you must go over and take Rushi's Service Broker Challange.

View 1 Replies View Related

SQL Server Service Broker Examples

Jul 17, 2005

Recently there has been questions on some of the newsgroups about examples for SQL Server Service Broker. So, yesterday on a flight back to England I crofted up three different SQL Server projects as examples: LocalSampleRemoteSampleServer1RemoteSampleServer2
All three examples are jus your very basic €œHello World€? example, but they show: LocalSample €“ communication between two databases on the same SQL server instance. MasterKeys in a database and the database being TrustWorthyRemoteSampleServerX €“ These two samples are meant to run on two different machines/instances and in the samples you set up both transport security and dialog security by using Certificates.
Download the zip file from here [0], unzip and read the README.txt file, and €“ Have Fun!!
[0] : http://staff.develop.com/nielsb/code/servicebrokerexamples.zip
 

View 6 Replies View Related

SQL SERVER 2005 Service Broker

Jul 23, 2007



Does SQL SVR 2005 Service Broker use connection pooling?



We are building a laptop application that will use SB to send messsages

to one SQL SVR 2005 instance. SQL 2005 Express will be installed on

each laptop and will be the "sender" to one SQL SVR instance, the "receipient"



It will be deployed to over 1500+ laptops. Each laptop will have a different

IP address.



Our SQLDBA wants to know if each of the laptops will use a different

connection when sending messages?



I answered "Yes", but another team member said "No".



What is the correct answer?







View 7 Replies View Related

Enabling Service Broker In Sql Server 2005

Oct 23, 2006

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

View 1 Replies View Related

Insert To A Linked Server Possible Via Service Broker?

Jul 14, 2006

I have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:
The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'

As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?


CREATE PROC sp_mytableServiceProgram
AS
SET NOCOUNT ON;

DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;

-- This procedure continues to process messages in the queue until the
-- queue is empty.

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.

-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@Dialog = conversation_handle
FROM mytableQueue
), TIMEOUT 2000 ;

-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END ;

SET @XML = CAST(@MessageBody AS XML);

INSERT INTO LINKEDSERVER.dbname.user.mytable
SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no,
tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid,
tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num,
tbl.rows.value('@status', 'NVARCHAR(20)') AS status,
tbl.rows.value('@creationtime', 'DATETIME') AS creationtime,
tbl.rows.value('@note', 'NVARCHAR(250)') AS note,
tbl.rows.value('@posted', 'NCHAR(1)') AS posted,
tbl.rows.value('@kms', 'INT') AS kms,
tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid,
tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code,
tbl.rows.value('@page_count', 'INT') AS page_count,
tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number,
tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution
FROM @XML.nodes('/inserted') tbl(rows);

-- If the INSERT did not insert any rows, rollback.
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
COMMIT TRANSACTION;
END
GO

View 8 Replies View Related

Can See Service Broker Queue With SQL Server Login

Sep 7, 2006

I have an application that is reading a message from a Service Broker Queue. When I use integrated security with an NT account it works fine. When I use a SQL Server User through Management Studio I can select from the Queue however, when I use this same account through the web app with the SQL Server User, I cannot see the Queue. Is there a grant that I must do to this account to get it to see the Service Broker Objects?

Gary

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved