Anatoly Lubarsky Logo
programming, design, integration, games, music

MSSQL: sp_xml_preparedocument is not supported in fibers mode

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:

Friday, January 12, 2007 5:25 PM

Comments

# re: MSSQL: sp_xml_preparedocument is not
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.

1/13/2007 4:57 AM by Ayende Rahien

# re: MSSQL: sp_xml_preparedocument is not supported in fibers mode
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.

1/13/2007 5:02 AM by Anatoly Lubarsky

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs