Heterogeneous queries error using stored procedure

Question

Stored procedure

ALTER    PROCEDURE Proc_DayShift
AS
insert into #Punching_History SELECT * FROM SERVER2.MARS.DBO.TABLE1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
SET ANSI_WARNINGS OFF 
GO

The above stored procedure is showing error as

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.

I added the server1 from server2 using linked server, when I run the query like this

SELECT * FROM SERVER2.MARS.DBO.TABLE1

It's executing and showing results.

When I run the query using stored procedure, it is showing this error message.

How to rectify this issue. what wrong in my query...?

Answer 1

The message mentions two settings. The first setting, ansi_nulls, is remembered from when the stored procedure was created. Make sure it's on when you run create procedure:

set ansi_nulls on
go
create procedure YourProc as ...

The second setting, ansi_warnings, should be enabled on the connection that calls the procedure:

set ansi_warnings on 
exec YourProc

Or alternatively, set ansi_warnings inside the procedure:

create procedure YourProc 
as
set ansi_warnings on
...

For more information see this MSDN article:

Stored procedures execute with the SET settings specified at execute time except for SET ANSI_NULLS and SET QUOTED_IDENTIFIER. Stored procedures specifying SET ANSI_NULLS or SET QUOTED_IDENTIFIER use the setting specified at stored procedure creation time. If used inside a stored procedure, any SET setting is ignored.