Error 7405: Heterogeneous queries (SQL Server)

T-SQL, SQL Server

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

Comments (1)

Make sure all your connections are closed

ASP Classic

I have had the unfortunate privilege to walk into an ASP classic application cold only to discover the person who orginilly wrote the app either didn’t know how-to or didn’t like closing their DB connections. As you can imagine this caused a bit of a headache from the application and so I write this little function that will close your known connections safely.

if IsObject(RS) then     if not (RS is nothing) then         if RS.state = 1 then RS.close         set RS = nothing     end if end if if IsObject(conn) then     if not (conn is nothing) then         if conn.state = 1 then conn.close         set conn = nothing     end if end if

Comments