ANSI_NULLS and ANSI_WARNINGS

[JefB]

(SQL 2000)
I am trying to write a stored procedure to copy a table from one server (SERV1) to another server (SERV2):

CREATE PROCEDURE [dbo].[DailyServerSync]

AS

-- Update CleanSiteData
BEGIN TRAN
DELETE FROM wbrdata.dbo.cleansitedata
INSERT INTO wbrdata.dbo.cleansitedata
SELECT *
FROM SERV1.wbrdata.dbo.cleansitedata
IF @@ERROR > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
PRINT 'CleanSiteData updated'

GO

The syntax checks out fine, but when I click the "OK" button, I get "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."

I tried the following:

CREATE PROCEDURE [dbo].[DailyServerSync]

AS

set ANSI_NULLS ON
set ANSI_WARNINGS ON

-- Update CleanSiteData
BEGIN TRAN
DELETE FROM wbrdata.dbo.cleansitedata
INSERT INTO wbrdata.dbo.cleansitedata
SELECT *
FROM SERV1.wbrdata.dbo.cleansitedata
IF @@ERROR > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
PRINT 'CleanSiteData updated'

GO

Same message.

Any idea where I am supposed to set these options?

JefB

 

[osjohnm]

Hi JefB

This is a problem with SQL Server Enterprise Manager. These is an article at Microsoft's support website about this.
Since you are using a stored procedure that contains linked objects you need to create it through Query Analyser. You need to set the ANSI_NULLS and ANSI_WARNINGS before the create statement like so:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [dbo].[DailyServerSync]

AS

-- Update CleanSiteData
BEGIN TRAN
DELETE FROM wbrdata.dbo.cleansitedata
INSERT INTO wbrdata.dbo.cleansitedata
SELECT *
FROM SERV1.wbrdata.dbo.cleansitedata
IF @@ERROR > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
PRINT 'CleanSiteData updated'

GO

It basically sets the options for that particular session/connection. SQL Server will stored the ANSI settings and you will be able to edit the procedure through Enterprise Manager once you have created it without having to reset the ANSI options.

Hope this helps

John

 

[JefB]

Perfect!

Thanks.

JefB