Solved

Truncated XML results in Query Analyzer

Posted on 2004-08-09
9
1,103 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 250 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

627 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