SQL Server Performance Forum – Threads Archive

Warning: The join order has been enforced…

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!

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

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.

Sorry that it didn’t help. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] Maybe you have more luck on some ASP related sites. I don’t know of a way to supress such messages in SQL Server. – Frank Kalis SQL Server MVP http://www.insidesql.de</a>

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!

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.

Hey Frank, as an FYI… While I believe the "err.number=0" will work, the more graceful method is err.clear 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=’

‘ /> Take it easy… <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I’m not a great ASP developer, but can’t you use something like <pre id="code"> if err.number<>0 then err.number=0 </pre id="code"> – Frank Kalis SQL Server MVP http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote">

]]>