Query to linked server:Oracle; problems with ANSI_NULLS;ANSI_WARNINGS

[A.M. de Jong]

When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation
error
when saving this procedure. (Not when compiling; it has no errors)

Server: Msg 7405, Level 16, State 1, Line 1
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.

When I create a dynamic SQL statement then I can save this stored procedure
when I run the stored procedure this same error happens.

What do I have to do.

Arno de Jong, The Netherlands

[Simon Hayes]

"A.M. de Jong" <ar****@wxs.nl> wrote in message
news:bq**********@reader11.wxs.nl...

When I perform a query on a linked Oracle server in the Query analyser I
have no
prboblem' to perform this query.
However, when I create this query in a stored procedure I get a compilation error
when saving this procedure. (Not when compiling; it has no errors)

Server: Msg 7405, Level 16, State 1, Line 1
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.

When I create a dynamic SQL statement then I can save this stored procedure when I run the stored procedure this same error happens.

What do I have to do.

Arno de Jong, The Netherlands

Have you tried putting SET ANSI_NULLS ON and SET ANSI_WARNINGS ON at the
start of your stored procedure, ie. in the procedure code itself? QA sets
these on automatically, but other connections may not.

Simon