?
Solved

Xml error: "Index was outside the bounds of the array."

Posted on 2008-11-07
7
Medium Priority
?
1,976 Views
Last Modified: 2012-05-05
I'll start with an easy way to reproduce the error I'm seeing:

Just download the following:
http://www.eazign.be/Temp/SqlRepro.sql.zip

Contains only 1 file: SqlRepro.sql
Run this script on any SQL Server 2008 (I use the Standard version).

What the script does:
1. Create a new database: "FdmSqlRepro" (drops it first if already exists)
2. Create a schema collection: "MySchemaCollection"
3. Create a table: "TestTable", with a numeric "idx" column and a typed xml column "xData". The xml column is bound to the created "MySchemaCollection"
4. Insert 1 record in the table
5. Query the table (SELECT * FROM TestTable)

That's all!

The last step (query *) gives the error:
"Index was outside the bounds of the array."

------------

Some more background:

Sql Server 2008 Standard:

To isolate the problem I'm having, I've created a simple Dbs with just one table, having an "int" field and an "xml" field.

The xml field is assigned to a SchemaCollection, so it is typed.
Updating the xml field with XML data works 99.999% of the times, but I have 2 specific XML fragments that systematically give me problems. Updating the xml field in the table with this data works fine without errors, however when I do a SELECT * from the table in the Management Studio, I get the error:

"An error occurred while executing batch. Error message is: Index was outside the bounds of the array."

Changing "SELECT xmlField FROM tab" to "SELECT CAST(xmlField AS nvarchar(MAX)) FROM tab" appears to give me the correct xml string, so the data is there!

Querying the data from .Net code gives the same error. Here's part of the stack trace:

[IndexOutOfRangeException: Index was outside the bounds of the array.]
System.Xml.XmlSqlBinaryReader.GetXsdKatmaiTokenLength(BinXmlToken token) +1207761
System.Xml.XmlSqlBinaryReader.ScanOverAnyValue(BinXmlToken token, Boolean attr, Boolean checkChars) +524
System.Xml.XmlSqlBinaryReader.ScanOverValue(BinXmlToken token, Boolean attr, Boolean checkChars) +159
System.Xml.XmlSqlBinaryReader.ImplReadData(BinXmlToken tokenType) +119
System.Xml.XmlSqlBinaryReader.ReadDoc() +589
System.Xml.XmlSqlBinaryReader.Read() +201

I get this exact problem on different machines: both the production Server 2003 and my Vista development PC.

I've been successfully using XML fields in SQL2005 for 3 years now, and have recently upgraded to SQL2008, so it's only on SQL 2008.

This has me completely puzzled. If I don't find a solution/work-around, I'll have to downgrade to SQL 2005....
This looks like a bug in SQL Server 2008.

What I'm looking for is a fix or work-around ...
0
Comment
Question by:FrankyDeMeyer
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 22927084
At the time of the exception, can't you examine the state of the XmlReader to see what node it was parsing at the time of the error?
Or lacking that, remove parts of the XML until the error stops occurring.  
Neither of these would solve the problem, but if you can isolate what it is about that specific XML that is causing the problem, then that's a starting point.
0
 

Author Comment

by:FrankyDeMeyer
ID: 22929220
Well, there are 2 ways to reproduce the exception:

1. In SQL Server Management Studio, by running the repro SQL script (http://www.eazign.be/Temp/SqlRepro.sql.zip). In that case, the error is shown in the SQL Mng Studio result window, so there's not much debugging I can do at that level (I think).

2. When accessing the Dbs from .Net code. In that case, the exception is thrown by calling:
SqlDataReader.GetSqlXml(i). That's a .Net library call. The index that I'm specifying is certainly OK.
I have even enabled MS Library Source debugging, to see if I could step into the GetSqlXml() library call, but this is in MS assembly "System.Data.SqlClient", for which MS has not published the Source debugging symbols.

So, in short, to answer your first question: I have no way to debug into the MS library code and find out at what XML node things go wrong. At least, not that I know of.

---

Regarding your second suggestion: I have done that, and the weird thing is that the exception goes away when I "randomly" remove parts of the XML. There is absolutely no consistency in that. It can go away by removing some fragment in the middle, or at the start or end of the XML. Even changing some text can make the error appear and re-appear.

We have thousands of records with different XML which are all OK, but it seems to be this specific combination of XML that provokes the error.

Any further suggestions are very welcome! Thanks.
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 2000 total points
ID: 22935770
The most common reason for an "out of bounds" exception is progamming that contains a sequence like
   if ( array(array.Length-1) = "x") ...
that is, a situation when an index of -1 might end up being used (e.g., .when Length in the above example is 0).    I thoroughly understand that it is not your code that is causing the exception, but it's a place to start looking at the data.  Is there any item in this particular XML where an empty value goes in but a non-empty value might be expected?
I'd also try making small changes to any element that is associated with a restricted string.  For instance, EmailPattern_Type and Guid_Type.  There may be a subtle bug in Microsoft's handling of these items, seen only when certain sequences of data are present.   A diagnostic aid would be to change these to simple xs:string and see if the problem goes away.
In your shoes, I'd keep trying specific small changes until I narrowed it down to the minimum possible dataset with the minimum possible schema.  Once you have a tight/short, repeatable error, I'll bet that Microsoft tech support will take notice.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Closing Comment

by:FrankyDeMeyer
ID: 31514364
Thanks for tackling this rather convoluted question of mine!
0
 

Author Comment

by:FrankyDeMeyer
ID: 22971954
Thanks for looking into the schema and XML and for your helpful suggestions.

Up to now, I've mostly looked into making changes to the actual XML to see which part the problem is related to.

I've now started to make some simplifications to the XSD of the schemacollection itself, to find any dependencies.

Substituting EmailPattern_Type and Guid_Type with xs:string didn't make any difference, but then I removed all the attributes which had a "fixed" value, and also removed any references to this attribute in the XML itself. This does seem to circumvent the problem with the XML examples I have tried.

Although it doesn't really solve the actual problem, and may not be a 100% complete work-around, it is a potential route I can take.

For this reason, and for the fact that it is now clear that it's an MS SQL Server 2008 bug, I accept your helpful advice as solution. Thanks.

I am now also in contact with MS for detailed bug reporting, hopefully resulting in some patch from them.
0
 

Expert Comment

by:PointRollIT
ID: 37240038
For me, this problem was being caused by comments in my XSD that spanned more than one line.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question