Sql-server – Heterogeneous queries error using stored procedure

sql serversql-server-2000stored-procedures

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…?

Best Answer

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.

Related Question