Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Truncated XML results in Query Analyzer

Posted on 2004-08-09
9
Medium Priority
?
1,114 Views
Last Modified: 2011-09-20
I have a small problem getting incomplete results back from Query Analyzer when calling a stored procedure that returns XML. On one machine, it works just fine, but on another I get truncated data. I've installed SQLXML 3.0 SP2, MDAC 2.8, and otherwise I can't see what the difference is between the machines. This is affecting both results in Query Analyzer and results obtained through System.Xml in ASP.NET. Where do I need to look to fix this problem?
0
Comment
Question by:macrom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 11753067
Can you post your code ?
Also check in QA
menu Tools-->Options : in the "Results" Tab, make sure the "Maximum characters per column" is set to a sufficient value (i think the max is around 8000)
0
 

Author Comment

by:macrom
ID: 11754265
OK, the problem in Query Analyzer is now fixed, but I still get this error in my .NET code :

"Invalid command sent to ExecuteXmlReader.  The command must return an Xml result."

Here is the code I'm calling, where the name of the stored procedure is passed in :

public void ExecuteStoredProcForXML( string inStoredProc, ref System.Xml.XmlDocument outXML )
            {
                  System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand( inStoredProc, m_conn );
                  command.CommandType = System.Data.CommandType.StoredProcedure;

                  System.Xml.XmlReader reader = command.ExecuteXmlReader();
                  outXML.Load( reader );
            }

Generally, the calling code would just pass in "sp_name" for the first parameter. Again, this code seems to work on one machine and not another, which seems odd.

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11754396
Post your Stored Procedure.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 26

Expert Comment

by:Hilaire
ID: 11754869
Also make sure your stored procedure has a

SET NOCOUNT ON
at the beginning of the code

and a

SET NOCOUNT OFF
at the end
0
 

Author Comment

by:macrom
ID: 11755039
Here's the SP :

CREATE PROCEDURE [core].[get_top_ten] AS
SELECT '<top_ten>'
SELECT 1 AS tag,
      NULL AS parent,
      rank.rank AS [product!1!rank],
      product.sku AS [product!1!sku] ,
      manu.manu_name AS [product!1!manufacturer],
      product.name AS [product!1!name],
      product.aspect_ratio AS [product!1!aspect_ratio],
      product.resolution AS [product!1!resolution],
      product.svideo_ports AS [product!1!svideo_ports],
      product.component_ports AS [product!1!component_ports],
      product.composite_ports AS [product!1!composite_ports],
      product.dvi_ports AS [product!1!dvi_ports],
      product.core_price AS [product!1!core_price],
      product.image_name AS [product!1!image],
      cat.category_name AS [product!1!category],
      product.model_number AS [product!1!model_number],
      product.manu_link AS [product!1!url]
FROM products AS product
INNER JOIN top_ten rank
ON product.sku = rank.product_sku
INNER JOIN manufacturers manu
ON product.manufacturer = manu.manu_id
INNER JOIN categories cat
ON product.category = cat.category_number
ORDER BY rank.rank
FOR XML EXPLICIT
SELECT '</top_ten>'
GO

Nothing fancy -- I suppose I could forgo manually building the XML doc and just say "FOR XML AUTO", but I don't know that would make any difference.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 11756335
Try it like this:

CREATE PROCEDURE [core].[get_top_ten]

AS

SET NOCOUNT ON
SELECT 1 AS tag,
     Null AS parent,
     Null As [top_ten!1],
     Null AS [product!2!rank],
     Null AS [product!2!sku] ,
     Null AS [product!2!manufacturer],
     Null AS [product!2!name],
     Null AS [product!2!aspect_ratio],
     Null AS [product!2!resolution],
     Null AS [product!2!svideo_ports],
     Null AS [product!2!component_ports],
     Null AS [product!2!composite_ports],
     Null AS [product!2!dvi_ports],
     Null AS [product!2!core_price],
     Null AS [product!2!image],
     Null AS [product!2!category],
     Null AS [product!2!model_number],
     Null AS [product!21!url]
            
Union All

SELECT 2,
     1,
     Null
     rank.rank,
     product.sku,
     manu.manu_name,
     product.name,
     product.aspect_ratio,
     product.resolution,
     product.svideo_ports,
     product.component_ports,
     product.composite_ports,
     product.dvi_ports,
     product.core_price,
     product.image_name,
     cat.category_name,
     product.model_number,
     product.manu_link
FROM products AS product
            INNER JOIN top_ten rank ON product.sku = rank.product_sku
            INNER JOIN manufacturers manu ON product.manufacturer = manu.manu_id
            INNER JOIN categories cat ON product.category = cat.category_number
ORDER BY rank.rank
FOR XML EXPLICIT
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11756425
I have a mistake:
Change:
ORDER BY rank.rank

To:
ORDER BY [product!2!rank]
0
 

Author Comment

by:macrom
ID: 11756977
Thank you, acperkins. That worked just fine. Now, for another question : what was wrong with my procedure? I'm not really a SQL person, so I just copied a sample stored proc that I found out on the net.

Also, do I need SELECT NOCOUNT OFF at the end of the stored proc?

Thanks for the great answer!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11757291
>>what was wrong with my procedure?<<
I realize the result looked good in SQL QUery Analyzer, but you were actually returning three results.  The first and last were recordsets not streams, so it was not surprising that command.ExecuteXmlReader failed.

>>Also, do I need SELECT NOCOUNT OFF at the end of the stored proc?<<
I never use it, on the other hand it does not hurt to add it.

Strictly speaking SET NOCOUNT ON is not entirely necessary in my stored procedure, however you should get in the habit of adding it.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

688 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