Data returned from .WriteXML is different than what is returned in query analyzer.

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.

Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).

Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.

If someone is able to help me figure this out, I would appreciate it.

1. public DataSet GetMarketList(string region, string marketRegion)
2. {
3. string sql = @."SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode";
5. DataSet dsMarketList = new DataSet();
6. SqlConnection sqlConn = new SqlConnection(intranetConnStr);

7. SqlCommand cmd = new SqlCommand(sql,sqlConn);
8. sqlConn.Open();
9. SqlDataAdapter adapter = new SqlDataAdapter(cmd);

10. try
11. {
12. adapter.Fill(dsMarketList);

13. String bling = adapter.SelectCommand.CommandText;//BRG
14. dsMarketList.DataSetName="RegionMarket";
15. dsMarketList.Tables[0].TableName = "MarketList";
16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4
18. // from the SQL query
19. }
20. catch(Exception e)
21. {
22. // Handle the exception (Code not shown)

I think that you need to look at what data isn't being returned in your app. Can you load the XML file into Excel? You could then paste the SQL Analyzer results into Excel and look at the difference.

Also, there isn't an order by clause in your SQL, so SQL Server is free to return the results in a different order between invocations of the same SQL. (It rarely happens, but it does occasionally.)

|||Ugh !!! I figured out what the problem is. Thanks !sql