Service Broker Message On Remote Instance Not Working

Service Broker: Cross DB, Same Instance
I'd like to set up a Service Broker queue in one database (dbRespond) on a server so that events in another database (dbEvent) on the same server instance can post messages to the queue.  The problem I'm having is that:

The BEGIN DIALOG CONVERSATION needs to reference a Contract that is in the current database, and I want to call BEGIN DIALOG CONVERSATION from dbEvent
The target service is in dbRespond.  Based on the "Hello World" Service Broker example that comes with SQL 2005, in dbRespond I need to specify the contract in the CREATE SERVICE call that creates the target service.  Here, too, the contract must be defined in the current database.
How do I deal with needing to have the same one contract in two different databases?

 

View Replies !   View Related

---

Service Broker: Trigger On New Message
Hi,

 

I have a small problem with my two databases ( A and B ).

 

On database A I have a queue set up for receiving messages from a service broker which are sent via a stored procedure from database B ...

 

Each time a message hits the queue on database A I would like to run a stored procedure that takes the message and actions it.

 

 

I have my stored procedures in place but can't figure out how to trigger a procedure each time a message is received. I have read this ( http://technet.microsoft.com/en-us/library/ms171601.aspx ) but would really appreciate someone posting an example of setting up queue activation.

 

Many thanks

 

Chris

View Replies !   View Related

---

Service Broker: Cross DB, Same Instance - Part 2
Hi,

I run the code sample below in a SQL Mgt Studio query window.  I get no error messages.  However, the two SELECT statements at the end produce undesireable results (see comments).  My messages to not make it to the target queue; they get stuck in sys.transmission_queue with the error message in the final comment of the code.  The server principal "ddddXXXXX" is a Windows Active Directory account that: 1) is a domain admin in the domain that my SQL Server box is a member server of, 2) is the login used by the "SQL Server (MSSQLSERVER)" service account, and 3) is a member of the SQL Server instance's sysadmin fixed server role.

What is it I'm missing that is denying a sysadmin login access to a database? 

/***
  Service Broker try 1
***/
USE master;
GO

CREATE DATABASE sbRespond;
GO

CREATE DATABASE sbEvent;
GO

USE sbRespond;
GO

CREATE MESSAGE TYPE ActivatedStudyMessage
  VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT ActivatedStudyContract
  (ActivatedStudyMessage SENT BY INITIATOR);
GO

CREATE QUEUE [dbo].[ActivatedStudyTargetQueue];
GO

CREATE SERVICE ActivatedStudyTargetService
  ON QUEUE [dbo].[ActivatedStudyTargetQueue]
  (ActivatedStudyContract);
GO

USE sbEvent;
GO

CREATE MESSAGE TYPE ActivatedStudyMessage
  VALIDATION = WELL_FORMED_XML;
GO

CREATE CONTRACT ActivatedStudyContract
  (ActivatedStudyMessage SENT BY INITIATOR);
GO

CREATE QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO

CREATE SERVICE ActivatedStudyInitiatorService
  ON QUEUE [dbo].[ActivatedStudyInitiatorQueue];
GO

-- Send a message.
USE sbEvent;
GO

BEGIN TRANSACTION;
GO

DECLARE @message XML;
SET @message = N'
<message>
  <PROT_ID>123456</PROT_ID>
  <StudyID>AAAA1234</StudyID>
</message>
';

DECLARE @conversationHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @conversationHandle
  FROM SERVICE ActivatedStudyInitiatorService
  TO SERVICE 'ActivatedStudyTargetService'
  ON CONTRACT ActivatedStudyContract
  WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @conversationHandle
  MESSAGE TYPE ActivatedStudyMessage
  (@message);

END CONVERSATION @conversationHandle;
GO

COMMIT TRANSACTION;
GO

USE sbRespond;
GO

SELECT * FROM [dbo].[ActivatedStudyTargetQueue];
GO
--The query above returns zero records

SELECT * FROM sbEvent.sys.transmission_queue;
GO
--The query above returns two records, each with this "transmission_status" value:
--An exception occurred while enqueueing a message in the target queue.
--  Error: 916, State: 3.
--  The server principal "ddddXXXXX" is not able to access the
--    database "sbRespond" under the current security context.

