Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

Question

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.

Answer 1

The issue is specific to ANSI_WARNINGS as the value of ANSI_NULLS is stored with the definition of the Stored Procedure when it is created. Just run:

SELECT * FROM sys.sql_modules;

To see the difference between ANSI_NULLS and ANSI_WARNINGS, run the following:

-- Check current values (SSMS defaults to ON for both unless changed in 
-- Tools -> Options -> Query Execution -> SQL Server -> ANSI
SELECT SESSIONPROPERTY('ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY('ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1

SET ANSI_NULLS OFF;
SET ANSI_WARNINGS OFF;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 0   0

EXEC('
CREATE PROCEDURE ##SessionSettings
AS
SELECT SESSIONPROPERTY(N''ANSI_NULLS'') AS [AnsiNulls],
       SESSIONPROPERTY(N''ANSI_WARNINGS'') AS [AnsiWarnings];
');

EXEC ##SessionSettings;
-- returns: 0   0   (as expected)

SET ANSI_NULLS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   0   (ANSI_NULLS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   0   (ANSI_NULLS is still OFF due to being saved with the proc definition)

SET ANSI_WARNINGS ON;

SELECT SESSIONPROPERTY(N'ANSI_NULLS') AS [AnsiNulls],
       SESSIONPROPERTY(N'ANSI_WARNINGS') AS [AnsiWarnings];
-- returns: 1   1   (ANSI_WARNINGS is back ON for the session)

EXEC ##SessionSettings;
-- returns: 0   1   (ANSI_WARNINGS is now ON due to NOT being saved with the proc definition)

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:

SET ANSI_WARNINGS ON;exec sp_storedproc 0,0,'A','0001'

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 named sp_storedproc that is just a wrapper that does the following:

CREATE PROCEDURE sp_storedproc
(
  @Param1 INT,
  @Param2 INT,
  @Param3 VARCHAR(50),
  @Param4 VARCHAR(50)
)
AS
SET ANSI_WARNINGS ON;

EXEC sp_storedprocReal @Param1, @Param2, @Param3, @Param4;

I have tested the above wrapper proc after reproducing the stated error and it does resolve the issue.

Answer 2

The reason for this behavior is that different DAL drivers set different connection options by default, and when the application doesn't initialise its own connection properly, there isn't much you can do.

The only solution for your case that I can think of is to wrap the query into a dynamic SQL and set all options needed in it:

create procedure dbo.TestAnsiOnLink
with execute as owner as

declare @sql nvarchar(max);

set @sql = N'set ansi_nulls, ansi_warnings on;
select top 100 *
from [LinkSrv].[LinkDB].dbo.RemoteTable s
    inner join [LocalDB].dbo.LocalTable t on t.RecordID = s.RecordID;';

exec (@sql);

return;
go

The main issue with this approach is that dynamic SQL executes under caller's credentials, so if your users aren't privileged enough to query the link directly, you'll have to add with execute as owner (or some other being with sufficient permissions) clause. Sometimes it might not be desirable, but... it's still much better than a stored procedure not working.