| Author |
Topic |
|
rado
Starting Member
18 Posts |
 Posted - 2005-03-02 : 06:02:05
|
| Hi there,I have a strange problem with one stored procedure which uses FOR XML EXPLICIT, and this problems has been occuring occasionally during the past few weeks. I've got this stored procedure with one SELECT FOR XML EXPLICIT query, and the query is correct - there are absolutely no problems within the query itself. The problem: every now and then (about once-twice a week), when this SP is executed it will throw a "Undeclared tag ID 15 is used in a FOR XML EXPLICIT query." Please note that there is, and always was the tag with ID 15. It will keep throwing this error, and to fix it is I need to edit the SP, I replace the tag id 15 with a non-existent tag id (e.g. 99), then click "OK" to save the SP. This time it will throw a modified error message "Undeclared tag ID 99 is used in a FOR XML EXPLICIT query." Then I edit the SP again, change 99 back to 15, click "OK" to save the SP, and voila, everything works like a charm again. The funny thing is that clickin "Apply" instead of "OK" didn't help, that is, after changing tag id to 99 and clicking "Apply" it will throw the "Undeclared tag ID 99 ..." error, and after changing 99 back to 15 and clicking "Apply" the old error message is back: "Undeclared tag ID 15 ..." Has anyone else experienced this strange behavior? The server used is SQL Server 2000 Developer Edition on Windows Server 2003 Standard.Thanks for any clues in advance!Rado |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 07:37:51
|
| Do you know what service pack you're using? If you're not on SP3/SP3a, try installing it and see if it fixes it:http://www.microsoft.com/sql/downloads/2000/sp3.asp |
 |
|
|
rado
Starting Member
18 Posts |
Posted - 2005-03-02 : 09:04:28
|
| It says it is version 8.00.760 (SP3), were there any newer updates/patches to install? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-02 : 21:39:17
|
| Nope, you're at the max, although SP4 is in beta right now and should be released within a month or two.The other thing you should do is NOT edit or create stored procedures in Enterprise Manager. Use Query Analyzer instead. It's possible that this is strictly an issue with EM and not the code itself. |
|
|
|
rado
Starting Member
18 Posts |
Posted - 2005-03-03 : 02:11:03
|
| I have never tried to work with stored procedures in QA so I may try that and see if it will help, although I'm afraid it won't. The stored procedure is executed from an ASP.NET web application, and it usually works fine for a few days, but then suddenly it will start throwing that strange error without doing any modifications to it from EM. |
|
|
|
rado
Starting Member
18 Posts |
Posted - 2005-03-08 : 04:37:50
|
| I have tried to save the SP in QA but it doesn't help. The problem now occurs almost daily. :( That is, the SP will start to throw the error message, than it has to be saved either in EM or QA to work again. It doesn't help to just save it with no changes, you have to change something in it (for example insert a blank or or anything) to make it work again. This is getting on my nerves. :-( |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-08 : 07:42:19
|
| Would you be able to post your code, along with the table structures involved, and some sample data and output? It would make it easier to try and duplicate the problem.Have you tried changing the names of the ID attribute? Like XYZ for example. Just to see if the error occurs. It might also be a good idea to test the query against different tables. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-08 : 08:46:03
|
| Any chance that its the fact that the SProc is caching a query plan, and recreating it starts a new query plan which in turn fixes the problem?Maybe add WITH RECOMPILE so that it always recompiles.Might be something weird with parallelism - is it a multi-CPU box?Kristen |
|
|
|
rado
Starting Member
18 Posts |
Posted - 2005-03-09 : 02:22:02
|
| robovolk:I don't think I'm able to post the code, and the SP is rather long, but anyway, it is not the single SP where this error occurs. It already happened with other FOR XML stored procedures as well, but we encounter it most often with this one, perhaps due to the fact that we're working with it most often during development.KristenNo it's not a multi CPU box it's an older dell development server with a celeron CPU. I have no experience with query plan caching / WITH RECOMPILE option - I'll look it up in the books online. Thanks for your idea. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-09 : 03:36:08
|
| CREATE PROCEDURE MySproc... parameters ...WITH RECOMPILEAS... Body of SProc ...I'd be interested to know what you find. I find that FOR XML is a bit fragile in SQL2K, and I'd like to know about any potential GotChas that we could remove from our production code!!Kristen |
|
|
|
|
Strange FOR XML EXPLICIT SP problem