View Replies !   View Related

---

Service Broker Erro But Everything Working
Hi

I have been running service broker between 3 initaitors and a single target witha forwarder for some time.

Everything works 100%, i use persistent conversation and all queues and transmission_queues are clear. Everything works 100%.

However while running profiler on the forwarder i see the following errors every now and again. Actually every time a message succesfully gets to central i see this error:

On the forwarder: Broker Connection: EventSubClass 4 - Closing :

An error occurred while receiving data: '64(The specified network name is no longer available.)'.

Database ID 1 : master:

ObjectName : tcp:11.45.101.100 - this is the target

i sometimes see the exact same error with a initiator IP.

I am very confused becuase everything is working 100%, queues and transmission queues are all clear.

Why do i see this error ?

Thanx 

 

 

 

View Replies !   View Related

---

Service Broker Message Dispatcher Error
One of my customers' SQL Server 2005 databases, which was set up to receive messages from another SQL Server 2005 database on another server has been re-installed by the customer and now the message passing no longer works. After recreating the End Point and re-enabling the Service Broker on the target database, I am now getting messages in the SQL Server error log.
 
An error occurred in the service broker message dispatcher, Error: 15581 State: 3.
 

and
 
Error: 9644, Severity 16, State: 14.

 
The two errors repeat alternately ad nauseum.
Running a trace on the target database server, for the Broker:Message Undeliverable, gives an endless stream of these entries:
 
This message could not be delivered because an internal error (code 15581, state 3) was encountered while processing it. Check the error log for more information.

 
Any ideas on how to fix this system?

View Replies !   View Related

---

Message Could Not Be Delivered Errors In Service Broker
 

Hi,

 

I am using service broker in between two database servers. following is the way i am sending and receiving messages

 

Send

 

BEGIN TRAN
  BEGIN DIALOG CONVERSATION @handle
  FROM SERVICE @SendService
  TO SERVICE @ReceiveService
  ON CONTRACT @Contract
  WITH LIFETIME = @lifetime;

  SEND ON CONVERSATION @handle
  MESSAGE TYPE @xmlMessageType(@xmlMessage);
 COMMIT

 

Receive

 

BEGIN TRAN;
  RECEIVE TOP(1) @xmlMessage = message_body,
    @handle = conversation_handle,
    @message_type = message_type_name
  FROM TransactionQueue;
  
  ----------------------------------------------------------------------------------------------------
  -- Check to make sure a message was returned to process.  In theory this should never happen.
  ----------------------------------------------------------------------------------------------------
  IF @@rowcount = 1
  BEGIN
   
   IF @message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
   BEGIN
    END CONVERSATION @handle;
    COMMIT
    RETURN 0
   END
   
   IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
   BEGIN
    RAISERROR(N'Received error %s from service [Target]', 10, 1)
    END CONVERSATION @handle;
    COMMIT
    RETURN 0
   END


  SET @sql = 'EXEC '+@message_processor_name+' @xml'

   BEGIN TRAN
    EXEC sp_executesql @sql, N'@xml XML', @xml=@xmlMessage
   COMMIT TRAN
   END CONVERSATION @handle;
  END
 COMMIT

 

I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.

 

1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end  when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.

 

2)  "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.

 

 Let me know if you need any other information

 

 

View Replies !   View Related

---

SQL Service Broker To Send Message To SSRS
Hi All,
I want to send notification to SSRS on change in database for that I am using SSB to send message to SQL Server Reporting Services.
 Can I use SSB and if yes, how? Please guide me I am new to this.   
 
Thanks,
Omkar. 

View Replies !   View Related

---

Can Service Broker Process A Email Message
How do you set up the service broker to process an email message, and how do you format that message and send it to the que.

 

Can the service broker alos process an html form from a que.

 

Thanks

View Replies !   View Related

---

Service Broker Sending Message To Self. Start Conversation With Self
I've got a situation where I want to put request message on a queue. Because starting a conversation is the only way to put messages on a queue I have to start a conversation with myself. So my Begin Dialog Statement looks something like this:

 

