Thursday, October 22, 2009 1:42 AM
I am hitting what appears to be a size limit for XML data type in the 43KB range which is much less than the 2 GB advertised. Changing the data type to NVARCHAR(MAX) exhibits the same "size limit" behavior.
Behavior of the code snippet below is that up to a 140 rows, the variable returns XML as expected. At 141 rows (for this particular example), it returns an empty string. NOTE: the query returns ALL rows regardless of size outside the variable.
Failure is occuring both on multiple local copies of SSMS as well as when running as a SQL Server Agent job. Environment is SQL Server 2005 9.00.4053.00 (X64)
Found an identical scenario here, but there was no resolution.
Is there a configuration setting or is there some other cause?
DECLARE @t TABLE (ClientId INT, FirstName VARCHAR(50), LastName VARCHAR(50), BatchId SMALLINT, SentDate Datetime, Amount MONEY, response_date DATETIME, response_code CHAR(3), response_text VARCHAR(50), response_type CHAR(1),debit_credit CHAR(1), [Description] VARCHAR(50), DraftNumber TINYINT, ClientStatus VARCHAR(50))
DECLARE @ix SMALLINT
SET @ix = 1
WHILE (@ix <= 141)
INSERT INTO @t VALUES(1234567, 'Joe', 'Bob', 999, '2009-10-15 00:00:00', 29.99, '2009-10-20 00:00:00', 'R01', 'Insufficient Funds', 'R', 'D', 'invoice',1, 'Not Current')
SET @ix = @ix + 1
DECLARE @xml XML
SET @xml = (SELECT * FROM @t FOR XML RAW ('ACH'))
SELECT @xml -- returns empty at 141 rows, returns xml for less than 141 rows
SELECT *FROM @t FOR XML RAW ('ACH') -- returns ALL rows at 141 and above
Thursday, October 22, 2009 8:32 AMAnswererIn Tools, Options, Query Results, SQL Server, Results to Grid, there is an option for XML data length. Try increasing that and report back.
Thursday, October 22, 2009 10:10 AMThank you for the reply.
Maximum characters retrieved XML Data: 2MB
I added to the example to illustrate the difference between XML variable and XML from a query. Also, this behavior is also exhibited when run as a job at the server level.
Thursday, October 22, 2009 12:26 PMAnswererWell your code works fine for me even if I set the limit to 9999. Maybe it's some kind of Service Pack thing? What edition and service pack of SQL Server are you running?
Thursday, October 22, 2009 12:32 PMTo clarify, up to 140 rows, SELECT @xml and SELECT * FROM @t FOR XML RAW ('ACH') return identical results. At 141 rows and above, SELECT @xml returns an empty string while SELECT * FROM @t FOR XML RAW ('ACH') continues to return the expected XML.
Thursday, October 22, 2009 2:54 PM
>>What edition and service pack of SQL Server are you running?
@@version = Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01.
>>Well your code works fine for me
Yeah, the code works on my home system, too. My favorite kind of problem: not easily reproduced.
>>some kind of Service Pack thing?
I believe that is the correct direction to go.
Is your system 32 BIT or 64 BIT?
My workstation is 64 BIT Vista and the servers are 64. I have run this code on other desktops -- ALL are 64 Bit VISTA. But, I just located a workstation (completely by accident) where this code is running correctly. So far, the only difference I see is that the 'good' machine is 32 Bit XP.
Thursday, October 22, 2009 4:09 PMAnswererYou might consider logging a connect:
I can't reproduce the problems on my 32 bit laptop running Windows XP, or a 64-bit VM running Windows Server 2003. Good luck!
Let us know how you get on.
Thursday, October 22, 2009 6:10 PM
Thanks for the feedback. I have started testing multiple variables -- OS, SSMS vs Stored Procedture, etc. So far, getting surprisingly variable results. I am pushing this up to Microsoft after I complete further testing. Will post results. Again, thank you for the feedback.
Thursday, October 22, 2009 8:17 PMResults are in ... Example query is same as above. Stored procedure uses example query to insert to a table column.
Device Operating System Software Test Expected Result Actual Result Workstation Vista 64 Bit, SQL Server 2005 9.00.4053.00 (X64) SSMS example query 2 identical XML results A) an empty string and B) XML Workstation XP 32 Bit SQL Server 2005 9.00.4053.00 (X64) SSMS example query 2 identical XML results 2 identical XML results Workstation XP 64 Bit SQL Server 2005 9.00.4053.00 (X64) SSMS example query 2 identical XML results 2 identical XML results Remote Desktop Connection Windows Server 2003 SQL Server 2005 9.00.4053.00 (X64) SSMS example query 2 identical XML results 2 identical XML results Server/SQL Server Agent Windows Server 2003 SQL Server 2005 9.00.4053.00 (X64) Stored Procedure XML inserted to table empty string inserted to table
Friday, October 23, 2009 1:51 PMSummary of the results above.
Results vary based on operating system and method.
IF OS = XP 32 bit OR XP 64 bit OR Windows Server 2003 64 bit
AND method = SSMS
THEN example query works as expected.
IF OS = Vista 64 bit AND Method = SSMS
THEN example query fails -- i.e XML variable returns empty string at 141 rows
IF OS = Windows Server 2003
AND method = Stored Procedure run from SQL Server Agent
THEN XML variable returns empty string at 141 rows
Stored procedure was tested by inserting XML data to a table.
Wednesday, November 04, 2009 11:54 PMSorry for the delay in reporting back. The problem is SQL Server 2005 with Vista SSMS.
There is no data loss with XML data type. There is only the appearance of data loss because Vista SSMS 2005 does not correctly display XML datatype columns or variables when size exceeds 43KB. Appears that NVARCHAR(MAX) may have the same limitation, but I have not conclusively reproduced that.
Final test that caused me to reach this conclusion:
1. CREATE TABLE dbo.xmltest (x XML)
2. INSERT INTO dbo.xmltest VALUES (@xml) ... where @xml is XML over 43KB
3. SELECT * FROM dbo.xmltest
a. From Vista SSMS empty string is displayed
b. From XP SSMS XML is correctly displayed
Microsoft was able to reproduce the error and has entered it as a bug fix. Microsoft reports that the bug does NOT occur in SQL Server 2008.
My workaround was to revert back to XP. Other workarounds include to not display results in grid and to upgrade to 2008.
Tuesday, January 17, 2012 10:04 PM
Does anyone know if this bug fixed for SQL 2005 (Developer edition)?
I'm running Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1).
And the problem you described is exactly the same thing is happening here...My computer has WindowsXP32bit and I can see/send the xml without any problems....the problem resides in computers that run Windows7 and ON Windows Server 2008...