servers and problem with ANSI_NULLS and ANSI_WARNINGS

Hello everybody,

I've configured linked server from MS SQL 7.0 to Oracle 9.2. Next I've created trigger on table in MS SQL Database. Application which I'm using inserts data to this table and should to run this trigger. But after insertion of data I can see following messages in my logs:

SQL Server User Error: 7405, State 1, Severity: 16, Message: 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.

...and trigger doesn't do nothing ...

I red that I should use special settings for connection via linked servers.

SET ANSI_NULLS on
SET ANSI_WARNINGS on

...but I have found this in examples about procedures not about triggers. So my question is : is it possible to solve this problem if I use triggers ? And if it's possible, how can I do this ?

Thanks for your help.

Pawel.You should use like following:

set ANSI_Nulls on
go
create /*or alter */Procedure uspRefreshTable
@.TableName sysname
as
set ANSI_Warnings on
declare @.SQL nvarchar(2000)
...

Refer to books online for more information.|||Originally posted by Satya
You should use like following:

set ANSI_Nulls on
go
create /*or alter */Procedure uspRefreshTable
@.TableName sysname
as
set ANSI_Warnings on
declare @.SQL nvarchar(2000)
...

Refer to books online for more information.

Hi,

Yes, it's true but for procedures not for triggers. I've found this in example. I am looking for some example about triggers.

Cheers,

Pawel|||Just insert the code before :
CREATE TRIGGER...|||Originally posted by Satya
Just insert the code before :
CREATE TRIGGER...

It doesn't work. I tried this before I send email to dbforums :-(

Has anyone idea how to do it ?

Pawel