Solved

Truncated XML results in Query Analyzer

Posted on 2004-08-09
9
1,067 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
Comment Utility
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
Comment Utility
Post your Stored Procedure.
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:macrom
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
I have a mistake:
Change:
ORDER BY rank.rank

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

Author Comment

by:macrom
Comment Utility
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
Comment Utility
>>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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now