SET ANSI_NULLS ON Problem

[princessk1]

I have created a stored procedure that gets data from different databases on different servers. I have added the line

SET ANSI_NULLS ON
CREATE PROCEDURE [dbo].[usp_UpdateActiveLots] AS...

If I take out the line SET ANSI_NULLS ON - I get the error 7504 - Heterogeneous queries require ANSI_NULLS & ANSI_WARNINGS settings to be set for the connection.

With the line in, I get the error
ALTER PROCEDURE MUST BE THE FIRST STATEMENT IN THE QUERY BATCH

What am I missing?

Thanks

 

[SQLBill]

The clue is that the error says ALTER PROCEDURE must be the first statement in the query BATCH. A batch is defined by the GO. Try this:

SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_UpdateActiveLots] AS...

-SQLBill

 

[osjohnm]

Hi princessk1

SQLBill is correct about the GO part but if you look at your error message in your post:

"Heterogeneous queries require ANSI_NULLS & ANSI_WARNINGS settings to be set for the connection."

You actually need to set ANSI_WARNINGS ON as well, so the statement should be as follows:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [dbo].[usp_UpdateActiveLots] AS...

This is a bug in Enterprise Manager in SQL 2000. Here is the link to the support article at Microsoft:

http://support.microsoft.com/default.aspx?scid=kb;en-us;296769

Hope this helps

John

 

[osjohnm]

Hi princessk1

One thing I forgot is that because of the Enterprise Manager bug you need to create procedures that use linked servers in Query Analyser, therefore execute the statement in my previous post in Query Analyser.

John