Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection.

Last week sometime, working on a very tight project with stringent deliverables and deadlines, its just when you don't need a reason you have one, a show stopper. And your job is failing. The error quite obscure : "Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." Of the internet/people/voices/opinions and some ON/OFF with no logical reason or supporting workable evidence. It didn't work !

I discovered something quite trivial, yet unknown.

To start with,

EXECUTE usp<<Procedure>> @LinkedServerName =<<LinkedServerName>> ,@SourceDatabaseName =<<SrcDBName>>,@SourceTableName=<<SrcTableName>> ,@TargetTableName = <<TargetTblName>>

The code was trying to call a Proc to connect to a Source and populate the target data, and was failing with the error message in context.

The procedure already had the 3 sacred declarations.

CREATE usp_Proc @Pram1 INT = 0
,@Pram1 VARCHAR(10) = NULL
AS
BEGIN
    BEGIN TRY
        SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  SET ANSI_WARNINGS ON
        SET NOCOUNT ON;

.

.

EXECUTE usp<<Procedure>> @LinkedServerName =<<LinkedServerName>> ,@SourceDatabaseName =<<SrcDBName>>,@SourceTableName=<<SrcTableName>> ,@TargetTableName = <<TargetTblName>>

I tried to play with it, turning to ON/OFF hoping that the job would run, until I bumped into, Stored Procedure Properties

The Properties for the Proc in question was

The ANSI_NULLs ans Quoted Identifier Properties were SET to FALSE. To SET the same, I executed the PROC ALTER script with the Properties ADDed AS ON

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE
@Pram1 INT = 0
,@Pram1 VARCHAR(10) = NULL
AS
BEGIN
    BEGIN TRY
        SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  SET ANSI_WARNINGS ON
        SET NOCOUNT ON;

With these SET to TRUE, the job/code succeeded and the issue was resolved.

Hope it helps.