|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
The weirdest SQL problem you'll ever see.forever acknowledged as THE supreme being of all things SQL Server 2000. I have a stored proc (spGetMemberInfo) that accepts one int parameter, @AppID, and returns XML via FOR XML EXPLICIT. The output looks something like this: <RESULTS><MEMBER><FIRSTNAME>John</FIRSTNAME>..etc.......</MEMBER></RESULTS> spGetMemberInfo is called hundreds of times daily by a C# object hosted as a service. Once in a blue moon, with no apparent pattern, SQL Server returns the following error to the object (and only the object): Undeclared tag ID 1 is used in a FOR XML EXPLICIT query. So I check the object's logs and see that it passed the following to SQL Server: EXEC spGetMemberInfo @AppID=N'123456' (app number varies, obviously) I copy and paste that exact line into Query Analyzer -- and it works just fine. I test the object again, and the same error is returned every time. But wait. The madness gets much worse: If I go into the stored proc and add whitespace -- yes, just add a space or a return somewhere in the code -- and then hit Apply, the problem is resolved until days or weeks later when it crops up again. So there it is. If anyone has ANY ideas where to even begin, I'm all ears. Thanks. On Fri, 02 Dec 2005 20:54:54 GMT, "Darren Mart via SQLMonster.com"
<u12409@uwe> wrote: >If I go into the stored proc and add whitespace -- yes, just add a space or a How about if you just sp_recompile the SP?>return somewhere in the code -- and then hit Apply, the problem is resolved >until days or weeks later when it crops up again. > >So there it is. If anyone has ANY ideas where to even begin, I'm all ears. No idea about the problem itself, however. J. jxstern wrote:
>>If I go into the stored proc and add whitespace -- yes, just add a space or a That's an interesting question, I'll have to see if sp_recompile accomplishes>>return somewhere in the code -- and then hit Apply, the problem is resolved >>until days or weeks later when it crops up again. >> >>So there it is. If anyone has ANY ideas where to even begin, I'm all ears. > >How about if you just sp_recompile the SP? > >No idea about the problem itself, however. > >J. the same thing as adding white space and Applying it... Please check the service pack . We have also problem with For XML where
we used SP1 , later we updated it with SP3 and SP4 , it worked fine. Regards > But wait. The madness gets much worse: Do you have any third party applications / services running on this server? > > If I go into the stored proc and add whitespace -- yes, just add a space > or a > return somewhere in the code -- and then hit Apply, the problem is > resolved > until days or weeks later when it crops up again. There may be something running that is finding funny Unix or other non-printing characters and attempting to fix them. I seem to recall hearing whispers of this kind of activity before, bug a quick google turned up nothing. Anyway, I agree with jxstern... in fact I would suggest dropping the procedure entirely and re-creating it. I might even go so far as to re-type out all the code, just to be sure you don't get any unintentional artifacts of the old code. A I should have mentioned that the procedures afflicted by the problem varies
each time this happens. It's anybody's guess which procedure(s) are going to bomb when this happens. I guess it's a thought to manually re-create the sprocs, but I shudder to think of doing that for more than 20 of them... Aaron Bertrand [SQL Server MVP] wrote: Show quote >> But wait. The madness gets much worse: >> >[quoted text clipped - 3 lines] >> resolved >> until days or weeks later when it crops up again. > >Do you have any third party applications / services running on this server? >There may be something running that is finding funny Unix or other >non-printing characters and attempting to fix them. I seem to recall >hearing whispers of this kind of activity before, bug a quick google turned >up nothing. > >Anyway, I agree with jxstern... in fact I would suggest dropping the >procedure entirely and re-creating it. I might even go so far as to re-type >out all the code, just to be sure you don't get any unintentional artifacts >of the old code. > >A Darren,
Have you upgraded to service pack 4? This sounds like a bug that was scheduled to be fixed there. See http://www.mcse.ms/post2588494.html There is a bug in the SQL Server engine that can cause this error, and you may be hitting it. If the bug was not fixed in sp4, or if you cannot upgrade for some reason, there is a workaround given, to change column references like [elt!28] to [elt!28!] . Usenet contains reports that the workaround worked for others also. Steve Kass Drew University Darren Mart via SQLMonster.com wrote: Show quote >If you're able to shed light on the problem described below, you will be >forever acknowledged as THE supreme being of all things SQL Server 2000. > >I have a stored proc (spGetMemberInfo) that accepts one int parameter, @AppID, >and returns XML via FOR XML EXPLICIT. The output looks something like this: > ><RESULTS><MEMBER><FIRSTNAME>John</FIRSTNAME>..etc.......</MEMBER></RESULTS> > >spGetMemberInfo is called hundreds of times daily by a C# object hosted as a >service. Once in a blue moon, with no apparent pattern, SQL Server returns >the following error to the object (and only the object): > >Undeclared tag ID 1 is used in a FOR XML EXPLICIT query. > >So I check the object's logs and see that it passed the following to SQL >Server: > >EXEC spGetMemberInfo @AppID=N'123456' (app number varies, obviously) > >I copy and paste that exact line into Query Analyzer -- and it works just >fine. I test the object again, and the same error is returned every time. > >But wait. The madness gets much worse: > >If I go into the stored proc and add whitespace -- yes, just add a space or a >return somewhere in the code -- and then hit Apply, the problem is resolved >until days or weeks later when it crops up again. > >So there it is. If anyone has ANY ideas where to even begin, I'm all ears. > >Thanks. > > > Steve Kass wrote:
>Darren, Steve,> >Have you upgraded to service pack 4? This sounds like a bug >that was scheduled to be fixed there. See > >http://www.mcse.ms/post2588494.html > >Steve Kass >Drew University >>> Thank you very much for the info and the link. That thread you linked sounds exactly like my scenario. I'll check with the DBAs on the service pack on Monday, in the meantime I've updated my procedures with the suggested workaround. Cheers, Darren -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200512/1 Let us know how it works out!
SK Darren Mart via SQLMonster.com wrote: Show quote >Steve Kass wrote: > > >>Darren, >> >>Have you upgraded to service pack 4? This sounds like a bug >>that was scheduled to be fixed there. See >> >>http://www.mcse.ms/post2588494.html >> >>Steve Kass >>Drew University >> >> >>>> >>>> >>>> > >Steve, > >Thank you very much for the info and the link. That thread you linked sounds >exactly like my scenario. I'll check with the DBAs on the service pack on >Monday, in the meantime I've updated my procedures with the suggested >workaround. > >Cheers, >Darren > > > The only time I've seen this sort of error is where the FOR XML
EXPLICIT statement is missing an ORDER BY clause, most of the time it works, occasionally it fails. Perhaps you could post the SQL.  |
|||||||||||||||||||||||
Other interesting topics