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.
Best Answer
The issue is specific to
ANSI_WARNINGS
as the value ofANSI_NULLS
is stored with the definition of the Stored Procedure when it is created. Just run:To see the difference between
ANSI_NULLS
andANSI_WARNINGS
, run the following:Putting the
SET
command inside of the stored procedure is too late; it needs to be set before the EXEC of the proc. So you were on the right track with wanting to do:However, you cannot change the app code and how it calls the proc. BUT, you can change the proc itself. Hence, you can rename the current proc to
sp_storedprocReal
and create a new stored procedure namedsp_storedproc
that is just a wrapper that does the following:I have tested the above wrapper proc after reproducing the stated error and it does resolve the issue.