FDC2005
asked on
How to suppress "The join order has been enforced because a local join hint is used."
Is there a way with a connection setting to suppress "The join order has been enforced because a local join hint is used.", similar to how "SET ANSI_WARNINGS OFF" suppresses "Null value is eliminated by an aggregate or other SET operation."? The reason is that a user wants to use JMP to call into a stored procedure and get a results set, and JMP is treating this warning as an error and halting.
Notes and requests:
1) Despite many suggestions on the web, "SET ANSI_WARNINGS OFF" does NOT suppress "The join order has been enforced because a local join hint is used", so please do not suggest this as an answer.
2) Please do not suggest that we rewrite all the stored procedures to eliminate join hints. We have spent thousands of hours optimizing the database code and the only join hints in it are the ones necessary to keep SQL Server from occasionally picking very bad query plans under particular load situations.
3) Please do not suggest that we rewrite the user's application to handle multiple result sets... the end user's application is simply a SQL call to a stored procedure that they want to execute inside JMP, and JMP is the one treating this warning as an error.
Thanks!
-Frank.
Notes and requests:
1) Despite many suggestions on the web, "SET ANSI_WARNINGS OFF" does NOT suppress "The join order has been enforced because a local join hint is used", so please do not suggest this as an answer.
2) Please do not suggest that we rewrite all the stored procedures to eliminate join hints. We have spent thousands of hours optimizing the database code and the only join hints in it are the ones necessary to keep SQL Server from occasionally picking very bad query plans under particular load situations.
3) Please do not suggest that we rewrite the user's application to handle multiple result sets... the end user's application is simply a SQL call to a stored procedure that they want to execute inside JMP, and JMP is the one treating this warning as an error.
Thanks!
-Frank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have come to the conclusion that there is no way to suppress this message, therefore I am closing the question. If there is a way to suppress the warning, I would be happy to hear about it.
The solution is helpless in view. OPTION(FORCE ORDER) is impossible (syntax error).
option(force order) can be used in top-level select. It suppresses these warning messages from views a subqueries. I needed it again now and thank God it seems it doesn't affect hints used in views, subqueries and 'with' queries used anywhere inside.
OPTION (FORCE ORDER)
which does not produce this error, while still enforcing join order.