April 13, 2012 at 1:05 pm
Hi folks
can anyone help me to resolve this error please?
Error: Msg 257, Level 16, State 3, Procedure procReceiveDeadLock_Graph, Line 28
Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
Script:
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[procReceiveDeadLock_Graph] Script Date: 03/13/2012 10:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procReceiveDeadLock_Graph]
AS
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
DECLARE @message_type_name nvarchar(128)
DECLARE @deadlock_graph xml
DECLARE @event_datetime datetime
DECLARE @deadlock_id int
DECLARE @DBname sysname
BEGIN TRY
BEGIN TRAN
WAITFOR(
RECEIVE TOP(1) @conversation_handle = conversation_handle
, @message_body = CAST(message_body AS xml)
, @message_type_name = message_type_name
FROM dbo.queDeadLock_Graph)
, TIMEOUT 10000 -- Line added 2010-07-24;
-- http://resquel.com/ssb/2010/07/24/ServiceBrokerCanMakeYourTransactionLogBig.aspx
-- Validate message
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' AND
@message_body.exist('(/EVENT_INSTANCE/TextData/deadlock-list)') = 1)
BEGIN
-- Extract the info from the message
SELECT @deadlock_graph = @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)')
, @event_datetime = @message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
, @DBname = DB_NAME(@message_body.value('(//*/process/@currentdb)[1]', 'varchar(10)'))
-- Put the info in the table
INSERT dbo.tblDeadLock_Log (DeadLock_Detected, DeadLock_Graph)
VALUES (@event_datetime, @deadlock_graph)
SELECT @deadlock_id = SCOPE_IDENTITY()
-- Send deadlock alert mail.
-- Requires configured database mail, will log an error if not (or anything else goes wrong).
BEGIN TRY
DECLARE @subj nvarchar(255), @bdy nvarchar(max), @qry nvarchar(max), @attfn nvarchar(255)
SELECT @subj = 'A deadlock occurred on ' + @@SERVERNAME + ', on the ' + QUOTENAME(COALESCE(@DBname, 'unknown')) + ' database'
, @bdy = 'A deadlock occurred at ' + CONVERT(varchar(50),@event_datetime, 120) + ' on SQL Server: ' + @@SERVERNAME + '. See attached xdl-file for deadlock details.'
, @qry = 'SET NOCOUNT ON; SELECT deadlock_graph FROM dbo.tblDeadLock_Log WITH (READUNCOMMITTED) WHERE DeadLock_ID = ' + CAST(@deadlock_id AS varchar(10)) -- Locking hint is to prevent this dynamic query to be blocked by the lock held by the insert. The dynamic SQL will not come from inside this transaction.
, @attfn = @@SERVERNAME + '_' + CAST(@deadlock_id AS varchar(10)) + '.xdl'
EXEC sp_send_dbmail @profile_name = DBA
, @recipients = 'ashah@mkmg.com'
, @subject = @subj
, @body = @bdy
, @query = @qry
, @attach_query_result_as_file = 1
, @query_attachment_filename = @attfn -- http://support.microsoft.com/kb/924345
, @query_result_header = 0
, @query_result_width = 32767
, @query_no_truncate = 1
END TRY
BEGIN CATCH
UPDATE dbo.tblDeadLock_Log
SET NoMailReason = ERROR_MESSAGE()
WHERE DeadLock_ID = @deadlock_id
END CATCH
END
ELSE -- Not an event notification with deadlock-list
END CONVERSATION @conversation_handle
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Link to see script: http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/5d97b5cb-8bb9-420a-ab29-6c420a6ed53d
Thank you
April 13, 2012 at 1:24 pm
Really? You can't figure this one out? The answer my friend is blowing in the error message.
No really, the error message is actually telling you what is wrong and how to fix it.
April 13, 2012 at 1:30 pm
To start, this is line 28 (the line that the error message indicates)
INSERT dbo.tblDeadLock_Log (DeadLock_Detected, DeadLock_Graph)
VALUES (@event_datetime, @deadlock_graph)
So, check the data types of the columns in the table, check the data types of the variables and fix whichever is incorrect.
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
April 13, 2012 at 1:31 pm
the problem seems to be in the INSERT statement:
INSERT dbo.tblDeadLock_Log (DeadLock_Detected, DeadLock_Graph)
VALUES (@event_datetime, @deadlock_graph)
SELECT @deadlock_id = SCOPE_IDENTITY()
what's the definition of DeadLock_Graph column of this table? If that's NVARCHAR then do an explicit CAST of variable @deadlock_graph, or if possible create the table with DeadLock_Graph being type XML
April 13, 2012 at 1:35 pm
Table Defination
USE msdb
GO
CREATE TABLE dbo.tblDeadLock_Log (
DeadLock_ID int IDENTITY(1,1) CONSTRAINT pk_tblDeadLock_Log PRIMARY KEY
, DeadLock_Detected datetime
, DeadLock_Graph nvarchar(max)
, NoMailReason nvarchar(2048))
GO
April 13, 2012 at 1:39 pm
Yes chris
The problem is near INSERT statement.
good catch...I saw the error recommending to use convert function but where to apply that?
April 13, 2012 at 1:42 pm
You can performance tune a proc but can't trouble shoot this error? Pretend it is an interview question and show us what you would do to solve it.
Really, I would expect a junior level dba with about 1 year experience to solve this without much help.
April 13, 2012 at 1:50 pm
As I said...
GilaMonster (4/13/2012)
So, check the data types of the columns in the table, check the data types of the variables and fix whichever is incorrect.
The column is nvarchar, the varible is XML and the error is saying that an implicit (automatic) conversion between XML and nvarchar is not possible. Given that, what do you think needs doing?
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
April 13, 2012 at 2:31 pm
Lynn Pettis (4/13/2012)
You can performance tune a proc but can't trouble shoot this error? Pretend it is an interview question and show us what you would do to solve it.Really, I would expect a junior level dba with about 1 year experience to solve this without much help.
i have been working with sql as a prime component of my job for about 3 months and even i know what i would need to do to solve this. the error even tells you what you need to do.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply