June 29, 2010 at 10:34 pm
I'm totally stumped on this problem. I have a stored procedure. Running the contents of the stored procedure works fine however when I execute the stored procedure it doesn't work.
The contents of the stored procedure sends a Service Broker message to its target. Now this works when I highlight the contents of this SP and run it manually.
I've pasted the code below and have deliberately renamed some of my variable names for privacy concerns (might have forgotten to rename all consistently) but be assured that the contents the SQL have been parsed successfully.
Any help would be greatly appreciated!
ALTER PROCEDURE [dbo].[Send]
@ServiceName NVARCHAR(MAX)
AS
BEGIN
--BEGIN TRANSACTION;
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @MessageBody NVARCHAR(MAX)
DECLARE @fMinBet FLOAT
DECLARE @nNumPlayers SMALLINT
DECLARE @OptimalGameSpeed SMALLINT
-- Set up Cursor to iterate through the Optimal Game Speeds
DECLARE OptimalGameSpeedCursor CURSOR FOR
SELECT * FROM OptimalGameSpeeds
OPEN OptimalGameSpeedCursor
FETCH NEXT FROM OptimalGameSpeedCursor INTO @fMinBet, @nNumPlayers, @OptimalGameSpeed
--DECLARE @ServiceName NVARCHAR(MAX)
SET @ServiceName = 'GamesService'
-- For each record in the Optimal Game Speeds table iterate through it and send it to the Nexus
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN DIALOG @InitDlgHandle
FROM SERVICE InitiatorService
TO SERVICE @ServiceName
ON CONTRACT [Contract]
WITH ENCRYPTION = OFF;
SET @MessageBody = CAST(@fMinBet AS NVARCHAR) + ',' + CAST(@nNumPlayers AS NVARCHAR) + ',' + CAST(@OptimalGameSpeed AS NVARCHAR);
SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [Message] (@MessageBody);
FETCH NEXT FROM OptimalGameSpeedCursor INTO @fMinBet, @nNumPlayers, @OptimalGameSpeed
END
CLOSE OptimalGameSpeedCursor
DEALLOCATE OptimalGameSpeedCursor
--COMMIT TRANSACTION;
END
June 29, 2010 at 11:22 pm
Executing the Stored Procedure works now. Don't know what it was but I'm guessing some bad parameter or something stupid along those lines.
I'm trying to find out how to delete this post but can't seem to find the button. Sorry for this.
June 30, 2010 at 1:09 am
n00b (6/29/2010)
I'm trying to find out how to delete this post but can't seem to find the button. Sorry for this.
Threads can't be deleted. Don't worry about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply