BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing Linked Server Objects
Applies to: Microsoft SQL Server 2000 Standard Edition
Article ID : 296769
Last Review : October 3, 2003
Revision : 1.0
This article was previously published under MSKB: Q296769
SYMPTOMS
When you use SQL Server 2000 Enterprise Manager (SEM) to create a stored procedure containing queries that use linked server objects, you may receive the following error:
Error 7405: 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.
CAUSE
By default, SQL Server 2000 Enterprise Manager sets ANSI_NULLS to OFF before creating a stored procedure.
WORKAROUND
To work around this problem, explicitly set ANSI_NULLS to ON in the stored procedure property dialog box as shown below:
Set ANSI_NULLS ON
Go
see:
http://support.microsoft.com/default.aspx?scid=kb;en-us;296769