Solved

Truncated XML results in Query Analyzer

Posted on 2004-08-09
9
1,085 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

830 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