Post reply

The weirdest problem you''ll ever see

  • December 2, 2005 at 2:22 pm

    #90075

    If you're able to solve the problem 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.

  • December 2, 2005 at 2:40 pm

    #607532

    Can you post the SQL?

  • December 2, 2005 at 2:50 pm

    #607535

    A bug that would cause this was fixed in SP4.

    Can you confirm which service pack level you are experiencing this at ?

     

  • December 2, 2005 at 3:17 pm

    #607540

    I have to be cautious w/ posting code due to business sensitivity, but I can give you a "shell" example. Note: the procedures afflicted by this crazy problem varies -- different procedures bomb at different times. I should have mentioned that initially.

    Anyway, each procedure is set up like this:

    CREATE PROCEDURE dbo.spGetMemberInfo

    (

     @AppID  int

    )

    AS

    SELECT

       1   AS Tag,

       NULL   AS Parent,

       NULL   AS [RESULTS!1],

       NULL   AS [MEMBER!2!FIRSTNAME]

    UNION ALL

    SELECT 2, 1, NULL,

       A.FirstName

    FROM tbl_Members WHERE AppID = @AppID

    FOR XML EXPLICIT

    GO

  • December 2, 2005 at 3:18 pm

    #607541

    I'll have to check into that, I'm not sure if SP4's on that server or not...

  • December 3, 2005 at 1:46 pm

    #607581

    Just a follow up. This issue seems to boil down to 2 things:

    - Service Pack 4 supposedly addresses/fixes this issue

    - In case it doesn't, there's a workaround suggested at this URL:

    http://www.mcse.ms/post2588494.html

    I'll keep my fingers crossed and hope this puts the issue to bed for good!

    Cheers,

    Darren

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply