Conversion of characters from XML to target collation impossible

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew Robinson - 10 Jan 2007 07:19 GMT

I have successfully setup Login Auditing using Service Broker, but have an
application that tries to login using invalid characters (猀愀). 2 empty
squares.

I have been unsuccessful in trapping this as an error in my Stored Proc
which is activated on my Queue, and as a result and as expected, it tries 5
times to unsuccessfully remove the message from the queue with the error:

The activated proc [dbo].[usp_Logging_20070108] running on queue
SQLAuditing.dbo.AuditLogonQueue output the following: 'Conversion of one or
more characters from XML to target collation impossible'

and subsequently disables the queue.

I am using the BOL trackmessage stored proc for error trapping, but this
needs @@error <>0 which it does not seem to be incremented.

I need help with my error handling. If you need to see the sp, let me know.

Reply to this Message

Andrew Robinson - 10 Jan 2007 08:41 GMT

I thought I would add the sp anyway...

CREATE PROCEDURE [dbo].[usp_Audit_Logins]
AS
SET NOCOUNT ON
DECLARE @message_body XML,
@message_type_name NVARCHAR(256),
@dialog UNIQUEIDENTIFIER;

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION
        SAVE TRANSACTION UndoReceive ;
-- Receive the next available message

WAITFOR (
        RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM AuditLogonQueue
            ), TIMEOUT 2000

--Rollback and exit if no messages were found
    IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

--End conversation of end dialog message
IF (@message_type_name =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT 'End Dialog received for dialog # ' + cast(@dialog as
nvarchar(40))
END CONVERSATION @dialog
        ROLLBACK TRANSACTION
END
ELSE
BEGIN
    DECLARE @DatabaseId VARCHAR(50)

    SET @DatabaseId =
CAST(@message_body.query('/EVENT_INSTANCE/DatabaseID/text()') AS VARCHAR(50))

    INSERT INTO AuditLogins(EventTime, EventType, LoginName, HostName,
NTUserName, NTDomainName,[Database],XMLEvent,
EventMonth, EventYear)
    VALUES
    (
        CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),
        CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS
VARCHAR(100)),
        CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS
VARCHAR(100)),
        CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS
VARCHAR(100)),
        CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS
VARCHAR(100)),
        CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS
VARCHAR(100)),
        CASE ISNULL(@DatabaseId,'Unknown')
        WHEN 'Unknown' THEN 'Unknown'
        ELSE DB_NAME(@DatabaseId)
        END,
        @message_body,

month(CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME)),

year(CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME))
    )

    if @@error <> 0
BEGIN
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @dialog ;
END ;
        --END
ELSE
BEGIN
EXEC dbo.ClearMessageTracking
@dialog ;
END
    END
    COMMIT TRANSACTION
END

Reply to this Message

Roger Wolter[MSFT] - 10 Jan 2007 16:53 GMT

The key here is to rollback and retry only if there's some hope that trying
again will result in success. Your logic does a rollback on any error so
anything that is caused by the input data will always disable the queue.
Things like deadlocks, disk errors, out-of-space conditions are worth
retrying but any kind of permanent error is not. Try-catch also makes error
handling easier to write. Here's the message processing part of a sample I
use:

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

-- Receive the next available message

WAITFOR (

RECEIVE top(1) -- just handle one message at a time

@MessageType=message_type_name, --the type of message received

@MessageBody=message_body, -- the message contents

@ConversationHandle = conversation_handle -- the identifier of the dialog
this message was received on

FROM [ISBNLookupTargetQueue]

), TIMEOUT 1000 -- if the queue is empty for five seconds, give up and go
away

-- This timeout should be tuned based on how expensive it is to startup up
the stored procedure

-- If we didn't get anything, the queue is empty so bail out

if (@@ROWCOUNT = 0)

BEGIN

ROLLBACK TRANSACTION

BREAK

END

SAVE TRANSACTION AFTERREAD

IF (@MessageType =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

-- When we receive an End Dialog, we need to end also.

END CONVERSATION @ConversationHandle

END

ELSE

IF (@MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')

BEGIN

-- Handle the error here. Maybe log it or send a notification to someone.

-- If there are still messages in the queue you may or may not want to
process them.

-- If work has already been done and committed as part of this dialog, you

-- may have to issue compensating transactions to undo it.

-- At this point the other end can't receive or send messages so once the
error is

-- handled the only thing to do is end our end of the dialog also.

END CONVERSATION @ConversationHandle

END

ELSE

IF (@MessageType = 'ISBNLookupRequest')

BEGIN

BEGIN TRY

Print 'In try'

insert into tst values (10)

END CONVERSATION @conversationHandle

END TRY

BEGIN CATCH

Print 'In Catch'

-- Check for transient errors. The numbers depend on what you're doing

-- I included some of the more obvious ones here

IF error_number() IN (580,596,601,701,708,802,844,845,

846, 847, 921,922, 923, 1121, 2502, 3967,

3984, 3985, 1205, 2755, 3635, 3928, 17884,

1203, 1221, 1807, 6292, 20041 )

BEGIN

ROLLBACK TRANSACTION

CONTINUE

END

ELSE BEGIN

ROLLBACK TRANSACTION AFTERREAD

INSERT INTO err VALUES (getdate(), error_number(), error_message())

END

END CATCH

end -- process message

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

>I thought I would add the sp anyway...
>
[quoted text clipped - 87 lines]
> COMMIT TRANSACTION
> END

Reply to this Message

Andrew Robinson - 11 Jan 2007 09:54 GMT

Thank you very much for your help Roger.

May I ask one more question, when the queue does eventually get disabled,
what are the various ways in which one can re-activate it, apart from
dropping and recreating all the various queues, routes, etc?

Reply to this Message

Roger Wolter[MSFT] - 11 Jan 2007 15:38 GMT

ALTER QUEUE xxx WITH STATUS = ON

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

> Thank you very much for your help Roger.
>
> May I ask one more question, when the queue does eventually get disabled,
> what are the various ways in which one can re-activate it, apart from
> dropping and recreating all the various queues, routes, etc?

Reply to this Message

Andrew Robinson - 11 Jan 2007 15:51 GMT

Thanks again Roger.

Reply to this Message