November 6, 2008 at 7:34 pm
I am prototype a communication concept with SQL Server 2005 using Service Broker. I want to sent a message from 1 database to another where the sender forms a XML string and the reciever execs a procedure to parse the XML string.
I believe that most of the pieces are in place I create a Queue, service, message and contract on the sender side. The stored procedure executes and builds the XML string. Within a transaction I have the begin dialog conversion .. and send on conversation statements. I believe the recieving logic is correct though the message has not made it that far.
When I execute the stored procedure (the sender piece), the procedure executes without error. However, when viewing sys.transmission_queue there is an error that I do not know how to correct. The message is:
Service Broker received an error message on this conversation.
Service Broker will not transmit the message; it will be held until
the application ends the conversation.
I attempted to add the 'end conversation' statement to the sender logic but that did not correct the problem.
Any advice would be appreciated.
November 6, 2008 at 9:46 pm
I am not sure what is causing your problem, however, you can read my Code Camp presentation on typical Service Broker problems, either at my blog (see my signature) or here: http://www.movingsql.com/dnn/LinkClick.aspx?fileticket=c7EJbXre%2fLg%3d&tabid=125&mid=461
Hopefully it can help you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 9:34 am
Your presentation and example code are helpful but my code is still not working. On the database where the dialog is started, the stored procedure containing the logic to start the conversation executes without error. The queue is empty upon completed and the sys.transmission_queue is empty. However, it appears that the message never reaches the receiver. The receiver should exec a procedure to parse the XML string and write the info to a table. The receiver queue is empty, the receiver sys.transmission_queue is empty, the table where the data should be written is empty and the server log has no errors. I am not sure if the message never makes it to the reciever, if the receiver stored procedure is not executing for some unknown reason, or other errors.
Any advice on how to trace track down this error?
November 7, 2008 at 10:17 am
What about the queue associated with the conversation's "FROM SERVICE"? Is there anything in it?
Note also, that messages associated with closed conversations in error, are not kept forever. Before you check these locations, you should be sure to initiate a fresh test.
If you still have no messages, then that you post scripts for the SEND procedure and the definitions of the Queues and Services.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 1:14 pm
Maybe I am just missing something though I think I have configured correctly. I have attached 2 files the "sendSample" piece is the database that starts the conversation, the "receiverSample" piece is the database recievces the message and closes the dialog. Is there something I am missing?
November 7, 2008 at 3:50 pm
OK, so is anything showing up in [RecordSalesQueue] on [actiondb] (the Initiator Queue)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 3:58 pm
if I use the query
Select *,cast(message_body as xml) from RecordSalesQueue
The error message is:
This makes no sense since a select from sys.service_contracts shows that the RecordSalesContract service contract does exist.
This is very confusing.
November 7, 2008 at 4:13 pm
We cannot see the error message. If it is in XML then either post it as a txt attachment or just cut and paste out the text message portion (i.e., no xml elements).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 4:16 pm
Sorry about that.
The error code is: -8425
The error text is:
The service contract 'RecordSalesContract' is not found.
November 7, 2008 at 4:25 pm
mcginn (11/7/2008)
Sorry about that.The error code is: -8425
The error text is:
The service contract 'RecordSalesContract' is not found.
Right, I was just getting ready to say that I had noticed that when you are Creating your Services, you are not adding any Contracts to them. Although this is valid syntax, Services created this way can only be used as "Initiator Services" used for error replies (i.e., the FROM SERVICE of your BEGIN DIALOG statement). This is OK for [RecordSalesService], but will not work for 'SalesService' (your TO SERVICE) since you want to SEND to it.
By the way, the fact that you have to look in the Intitiator Reply Q for some of the errors is tip #4 ("Dude, Where's My Error?") in my presentation. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 4:30 pm
I dropped the contract and changed initiator to INITIATOR and added a END CONVERSATION after the send and now there are no errors on the sender side. But, there is nothing showing up on the receiver side. I am not sure where the message is being sent.
November 7, 2008 at 4:41 pm
Did you check all three error message locations?
1) sys.transmission_queue (source and target)
2) Initiator's Reply Queue
3) SQL Server Error Logs
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 7, 2008 at 4:44 pm
As I recall, the next tip in my presentation is #5: "Master Key is NOT Optional", and I did not see you creating master keys for your databases, so you might want to check that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 11:05 am
I realized that I had not created the master key on the recieving database. I went back and started over dropping both databases and recreating everything this time including the master key on both databases.
This time when I executed the procedure on the sender side the error in the server log was from the activation procedure indicating the conversation handle is missing (error 8418). I understood this to be caused by my END CONVERSATION statement from being executed after the recieve without checking to see if @@rowcount was greater then 0. I added this check and rebuild the procedure.
Now both queues are empty, the are no errors in the log, but the table I am writing to is also empty.
I can not determine if the activation procedure is executing improperly or if it is not executing at all. I believe that I have checked all noted locations to find a trace, but I can not determine where this process is breaking down.
November 10, 2008 at 4:51 pm
mcginn (11/10/2008)
This time when I executed the procedure on the sender side the error in the server log was from the activation procedure indicating ...
This is actually very good, because it means that you almost have this thing beat.
Now both queues are empty, the are no errors in the log, but the table I am writing to is also empty.
I can not determine if the activation procedure is executing improperly or if it is not executing at all. I believe that I have checked all noted locations to find a trace, but I can not determine where this process is breaking down.
In your receiver code you have an IF block right after the RECEIVE:
IF(@Handle IS NOT NULL AND @Message IS NOT NULL)
BEGIN
SELECT @SaleDate = CAST(CAST(@Message.query('/Params/SaleDate/text()') AS NVARCHAR(MAX)) AS DATETIME)
SELECT @SaleAmount = CAST(CAST(@Message.query('/Params/SaleAmount/text()') AS NVARCHAR(MAX)) AS MONEY)
SELECT @ItemsSold = CAST(CAST(@Message.query('/Params/ItemsSold/text()') AS NVARCHAR(MAX)) AS INT)
INSERT INTO Sales(SaleDate ,SaleAmount ,ItemsSold )
VALUES(@SaleDate,@SaleAmount,@ItemsSold);
END
I suggest that you add an ELSE block here and just use RAISERROR..WITH LOG to log a message. That way you can trace it to see if it is falling through the cracks here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply