| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-04-24 : 11:17:16
|
| When Running a query that has "FOR XML AUTO" at the bottom in Query Analyzer, I'm getting this error just after it shows my string of XML. This error is in the data windows just below teh result set.[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL ServerI started getting this when I re-arranged some fields. What could be causing this? I can post the query if needed, but it's rather large.MichaelEdited by - michaelp on 04/24/2002 11:17:48 |
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2002-10-18 : 11:24:22
|
| Does anyone have an answer for this? I'm having the same issue, although it's with XML AUTO as well as XML RAW. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-18 : 11:27:58
|
| Wish I had an answer for ya, but I got away from "FOR XML" in SQL Server. Sorry!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2002-10-18 : 11:58:02
|
| Define "Got away". Is there another option within SQL server for outputing XML? |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-18 : 12:03:12
|
| I was testing some things when I posted this question. I've since stopped using FOR XML queries in SQL Server.There are a few ways around getting XML data out of SQL Server.ADO1. Run a stored proc, populate an ADO recordset with the data, and save the recordset as XMLrs.Save("c:\myxmlfile.xml", 2) '2 is a constant for ADO XML or something like thatADO.Net2. Run a stored proc, populate a dataset, and serialize the Dataset to XML.Can you explain what you are trying to do with XML? Maybe there's another way to do what you are trying to do.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-18 : 12:03:31
|
| Since it seems to be an ODBC problem, maybe updating your MDAC components might fix it. Get the latest here:http://www.microsoft.com/data/download.htmBTW, what kind of data is being returned in the XML? I imagine that Unicode data might be part of the problem. If you have any nchar/nvarchar/ntext columns in the output, try removing them and see if it fixes it. Same applies for bit, binary/varbinary, and image data types. |
 |
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2002-10-18 : 12:21:04
|
| Boy, am I stumped! I'll check into my MDAC, but I've discovered that if I use QA and have Show Execution Plan on, I get very unpredictable results, including this error. If I remove columns from the select list (looking into the datatype issue rob mentioned), I can sometimes get no results at all. So, as long as I have Show Execution Plan off, it works fine.As to why I'm outputting XML, this application was built bass ackwards and the UI has already been completed. It is expecting XML as the data source, so I started messing around with FOR XML.Using ADO.NET to create the XML is a good suggestion. Any idea on what the performance difference is between using FOR XML and serializing the dataset in ADO.NET? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-18 : 12:25:55
|
| It will probably work out about the same. If SQL Server generates the XML, it will have added "bloat" that will take longer to transmit over the network. However, the web server or client computer will only need to receive the data. With ADO.Net, there would be less network traffic but more client CPU cycles to XML-ize the data. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-18 : 12:27:07
|
| FOR XML is probably faster, but probably not by too much. It will probably mean less network traffic. I'm not experienced enough with ADO.net yet to tell you exactly.If it works and FOR XML doesn't, I'd say go for it.Could I have used probably more in this post? Probably not :)Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
BlackDog
Starting Member
18 Posts |
Posted - 2002-10-18 : 12:34:30
|
| Thanks. I'll do some testing and see which is best for my situation. |
 |
|
|
|