Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 30 '12 at 13:56

This question came from our site for professional and enthusiast programmers.

add comment

1 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.

share|improve this answer
    
I was given inside the store procedure like this create procedure Proc_DayShift @Dates varchar(10) as set ansi_warnings on ...", While executing the store procedure it's showing error as "Must declare the varaibale @Dates" –  user1157846 Jan 30 '12 at 11:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.