We have an application having problems communicating with a SQL Server
database via XML.

The application is a Microsoft Word VBA applicatino using Microsoft SML,
v2.6 to create XML documents to hand to the server.
It is connecting to SQL Server 2000.

It has been operating fine for 18 months.

Last week we moved SQL Server to a new Server. The only significant change
was that the server is running Windows Server 2003 whereas the old server was
running Windows Server 2000.

The passing of the XML document back to the SQL Server database is now
crashing whenever the user has used the £ symbol.

I haven't been able to find anything on the web about this. Suggestions
welcome.

Thanks
Derek

This is most likely an encoding issue...

What encoding is the pound sign in the XML document? And how do you pass it
to SQL Server?

As always, a minimal repro would be useful.

Thanks
Michael

"Derek" <Derek (AT) discussions (DOT) microsoft.com> wrote

Thanks Michael

The document is passed to SQL Server stored procedures as a Varchar (8000).

The following code then converts it into a table ready for inserting into
the database.


Exec sp_xml_preparedocument @l_iXMLDoc Output, @@p_sXML

Insert into @tblXML ( ActionID,
Salary,
Incentives,
Operation,
UserID)
Select *
From OpenXML(@l_iXMLDoc,'Finances/Finance')
With (ActionID Int './@ActionID',
Salary Varchar(50) './@Salary',
Incentives Varchar(100) './@Incentives',
Operation Char(1) './@EditStatus',
UserID Varchar(20) './@UserID')

EXEC sp_xml_removedocument @l_iXMLDoc

Since the application was unchanged, and it was purely a SQL change, is it
possible to simply make the change in the SQL environment, or would that be a
regressive step. If we need to make a change in the application, we can, but
it would be considerably more expensive and need to be project planned.

Do you know what these SQL Server settings would be?

Thanks
Derek

"Michael Rys [MSFT]" wrote:

Without seeing the actual xml you are trying to load its only possible
to guess at what the problem is...

The problem probably is that the xml parser inside of is using the code
page configured for your instance of SQL Server, which doesn't support
the £ symbol. For example:

DECLARE @m VARCHAR(8000)
SELECT @m = '<B><A>£100</A></B>'
DECLARE @h INT
exec sp_xml_preparedocument @h OUTPUT, @m

Will produce:

The XML parse error 0xc00ce508 occurred on line number 1, near the XML
text "<B><A>".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'An invalid character was found in text
content.'.

Because of the code page setup for SQL Server. However if you can use
NVARCHAR instead of VARCHAR the xml will be interpreted as Unicode which
the xml parser in SQL Server can handle. For example will produce no
error:


DECLARE @m NVARCHAR(4000)
SELECT @m = '<B><A>£100</A></B>'
DECLARE @h INT
exec sp_xml_preparedocument @h OUTPUT, @m

Of course with NVARCHAR you can only handle 4000 characters.

If you can modify the incomming xml you can specify an encoding that
supports the £ character, for example:

DECLARE @m VARCHAR(8000)
SELECT @m = '<?xml version="1.0"
encoding="ISO8859-1"?><B><A>£100</A></B>'
DECLARE @h INT
exec sp_xml_preparedocument @h OUTPUT, @m

Will allow the xml to be read without an error.



Dan



"Derek" <Derek (AT) discussions (DOT) microsoft.com> wrote

Also, if you are allowed to change the collation in the database
something like this will also allow the £ character in your xml to be
read without changing your code.

ALTER DATABASE DatabaseName COLLATE SQL_Latin1_General_CP1250_CI_AS

Or change your code to load the VARCHAR as follows:

DECLARE @m VARCHAR(8000)
SELECT @m = '<B><A>£100</A></B>' COLLATE SQL_Latin1_General_CP1250_CI_AS
DECLARE @h INT
exec sp_xml_preparedocument @h OUTPUT, @m

And this will work.

In any case you have to be sure that the collation you use makes sense
for the string you are passing into SQL Server.


Dan


"Dan" <DanATPluralsight> wrote

Thanks for this Dan.

It appears that the new box has a different collation to the old box. (The
old box actually had the wrong collation).

We now have three options.
1) Re-write the stored procedures with nVarchar. I’d prefer not to do this,
as we regularly had trouble with the 8000 character limit and reducing that
to 4000 would definitely cause problems.
2) Explicitly set the collation as shown by you in all the stored
procedures. This probably won't be too much work.
3) Create a non-XML version.

To be honest we were looking at moving away from XML anyway (too clunky and
too much work to code). Our BA is talking to the business about it.

So thanks for helping us track down the source of the problem.

Cheers
Derek

"Dan" wrote: