dbmail and For XML error

Question

dbmail and For XML error

Red8Rain

Hall of Fame

Points: 3312
More actions
September 8, 2008 at 7:30 pm

#131170

anyone see this type of error before when SQL 2005 tries to send you an email? This happen once or twice a week but google search can't seem to find anything worthwhile to me.
error:
FOR XML could not serialize the data for node 'td' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive. [SQLSTATE 42000] (Error 6841). The step failed.
my code basically look like this:
SET @tableHTML =
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
N' ' +
CAST ( ( SELECT Distinct td = dbname, '',
td = spid, '',
td = duration, '',
td = program_name, '',
td = input_buffer, '',
td = blocking_process, '',
td = program_name1, '',
td = input_buffer1, ''
FROM @info WHERE blocking_process <> 'NO BLOCK'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' '
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I have not had this error in DBMail, but I have had errors like this in other XML applications and the error is clear that one of your columns being returned in your query has a character or characters that are invalid within XML and needs to be converted.

What is the query the loads @info? If I knew that I might be able to offer a conversion and what column is causing the issue

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Red8Rain Hall of Fame Points: 3312 More actions · Answer 2

hey jack,

here's table structure for @info:

DECLARE @info table (DBNAME SYSNAME, SPID INT, Duration INT, PROGRAM_NAME SYSNAME, INPUT_BUFFER NVARCHAR(MAX), BLOCKED VARCHAR(2), BLOCKING_PROCESS VARCHAR(10), PROGRAM_NAME1 SYSNAME, INPUT_BUFFER1 NVARCHAR(MAX))

and the select into statement:

INSERT INTO @info
SELECT db_name(b.dbid)
, A.SPID
, datediff(mi, b.last_batch, getdate())
, B.PROGRAM_NAME
, A.INPUT_BUFFER
, CASE B.BLOCKED WHEN 0 THEN 'N' ELSE 'Y' END
, CASE WHEN B.BLOCKED <>0 THEN CONVERT(VARCHAR(10),B.BLOCKED) ELSE 'NO BLOCK' END,'',''
FROM @handler0 A
, sys.sysprocesses B
WHERE A.SPID = B.SPID

pls keep in mind that this doesn't happen all the time, just sometimes.

-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

dsslim SSC Enthusiast Points: 139 More actions · Answer 3

Hi,

I have the same error. Then I realise that the source ddl must match your target. For e.g., your @info table ddl must match sys.processes ddl.

Hope that helps !:-)

rjd-580571 Valued Member Points: 55 More actions · Answer 4

good day for yours.

here i have had this problems.

resolved without "Type"

SET @tableHTML =

N' ' +

CAST ( ( SELECT Distinct td = dbname, '',

td = spid, '',

td = duration, '',

td = program_name, '',

td = input_buffer, '',

td = blocking_process, '',

td = program_name1, '',

td = input_buffer1, ''

FROM @info WHERE blocking_process <> 'NO BLOCK'

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N' '

substitua por :