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