Truncated XML results in Query Analyzer

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?
macromAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
HilaireCommented:
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
 
macromAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Anthony PerkinsCommented:
Post your Stored Procedure.
0
 
HilaireCommented:
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
 
macromAuthor Commented:
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
 
Anthony PerkinsCommented:
I have a mistake:
Change:
ORDER BY rank.rank

To:
ORDER BY [product!2!rank]
0
 
macromAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.