Actually, I forgot to include a GO.

Try this

Set ANSI_NULLS ON
Go
Set ANSI_WARNINGS ON
Go

Create procedure <procname>

AS
....


-----Original Message-----
From: John Thomas
Sent: October 31, 2002 2:25 PM
To: LazyDBA.com Discussion
Subject: RE: Linked Server Query Execution Problem.


Parag:

You need to set those ansi settings outside the text of
the procedure.

Set ANSI_NULLS ON
Set ANSI_WARNINGS ON

Create procedure <procname>

AS
....



----
John

-----Original Message-----
From: Parag Kulkarni [mailto:[Email Address Removed] October 31, 2002 2:19 PM
To: LazyDBA.com Discussion
Subject: Linked Server Query Execution Problem.


Hi List,

I have added a SQL 2000 linked server on one of the
servers.When I try to write a SP that inserts data
into the local server from the linked server table it
doesnt allow me to compile the SP and gives the error
below.

"Error 7405 : Heterogeneous queries require the
ANSI_NULLS and ANSI_WARNINGS options to be set for
the connection.This ensures consistent query
symantics.Enable these options and then reissue your
query."

However If I run the query in the Query Analyser it
works perfectly.
I tried to set the options to On/Off in the SP but it
still gave the same error.May be I am missing
something here.Does anybody know where I am going
wrong.

Thanks,
Parag


__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page