February 14, 2017 at 10:29 am
I'd like to populate the queue data as nvarchar and not XML but i don't seem to be able to :
ALTER TRIGGER [dbo].[PlacementInserted] ON [dbo].[cir_Placement] FORINSERT
AS
BEGIN
BEGIN TRANSACTION;
DECLARE @ch UNIQUEIDENTIFIER
DECLARE @messageBody NVARCHAR(MAX);
BEGIN DIALOG CONVERSATION @ch
FROM SERVICE [InitiatorService]
TO SERVICE 'TargetService'
ON CONTRACT [HelloWorldContract]
WITH ENCRYPTION = OFF;
-- This line of code works:
SET @messageBody = (SELECT ' 1 ', ID, PlcType FROM INSERTED FOR XML AUTO);
This line does not:
SET @messageBody = (SELECT ' 1 ', ID, PlcType FROM INSERTED );
-- Send the message to the TargetService
;SEND ON CONVERSATION @ch
MESSAGE TYPE [RequestMessage] (@messageBody);
COMMIT;
END
February 14, 2017 at 7:35 pm
FOR XML turns an entire resultset (columns and rows) into a single XML fragment, which can be assigned to an XML variable.
The second line does not return a single varchar value; it returns a resultset with three distinct columns of different datatypes and zero or more rows. If you want to add all those values to a single nvarchar value, you need to turn the entire output into a single nvarchar value by concatenating the columns together (and then string-parsing your way out of it later). Something like:SELECT @MessageBody = N'1 ,' + CONVERT(nvarchar(max), ID) + N', ''' + convert(nvarchar(max), PlcType) + N'''' FROM INSERTED
-Eddie
Eddie Wuerch
MCM: SQL
February 15, 2017 at 11:17 am
Thank you!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply