Heterogeneous queries error using stored procedure
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.