Can anyone help a newbie?

Question

Hi having recently passed 70-433 I wanted to find out more about Service Broker. Im working though the tutorials found here: http://msdn.microsoft.com/en-us/library/bb839489(v=sql.105).aspx and have single database and across databases on the same instance working perfectly. I am now trying and failing to implement across the databases on the same machine - my laptop

The scripts here show you what I have run so far:   http://sdrv.ms/KQ7z2Y

scripts 1 and 3 were run on localhost, 2 and 4 were run on a named instance on the same machine

After running the final script in #4 I consulted sys.transmission_queue and found the error: DNS lookup failed with error: '11004(failed to retrieve text for this error. Reason: 15100)'.

How can I resolve this? Im really hoping to find anyone willing to help me with this specific error and if they are feeling generous hand hold me through the process.

Hope to hear from someone/anyone soon.

Answer 1

Hi,

I looked at the 4 scripts and they seem to be correct. What version of SQL Server are you using ? It is SQL Express ?

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

Answer 2

localhost: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

named instance: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Answer 3

skydrive is down...

can you post equivalent of:

SET @Cmd = N'USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''//InstDB/2InstSample/InitiatorService'',
     ADDRESS = N''TCP://MyInitiatorComputer:4022'';';

EXEC (@Cmd);

Answer 4

DECLARE @Cmd NVARCHAR(4000);

SET @Cmd = N'USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
       N''//TgtDB/2InstSample/TargetService'',
     ADDRESS = N''TCP://MyTargetComputer:4022'';';

EXEC (@Cmd);

SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''//InstDB/2InstSample/InitiatorService'',
     ADDRESS = N''LOCAL''';

EXEC (@Cmd);
GO
CREATE REMOTE SERVICE BINDING TargetBinding
      TO SERVICE
         N'//TgtDB/2InstSample/TargetService'
      WITH USER = TargetUser;

GO

Answer 5

Replace "MyTargetComputer" with name of your laptop, localhost or 127.0.0.1

do it for every line with:  ADDRESS = N''TCP://MyTargetComputer...

Answer 6

On more, because you will have both instances on the same hardware, you need to use different ports for each instance, i.e. replace:

MyTargetComputer:4022 -> localhost:4022

MyInitiatorComputer:4022 -> localhost:4023

Answer 7

Replace "MyTargetComputer" with name of your laptop, localhost or 127.0.0.1

do it for every line with:  ADDRESS = N''TCP://MyTargetComputer...

Excellent, cant believe I missed that when setting it up, now I get a new error

The Service Broker protocol transport cannot listen on port 4022 because it is in use by another process.

Answer 8

On more, because you will have both instances on the same hardware, you need to use different ports for each instance, i.e. replace:

MyTargetComputer:4022 -> localhost:4022

MyInitiatorComputer:4022 -> localhost:4023

So just to clarify I have changed

CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

to

CREATE ENDPOINT InstInitiatorEndpoint

STATE = STARTED
AS TCP ( LISTENER_PORT = 4023 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

and

SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''//InstDB/2InstSample/InitiatorService'',
     ADDRESS = N''LOCAL''';

to

SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
       N''//InstDB/2InstSample/InitiatorService'',
     ADDRESS = N''TCP://localhost:4023''';

This time the message is still in sys.transmission_queue however the status is blank

there are a few lines in the error log

The Service Broker protocol transport is disabled or not configured.

Server is listening on [ 'any' <ipv6> 4023].

Server is listening on [ 'any' <ipv4> 4023].

The Service Broker protocol transport is now listening for connections.

Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.

Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.

Answer 9

Update, up to a point it worked...

After running the receive scripts on the target and trying to send the reply I receive the following error

An error occurred while receiving data: '64(failed to retrieve text for this error. Reason: 15105)'.

Ive added script 5 and 6 to the skydrive so you can see what I am running, further help appreciated, script 5 is where the error occurs

Answer 10

updated skydrive with trace files

I see this error in the trace: Could not forward the message because forwarding is disabled in this SQL Server instance.

Answer 11

Everything now working!  :)

The updated files are sitting in my skydrive and I'll try and write this experience up later today

*edit* Blog post: http://jl45sql.wordpress.com/2012/05/27/service-broker-completing-a-conversation-between-instances/