DECLARE @conversation_handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @conversation_handle

FROM SERVICE [ServiceName1]

TO SERVICE 'ServiceName1'

ON CONTRACT [ContractName1]

WITH ENCRYPTION = OFF;

SELECT @conversation_handle AS ConversationHandle

 

I haven't noticed any problems with doing this but I wanted to know if there was anything wrong with it. Does someone know what problems this might cause?

View Replies !   View Related

---

Scripting Service Broker Objects Using A Remote Connection
Hi
I have recently upgraded several of our SQL2005 servers to SP2a  :)  version 9.0.3050
and find that I can no longer script the Service Broker objects from a remote connection. The error I am getting is:
Script failed for MessageType 'MyMessageType' (Microsoft.SqlServer.Smo)
Additional Information
Either the object or one of its properties is not supported on the target server version
(Microsoft.SqlServer.Smo)
Now I know that this worked pre SP2 using exactly the same setup. Has anyone else experienced this or know how to fix it?

View Replies !   View Related

---

How To Prevent The Hang On The Initator Service Broker If The Target Service Broker Is Not Started?
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 Replies !   View Related

---

Remote Messages Not Working – Message Vanishes
I need some assistance with getting service broker to work across servers.  I have 2 separate servers and I am trying to send a message from one server to the other.  No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server.  Where is the message going? 
 
Here is the code I use to create the service broker objects that are being used€¦
 
RUN THIS ON THE SENDING SERVER:
 
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
 
CREATE CONTRACT [MyContract] (
       MyMessage SENT BY INITIATOR,
       MyResponse SENT BY TARGET)
GO
 
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
 
CREATE SERVICE [MyInitiatorService]  ON QUEUE [MyInitiatorQueue]
GO
 
CREATE ROUTE [RouteToODS] 
WITH 
       SERVICE_NAME = N'MyTargetService',
       BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',
       ADDRESS  = N'TCP://CONSULT01:4022'
GO
 
CREATE ENDPOINT DWHEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
       AUTHENTICATION = WINDOWS,
       ENCRYPTION = DISABLED)
GO
 
 
RUN THIS ON THE RECEIVING SERVER:
 
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
 
CREATE CONTRACT [MyContract] (
       MyMessage SENT BY INITIATOR,
       MyResponse SENT BY TARGET)
GO
 
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
 
CREATE SERVICE [MyTargetService]  ON QUEUE [MyTargetQueue] ([MyContract])
GO
 
CREATE ROUTE [RouteToDWH] 
WITH 
       SERVICE_NAME = N'MyInitiatorService',
       BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',
       ADDRESS  = N'TCP://DWH:4022'
GO
 
CREATE ENDPOINT ODSEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
       AUTHENTICATION = WINDOWS,
       ENCRYPTION = DISABLED)
GO
 
SEND A MESSAGE USING THE FOLLOWING:
 
Declare @ConversationHandle uniqueidentifier
 
Begin Transaction
       Begin Dialog @ConversationHandle
              From Service [MyInitiatorService]
              To Service 'MyTargetService'
              On Contract [MyContract]
              With Encryption = Off,
              Lifetime = 600;
      
       Send on Conversation @ConversationHandle
              Message Type [MyMessage] (N'This is a my message')
      
       End Conversation @ConversationHandle
Commit
Select GET_TRANSMISSION_STATUS(@ConversationHandle)

View Replies !   View Related

---

ODBC Connection To Remote Computer Giving Invalid Instance() Message
I am trying to set up a ODBC connection to a remote database using the ODBC data source administrator. I am getting an error message complaining of an invalid instance(). The database I am trying to connect to does have a default instance and a named instance. Do I need to specify the instance name somewhere in the connection settings? I tried specifying the server name as <Remote computer IP Address>/<Instance Name> but this didn't work. Any ideas?

View Replies !   View Related

---

Service Broker &&"TO Service Could Not Be Found&&" Message Origin: &&"Transport&&"
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 Replies !   View Related

---

In Service Broker Message &&" Dialog Security Is Not Available For This Conversation...&&"
when ever I send my message thru Service Broker I am getting an error message like this "

 

"Dialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement."

 

This I found in sys.transmission_queue

Please reply with your comments

View Replies !   View Related

---

App Receiving &&"Options&&" Message From Service Broker
I have an app receiving messages from SQL Service Broker when data is updated. (Messages are located at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo.aspx )

When I run this app against a remote SQL Server, I receive the message "Updated" which I expect.

But when I run the same app against the local machine SQL Server, I receive the message "Options".

Does anyone know if there are SQL Server options that must be set to certain values?
I can't seem to find anything that troubleshoots this message... either from a SQLServer- or a .NET standpoint.

View 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.
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 Replies !   View Related

---

Architectural (broker) Place Of SQL Service Broker
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 Replies !   View Related

---

Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.

When i tried the sample on a newly created database it worked fine.

Is there any solution to make the restored database to work for service broker.

Thanks

Prashanth

View Replies !   View Related

---

Broker:Corrupted Message
Hello,

Has anyone seen this in profiler? I have two brokers on different servers with one of them being the initiator. All messages end up sitting in the initiator's transmission queue. Profiler on the target broker's machine displays this for every attempt to send by the initiator:

A corrupt message has been received. The End of Conversation and Error flags may not be set in the first sequenced message. This occorred in the message with Conversation ID '...', Initiator: 1, and Message sequence number: 0

In case it's relevant encryption is disabled and endpoints on both servers use windows authentication.

Any hints are greatly appreciated.

Regards,

Svega

View Replies !   View Related

---

Can't Route To Another LOCAL Broker Instance
I have two databases (A and B )on the same SQL Server instance. Both have SSB enabled and running fine within themselves. All athorizations are at present set to dbo.

Recently I had a requirement to start a dialog and send a message from within data base A to a queue via a service that is in database B.

I tried coding the SSB instance in the BEGIN DIALOG then I set up a route and tried that. On both occoasions I got the following on sys.transmission_queue

"An exception occurred while enqueueing a message in the target queue. Error: 916, State: 3. The server principal "sa" is not able to access the database "B" under the current security context."

Is this sometjhing to do with security lock downs in 2005?

View Replies !   View Related

---

Broker Message Classification Trace
In profiler i see next error:

"The message could not be delivered because it could not be classified. Enable broker message classification trace to see the reason for the failure."

Where and what  should I enable? I enabled all in "Broker event category" of the profiler.

Thanks

View Replies !   View Related

---

Service Broker And .net Windows Service
I am doing some research to see if the Service Broker technology would help my company with our Enterprise application. Here is our scenario: We have a 3 tier system. The first tier needs to contact the second tier asynchronously. Hence, using queues is a good option. However, the process that needs to happen on the second tier is mostly process intensive with little database updates. Is it still worth our time to use Service Broker?

I like the concept of Activation that Service Broker provides. But, from what I am reading most of the documentation describes activation as a way to call another stored proc. I definitely dont' want to do any process intensive work on the SQL server. So here comes my question...

How would I use a windows service to listen to the activation event from the Service Broker. I could have multiple windows services watching the same queue (scalable). Would I have to handle collisions myself? If so, I think I would rather keep it simple, and just use a simple table as my queue.

Thanks for your comments in advance...
Vijay.

View Replies !   View Related

---

Conversation Handle Processed By An Activated Stored Procedure Service Can Not Be Invoked By A CLR Service Instance?
I have a initiator and a target service broker peer.

Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.

Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.

The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.

For this the C# unit test creates a Conversation off of the saved conversation handle:


Service client = new Service("cleintservicename", conn, tran);

Conversation dialog = null;

dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.

View Replies !   View Related

---

How To Use SSMSE To Connect To Instance Service Directly (without Going Through Browser Service)?
I have configured an instance to use static port, say 1435, and I turned off the Browser service.

I would like to know if I can use SSMSE to connect to the instance database directly. The instance is listening at port 1435 and accepting connection requests from other methods.

What I need to put into the "Server name: " field?

Normally, it would be like: <serverIP><InstanceID> with Browser turned on.

Now how do I add the port information to it, I tried

<serverIP>:<portNumber><InstanceID>

<serverIP><InstanceID>:<portNumber>

<serverIP><InstanceID>  <portNumber>

None of them worked.

Any suggestions?

Thanks.

View Replies !   View Related

---

Cross DB Dialog Security Issues. Was: Can't Route To Another LOCAL Broker Instance
Hi Remus,

 

I am experiencing the same problem, and I can't get the easy fix to work. I drop and create the DB's in between tests, so it is not related to having an old certificate in the DB, as in the case of Tilfried.

 

The situation is as follows:

DB1 owned by login1, has a user for login2; this DB is for the initiator

DB2 owned by login2, has a user for login1; this DB hosts the target

Both DB's have TRUSTWORTHY flag set to ON

Error in sys.transmission_queue: 'Error 916, State 3: The server principal "Login1" is not able to access the database "DB2" under the current security context.

 

Going on a limp, I decide to add a remote service binding in DB1, binding the user for Login2 to the target service, even though BOL explicitly states that this is only required for cross-server communications. This does change the situation - I still get an error, but a new message is sys.transmission_queue: "Dialog security is unavailable for this covnersation because there is no certificate bound to the database principal (Id: 5). Either create a certificate for the principal, or specify ENCRYPTION = OFF when beginning the conversation." I already know that the first option works, but I wanted to get the simple solution running. As for the second option, I doublechecked and the initiating procedure DOES already specify ENCRYPTION = OFF in the BEGIN DIALOG CONVERSATION command. My theory is that the remote service binding somehow forces SB to use encryption, but (a) that is not stated in the error message, and (b) if so, then how to get the messages sent over to the target service without using the binding?

 

==> EDIT: Just saw that you confirmed this theory in your last reply to Tlifried. So I am indeed back to having to find out how to get this to work without remote service binding - it should be possible, but how???

 

BTW, SELECT @@VERSION shows that I'm on build 3054, in case it matters.

 

Between all the errors in BOL and less than helpfull error messages produced by SB, I feel like I'm slowly losing my sanity. Please help!

 

Best regards,

 

Hugo Kornelis

View Replies !   View Related

---

SQL Service Broker
Hi to all,           I want to study Sql server Service broker, have some questions1. What is the use of service broker ?2. Where this will use ? (With example)3. How to enable Service broker? Because i have sql server 2005 version but no folder like service broker. 

View Replies !   View Related

---

SQL Service Broker
Hello,

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

Mike

View Replies !   View Related

---

Service Broker
Is SQL Service Broker part of SQL Server Express or is there an add-in that I need?Currently I am not seeing the Service Broker Directory Folder under the database that I created.ThanksDoug

View Replies !   View Related

---

Service Broker Example.
Im having a hard time understanding everything required to create a simple Service Broker example. Can someone please assist? Source code would be ideal, but if not "do this, do that" would even be helpful.

Thanks.

View Replies !   View Related

---

Service Broker
I have tried the following, each runs successfully with no error, but nothing is in the queues, what can be the issue?
CREATE MESSAGE TYPE SentMsgType
VALIDATION = WELL_FORMED_XML;
 
CREATE CONTRACT MQContract
(SentMsgType SENT BY ANY );
 
CREATE QUEUE SentQueue
WITH
STATUS=ON, RETENTION=OFF;    
 
 
CREATE QUEUE ReceivedQueue
WITH
STATUS=ON, RETENTION=OFF;
 
CREATE SERVICE SentService ON QUEUE SentQueue
(MQContract);
 
CREATE SERVICE ReceivedService ON QUEUE ReceivedQueue
(MQContract);
 
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[insertTrigger]
   ON [dbo].[tblBBB] FOR INSERT AS
BEGIN
   SET NOCOUNT ON;
   DECLARE @handle uniqueidentifier
   DECLARE @msgBody nvarchar(500)
  
   select @msgBody = someString from inserted
 
   BEGIN DIALOG CONVERSATION @handle
   FROM SERVICE SentService
   TO SERVICE 'ReceivedService', 'CURRENT DATABASE'
   ON CONTRACT MQContract;
 
   --Sends a message
   SEND ON CONVERSATION @handle
   MESSAGE TYPE SentMsgType
   ('<message>' + @msgBody + '</message>')
   END CONVERSATION @handle WITH CLEANUP;
