Microsoft SQL-DMO error 7405 on linked server query

I am attempting to execute a query on a linked oracle server. The link
is fine and I can execute the query
from query analyser but when I create the stored procedure I get this
error:
Microsoft SQL-DMO (ODBC SQL State:42000)
Error 7405: Hetergenous 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 have looked through numerous posting on the subject and still
cannot resolve the issue.
here is the code
CREATE PROCEDURE [DBO].[TEST1] AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select sid from ......
GO
After this I attempted to alter the procdure still with no effect and
finally I issued the sp_configure 'user options',
'32' do set the default and still no luck does anyone have any other
suggestions.Try removing the GO after setting the ANSI NULLS and WARNINGS.|||ehwood@.msn.com (Eric Wood) wrote in message news:<43c03fab.0402191320.27124b8e@.posting.g
oogle.com>...
> I am attempting to execute a query on a linked oracle server. The link
> is fine and I can execute the query
> from query analyser but when I create the stored procedure I get this
> error:
> Microsoft SQL-DMO (ODBC SQL State:42000)
> Error 7405: Hetergenous 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 have looked through numerous posting on the subject and still
> cannot resolve the issue.
> here is the code
> CREATE PROCEDURE [DBO].[TEST1] AS
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
> select sid from ......
> GO
> After this I attempted to alter the procdure still with no effect and
> finally I issued the sp_configure 'user options',
> '32' do set the default and still no luck does anyone have any other
> suggestions.
Well problem solved and lesson learned. The error I was receiving was
due to a bug in enterprize manager knowledge base 296769, create the
stored procedure using query analyiser and all is well.
Eric