SQL Server Performance

Warning: The join order has been enforced...

Discussion in 'General DBA Questions' started by darthjure, May 23, 2005.

  1. darthjure New Member

    I've got a stored procedure that uses a join hint like this:

    (nolock, index(BoundDateMod_Idx))

    When I run it I get the following message:

    Warning: The join order has been enforced because a local join hint is used.

    Is there a way to turn this warning off? Assume that I want to keep the join hint.

    The reason I want to get rid of the warning is an ASP page that calls this stored procedure. I think ASP has ignored these warnings in the past, but now if I get a warning the page continues as though no rows were returned from the SP (even though rows were returned).

    The good thing is that the warning only appears the first time the stored procedure is run, so I can run it a second time and the ASP page will work. But if possible, I'd rather just turn off those warnings. I thought it could be done by adding this to the begining of my stored procedure:

    Set Ansi_Warnings OFF

    Adding that line doesn't get rid of my warning. Any ideas?

    Many thanks in advance for your help!
  2. FrankKalis Moderator

    I'm not a great ASP developer, but can't you use something like


    if err.number<>0 then err.number=0



    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  3. darthjure New Member

    That's a great idea, but it won't work here. It's not an error - it's a message/warning. I wish I was getting an error back - I could handle that.

    If I run the stored procedure in Query Analyzer, I get a table on the Grids tab and a warning on the Messages tab. Once ASP sees that message, it doesn't look for the table to come back and it also doesn't give any kind of error message.

    Thanks for your response.
  4. FrankKalis Moderator

    Sorry that it didn't help. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />Maybe you have more luck on some ASP related sites. I don't know of a way to supress such messages in SQL Server.<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  5. darthjure New Member

    Wow. Well, that may be the final answer on this. I was hoping I could add some line to my stored procedure (like "Set NoCount On" or "Set Ansi_Warnings Off" - but neither of these work) or that maybe there was some SQL server setting that would do it.

    Thanks again for your help!
  6. darthjure New Member

    I was surprised to see that the warning is being considered another recordset. I found this knowledge base article explaining it:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;245179

    Here's what I added to my code:

    Rs.Open "Execute " & StoredProcedureName

    dim myCounter
    myCounter = 1

    Do While rs.state = 0 AND Not (rs is nothing) AND (myCounter < 40)
    myCounter = myCounter + 1
    Set rs = rs.NextRecordset
    Loop

    I've never seen it go through more than 4 recordsets, but I put the counter in just in case - I don't want to get stuck in an infinite loop.

    I also read that I could add "OPTION (FORCE ORDER)" to the stored procedure (beginning or end) to suppress the warning, but I haven't tried it. It may impact performance.
  7. ChrisWoodruff New Member

    Hey Frank, as an FYI...<br />While I believe the "err.number=0" will work, the more graceful method is<br />err.clear<br /><br />while will clear any error codes. I also wouldn't bother checking the existing number, just the clear. If it's already zero, it will still be zero... otherwise it becomes zero. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Take it easy...<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />I'm not a great ASP developer, but can't you use something like<br /><pre id="code"><font face="courier" size="2" id="code"><br />if err.number&lt;&gt;0 then err.number=0<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Share This Page