I have a stored procedure that is getting the following error when executed via an application :
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
This stored procedure has a
SELECT statement in it that joins to a linked server table to return results. If you execute this stored procedure directly in SMS, there is no problem whatsoever. If I execute the stored procedure via a script through an ODBC connection, then there is no problem. The only way to generate the error is when the application tries to execute it.
When viewing the application, there are no errors. In fact, it looks like it runs successfully, except there are no results. Only way we were able to identify this error is through running a SQL Trace.
The trace shows that none of the proc is ever actually running, instead it is just erroring immediately with that error. Trace also shows that it is not executing in a different way (such as
exec sp_executesql or something like that). I can see the stored proc start executing, then get the error, and then stop.
I have researched this a ton and the answers seem simple enough, but nothing has worked.
I tried adding
SET ANSI_NULLS ON &
SET ANSI_WARNINGS ON to the stored proc, even moving it around different places in it. But never able to get it to succeed
Also, tried adjusting the database level settings -
ALTER DATABASE [NGDevl] SET ANSI_NULL_DEFAULT OFF WITH NO_WAIT GO ALTER DATABASE [NGDevl] SET ANSI_NULLS OFF WITH NO_WAIT GO ALTER DATABASE [NGDevl] SET ANSI_WARNINGS OFF WITH NO_WAIT GO
But did not work, even after rebooting the SQL Instance.
The only other thought I had would be to have the application execute the proc with the settings part of that, so like ---
SET ANSI_NULLS ON;SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'
But unfortunately, we dont have control over the application to try that.
Also, recreated the stored procedure so that it pulled all of the data needed from the linked server over into a temp table so it was local, and then joined to there, but same issue.
Do you guys have any ideas or something to try here. The error seems like it should be something pretty simple to fix, just not sure what I am missing.