SQL Server 2005 / XML Stored Proc - Unicode to ascii? (Exception 0xc00ce508)

Question

Hello!

I have an MSSQL2005 stored procedure here, which is supposed to take an XML message as input, and store it's content into a table. The table fields are varchars, because our delphi backend application could not handle unicode. Now, the messages that come in, are encoded ISO-8859-1. All is fine until characters over the > 128 standard set are included (in this case, ÄÖäö, which are an integral part of finnish). This causes the DB server to raise exception 0xc00ce508. The database's default, as well as the table's and field's, collation is set to latin1, which should be the same as ISO-8859-1.

The XML message is parsed using the XML subsystem, like so:

ALTER PROCEDURE [dbo].[parse] @XmlIn NVARCHAR(1000) AS
SET NOCOUNT ON
DECLARE @XmlDocumentHandle INT
DECLARE @XmlDocument VARCHAR(1000)
BEGIN
SET @XmlDocument = @XmlIn
EXECUTE sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
BEGIN TRANSACTION
//the xml message's fields are looped through here, and rows added or modified in two tables accordingly
// like ...
DECLARE TempCursor CURSOR FOR
SELECT AM_WORK_ID,CUSTNO,STYPE,REFE,VIN_NUMBER,REG_NO,VEHICLE_CONNO,READY_FOR_INVOICE,IS_SP,SMANID,INVOICENO,SUB_STATUS,TOTAL,TOTAL0,VAT,WRKORDNO
FROM OPENXML (@XmlDocumentHandle, '/ORDER_NEW_CP_REQ/ORDER_NEW_CUSTOMER_REQ',8)
WITH (AM_WORK_ID int '@EXIDNO',CUSTNO int '@CUSTNO',STYPE VARCHAR(1) '@STYPE',REFE VARCHAR(50) '@REFE',VIN_NUMBER VARCHAR(30) '@VEHICLE_VINNO',
REG_NO VARCHAR(20) '@VEHICLE_LICNO',VEHICLE_CONNO VARCHAR(30) '@VEHICLE_CONNO',READY_FOR_INVOICE INT '@READY_FOR_INVOICE',IS_SP INT '@IS_SP',
SMANID INT '@SMANID',INVOICENO INT '@INVOICENO',SUB_STATUS VARCHAR(1) '@SUB_STATUS',TOTAL NUMERIC(12,2) '@TOTAL',TOTAL0 NUMERIC(12,2) '@TOTAL0',VAT NUMERIC(12,2) '@VAT',WRKORDNO INT '@WRKORDNO')
OPEN TempCursor
FETCH NEXT FROM TempCursor
INTO @wAmWork,@wCustNo,@wType,@wRefe,@wVIN,@wReg,@wConNo,@wRdy,@wIsSp,@wSMan,@wInvoNo,@wSubStatus,@wTot,@wTot0,@wVat,@wWrkOrdNo
// ... etc
COMMIT TRANSACTION
EXECUTE sp_xml_removedocument @XmlDocumentHandle
END

Previously, the stored procedure used to use nvarchar for input, but since that caused problems with the ancient backend application (Delphi 5 + ODBC), we had to switch the fields to varchars, at which point everything broke.

I also tried taking in nvarchar and converting that to varchar at the start, but the result is the same.

Answer 1

The errorcode you mention seems to come from the MSXML Library. How is that involved there? From your question I would assume that you pass a varchar parameter to a stored procedure, then insert or update a varchar column with that parameter.

However that does not match with your exception code so it must happen outside of the actual stored procedure or you are doing additional things based on xml inside the stored procedure.

Please check that and modify your question accordingly.

Answer 2

I'll answer my own question, since I managed to resolve the more than cryptic problem...

1) The stored procedure must reflect the correct code page for the transformation:

@XmlIn NVARCHAR(2000)
@XmlDocument VARCHAR(2000)
SELECT @XmlDocument = @XmlIn COLLATE SQL_Latin1_General_CP1_CI_AS

2) The XML input must specify the same charset:

<?xml version="1.0" encoding="ISO-8859-1" ?>

Answer 3

I don't know if anybody with enough rights to edit the answer will see this but while the answer is correct I would like to add that without specifying the collation explicitly the default collation of the database would be used in this case since it is implicitly assigned to every varchar-variable without a collation statement.

So

DECLARE @XmlDocument VARCHAR(2000) COLLATE SQL_Latin1_General_CP1_CI_AS

should do the trick, too.