END
 
 
SELECT * FROM SentQueue
SELECT * FROM ReceivedQueue;
 

View Replies !   View Related

---

Service Broker + .Net 1.1
Hi,
 
Is it possible to develop Service Broker in .Net 1.1 (VS 2003)?  Currently I have a project developed in .Net 1.1 and I want to add a new method utilize the message queue concept (instead of using MSMQ, using Service Broker SQL 2005), although my DB is SQL server 2005.
 
Thanks,

View Replies !   View Related

---

How To Use Service Broker And When
Hi all

 

if any one have any white paper or artical cover this issue kindly i need it

 

thanks , regards

View Replies !   View Related

---

Is SQl Service Broker What I Need??
Hi,

 

I am looking at the Service Broker as a way to notify multiple clients that there has been data changed on a table in the shared database. These clients may or may not be online. When there is a change, the notification should fire off a query to refresh the clients local cache. Is this a situation where Service Broker would help me? Can multiple clients recieve the notification at different times ( some recieve while online, some recieve when they come back online)? Any help on this would be appreciated. It seems from what I read that the messages are pulled off the queue when a notification has taken place. Is this correct? If so, can I set it to behave differently?

 

Thanks,

-paul

View Replies !   View Related

---

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

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

The error i'm getting is

 

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

Error:  28054, Severity 11, State: 1.

 

View Replies !   View Related

---

Service Broker
 

How to create service broker and whic version is supported to create serveice broker.

 

can you plz exlain to create servece broker from the scratch

 

 

View Replies !   View Related

---

Service Broker And NAT
Hi

It will be great to have an update on MS plans to solve the problem of using
Service Broker for remote users who sit behind the NAT.
Any news will be appreciated.

Leonid.

View Replies !   View Related

---

Service Broker
Hello , I am trying to Implement distribution of the Stock Quotes over the LAN(only within the Network) and showing the live changing stock Quotes on the front end (in datagrid) installed at each clients desktop.I am receiving the Stock prices over the TCP / IP from the Stock Exchange. I am recieving atleast 10-15 messages per second over the TCP / IP from the Stock Exchange. Now i need to distribute this feed to Each connected client.

I tried doing it from TCP / IP , but in vein. Can we install the SQL 2005 Database Client Version on every client and use Service broker instaed of Live TCP / IP connections programmatically?

Ideally Can i dump the meesages from Stock Exchange in to each connected client's database locally and each front end application will keep a record of all the incomming messages.i.e Front end have a  notification event , it will referesh the Datagrid in Front end accordingly...

ALL my front end application are made in dot net

Pls suggest if this above workflow will help me

Yugant

 

 

 

 

View Replies !   View Related

---

Service Broker
I am trying to implement service broker. I send a message from my application code to the database to execute a specific stored procedure. How do i return the result set obtained by the execution of the stored procedure to the application.

View Replies !   View Related

---

Service Broker From Behind The NAT
Let's assume the situation: we have Initiator and Target. Target is behind ISP's NAT and can't be published outside. So, when Initiator sends a message to Target, Target will not be able to establish a backward connection and will not send an acknowledge. Initiator will retry and retry...

View Replies !   View Related

---

Sql Dependency And Service Broker.
Hi everyone,
 
                  Can anyone let meknow how do i enable a service broker. I am trying to enable a service broker for an issuetracker application to get change of events in my database. When ever i try enabling it using the ALTER DATABASE [ databse] set Enable_Broker. it takes abt more that 2 hrs or more but doesnt show as enabled.
 Thanks in Advance,
 Pawan Venugopal

View Replies !   View Related

---

Communicate With Service Broker In C#?
So SQLDependencies failed to do what I wanted them to do for my Cache Invalidating, so i'm going to humor another possibility for a half day - Triggers on my database table that communicate messages to my C# inside my ASP.NET App. Any advice on how to tap into a message queue with C#? I'm thinking that my messages could be 1 of about 100 different strings as far as what occurred on the Database Tables

View Replies !   View Related

---

2005: Service Broker?
Hello,I am learning SQL Server and I would like to ask you a question: whatare applications of Service Broker? I would like to know in whatpuproses it can be used in database systems.Thank you very much for you answers/RAM/

View Replies !   View Related

---

SSIS And Service Broker
I am trying to use SSI Sto receive messages from a Service Broker Queue and continue to have difficulty doing so.

I  have come to the point where I need to get the messages that share a conversation with this BatchEnd message type before I get any other messages, as this message type tells me information about how many others there are.

I built this script and ran it in management studio with success and felt it should work for me running as the sql for an OLE DB source.

declare @ch uniqueidentifier;

select @ch = conversation_handle

from dm.[consultant queue]

where message_type_name = 'BatchEnd';

Receive conversation_handle, message_type_name, message_body

from dm.[consultant queue]

where conversation_handle = @ch;

But SSIS constantly complains about it.  I finally profiled SSIS to catch the sql that it is executing.  It is below and this fails with an error about a missing conversation_handle. 


declare @p1 int

exec sp_prepare @p1 output,NULL,N'declare @ch uniqueidentifier;

select @ch = conversation_handle

from dm.[consultant queue]

where message_type_name = ''BatchEnd'';

Receive conversation_handle, message_type_name, message_body

from dm.[consultant queue]

where conversation_handle = @ch;',1

select @p1

go

 

Now I'm not sure which team may own this, but shouldn't this sp_prepare work.  If I change this to use a table variable then the prepare seems to work just fine, but then I never actually get any data back into SSIS.  It is almost like it calls it twice; although, I could not confirm that with profiler.

I'll cross post this in the Service Broker forum too.

Thanks!

View Replies !   View Related

---

Service Broker And SSIS Together ?
Hello,

I want to use Service Broker and SSIS in a integration project where the queue contains Xml data and the SSIS package processes and sends it to a destination. What is a good way to do this? Is it possible for the queue to execute the package with the data as a parameter, or what is good practise when combining this techniques? I am not familiar with Service Broker so i don´t know what you can do with it when data are loaded in it.

Would be grateful for some hints to put me in a good direction..

/Erik B

View Replies !   View Related

---

Using Service Broker To Synchronize Non SQL DB
Hi everyone,

I would like to know if using Service Broker to synchronize another non MS DB with this technologie is suitable?

My idea is to place in messages the modification of any table and then have a windows service or web service to read this Service Broker Messages, and then update another database (Intersystems Caché).

Any other ideas are welcome!

Best regards,

Sam

View Replies !   View Related

---

Help Implementing Service Broker
 

I am working on a project where I want to implement service broker.  A lot of the examples that I have seen are all based on T-SQL. Here are my questions.
 
1. I have a web page that allows a user to type in an order.  How do I put a message on the queue using a .NET assembly?  Is there some sort of API set? Do I just pass the message to a stored procedure that puts the message on a queue?
 
2. Are there any samples that illustrate how to read messages off a queue using a console application or windows service?  Is it possible to read the messages off of the queue with a console application or do you have to subscribe to the queue activation event and write a service program that runs under the control of service broker?  All of the data that I need to fill the order has to come from a DB2 database on the mainframe.  Therefore, I am stuck with creating an external application that processes the messages.
 
3.  In order to use Service Broker, do I have to install Notification Services as well? 

View Replies !   View Related

---

Looking For Info On Using Service Broker With .net
Hi!!
 
I'm new to service brooker and I'm looking for a tutorial or an example about a .net application sending messages to a queue in Service Brooker, and other .net application consuming the messages. Can you giveme some links or somethig about this?
 
Until now I have found only info about using this on SQL Server, but not interacting with an external application made in dot.net.
 
Thanks in advance
 
Andres Garcia

View Replies !   View Related

---

Service Broker &&amp; 2 DBs, Using Set Trustworthy Off.
I'm trying to create an audit database containing a log table, which will be filled with some info, according to the case. These info will be send from the db being audited and then saved in the audit db using service broker. Both databases are on the same instance.
 
