Despite the hundreds of stored procedures I have written that contain heterogeneous queries I always forget to set ANSI_NULLS and ANSI_WARNINGS. If you see the following error,

Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_Warnings options to be set for the connection. This ensures consisten query semantics. Enable these options and then reisse your query.

Just insert the following commands before creating/updating your STORED PROCEDURE.

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO CREATE PROCEDURE spStoredProdc AS  ... go
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

In the case of CUSTOM FUNCTIONS you can do the same,

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO CREATE FUNCTION dbo.functionName  ...
END