BUG: Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects

Article translations Article translations
Article ID: 296769 - View products that this article applies to.
This article was previously published under Q296769
BUG #: 351020 (SHILOH_BUGS)
Expand all | Collapse all

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
Create Proc spXXXX as
select * from [Linkedserver].pubs.dbo.authors
.
.
				
Another workaround is to create the stored procedure from Query Analyzer. Note that the default ANSI setting for Query Analyzer must be set to ON in order for this to work.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000.

MORE INFORMATION

SQL Server 2000 Enterprise Manager can be used to edit existing stored proceduress; it will retain whatever ANSI_NULLS settings were used when a stored procedure was initially created.

Properties

Article ID: 296769 - Last Review: December 23, 2005 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbpending KB296769

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com