Is it possible to have both databases set with trustworthy off? How to do that?
 
I've tried this:
 
1 - Create a certificate;
2 - Create a login from the certificate;
3 - Create an user in each database from that login and

4 - Alter the queue from db being audited, including the "execute as"
 
But I get this error:
Cannot execute as the database principal because the principal "usrServiceBroker" does not exist, this type of principal cannot be impersonated, or you do not have permission.
 
5 - Impersonate these users, with no success.
6 - use execute as 'dbo' in the procedures related to the queues.
 
Any clues?
 

View Replies !   View Related

---

How To Run The Service Broker Samples
I am trying to run the Readme_HelloWorld sample on the SQL server 2005 Books online. But don't know where I can get the setup.sql, SendMessage.sql, ReceiveMessage.sql scripts.

I followed two examples on the blog, but they did not work at all. Here are the scripts:

/* example 1 */
create database TestSB
go
use TestSB
go
create queue TxQ
create queue RxQ
create message type Msg
create contract MsgContract(Msg SENT BY ANY)
create service TxSve on Queue TxQ
Create service RxSve on Queue RxQ
declare @h uniqueidentifier
begin dialog conversation @h
 from service TxSve to Service 'RxSve'
 on contract MsgContract;
 send on conversation @h Message type Msg ('<hello>World</hello>')
end conversation @h with CLEANUP;

select *
from RxQ

/* example 2 */
use testsb
go

create message type HelloMessage
validation = NONE
go

/** 2. create contract **/
create contract HelloContract
(HelloMessage SENT by initiator)

/** 3. create queue **/
create queue SenderQueue
create queue ReceiverQueue

/** 4. create service **/
create service sender
on queue SenderQueue

create service receiver
on queue ReceiverQueue(HelloContract)

declare @handle uniqueidentifier
declare @message nvarchar(100)

begin
 begin transaction;
  begin dialog conversation @handle
    from service sender
    to service 'receiver'
    on contract HelloContract
    set @message = N'Hello, World';
    send on conversation @handle
   message type HelloMessage(@message)
  end conversation @handle
 commit transaction
end
go

Receive convert(nvarchar(max), message_body) as message1
from ReceiverQueue

select  convert(nvarchar(max), message_body) as message1
from ReceiverQueue

select *
from SenderQueue

select *
from Receiverqueue

Any assistance or WORKING samples are greatly appreciated.

 

View Replies !   View Related

---

Disable Service Broker
Using SQL 2005 SP2 I enabled service broker, added 2 new roles (sender and receiver) with rights below:
 
--for sender

GRANT CREATE PROCEDURE to [sender]

GRANT CREATE QUEUE to [sender]

GRANT CREATE SERVICE to [sender]

GRANT REFERENCES on CONTRACT::[http://schemas.Microsoft.com/SQL/Notifications/PostQueryNotification] to [sender]

GRANT VIEW DEFINITION TO [sender]
 
-- for receiver

GRANT SELECT to [receiver]

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [receiver]

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [receiver]

GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [receiver]
 
I added a new Schema, a new SQL user as well and altered that SQL user WITH DEFAULT_SCHEMA so I can use it for my purpose and the process is all working well however...
The problem I have is to rollback and disable(drop) all references because after I remove:
    - all the members from the role
    - drop the two roles
    - drop all procedures, services (DROP SERVICE ...) and queues (DROP QUEUE....) owned by the schema
I still can't drop the schema because sys.objects still has entries where type = 'SQ' but that they were supposedly removed by the commands I executed before
 
Did anyone else has seen this before?
 

View Replies !   View Related

---

Is Service Broker Solution For This ?
 




Hi guys,

I have an application for which I need to send emails based on results of query.
list of subscribers will change dynamically.
I only need notify via emails .
Is notification services solution for this ?
Will pure service broker based solution without notification service work ?
thinking of using atleast service broker since I want to enforce guranteed delivery.
what do you think ?
Please let me know
 



venkata

View Replies !   View Related

---