Anatoly Lubarsky

Did you know, that XML procedures like sp_xml_preparedocument or sp_xml_removedocument are not supported in MSSQL when the server is configured as "Use Windows NT fibers" (under server » processor) ? I didn't. Consider the following T-SQL snippet, which extracts data from XML document:

DECLARE @xml  VARCHAR(1000)
DECLARE @idoc INT
 
SET @xml = '< ROOT>
                < User Id="1" />
                < User Id="2" />
                < User Id="3" />
            < /ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
 
SELECT UserId 
  FROM OPENXML (@idoc, '/ROOT/User', 2)  
  WITH (UserId INT '@Id')

EXEC sp_xml_removedocument @idoc

If the processor is configured in "NT fibers mode", you get run-time exception:

"XML stored procedures are not supported in fibers mode"

BOL does not tell anything about the issue (at least under sp_xml_preparedocument it doesn't ). To fix this problem, go to the processor tab, and uncheck the box "Use Windows NT fibers" (requires restart). If it is your server - everything is fine.

Related Posts:

• MSSQL: Got arithmetic overflow for INT ?
• MSSQL 2005 Express Edition - unbeatable
• T-SQL: The Rozenshtein Method
• Always Use Stored Procedures in MSSQL
• MSSQL 2005 Service Pack 1 is available
• Enterprise Library: Bug with decimal in DataAccess block
• Microsoft Query Optimization Team is Blogging
• MSSQL 2005: Paging with ROW_NUMBER is the fastest
• MSSQL: Managing Schema Changes on Large Tables
• The most important thing in SQL

I would assume that those SP uses thread local storage to do their work, most probably putting the prepared document somewhere.

Fibers mode was a good option when thread switching cost a lot more than it does today (although it is still expensive). I believe that the current recommendation is to use threads instead.
Especially since that let SQL Server use multi core more efficently.

Yes, these procs use threads instead of fibers and also store the doc in memory specially allocated by MSSQL.

Recommendation is to use threads but if you write a distributable application you have to be aware of the issue since it is upon customer to decide what mode MSSQL server to run.