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

server Trigger

Hi there,

I'm pretty new to SQL and am having some porblems with a linked server.
I have a table on a SQL server which stores employee information.
I also have a view on a linked server which stores the same information.

What I would like to happen is, whenever the view changes on the linked server I want the information to be changed in the table on my server.

I've been trying to write a trigger to do this, but have had no luck so far.

This is the trigger I've written:

CREATE TRIGGER fnChangeData

ON PSREP..SYSADM.PS_ML_UK_EXTR_AMS

FOR UPDATE

AS

SET XACT_ABORT ON

UPDATE t

SET
LAST_NAME_SRCH = i.LAST_NAME_SRCH,
FIRST_NAME_SEARCH = i.FIRST_NAME_SEARCH

FROM Employee t JOIN inserted i ON t.EMPLID = i.EMPLID

And the error I get:

Server: Msg 117, Level 15, State 1, Procedure fnChangeData, Line 5
The object name 'PSREP..SYSADM.' contains more than the maximum number of prefixes. The maximum is 2.

Is this because you can't run a trigger on a linked table? I've tried using openquery as well, but with no success.

Can anyone help me?

Much appreciated,

SimonYou probably need to write your trigger on the other side, - against the view of your linked server. This means that you have to create a linked server on the other side as well. Also, avoid defaults and fully qualify object references (servername.databasename.owner.object)|||Problem is I can't create a trigger on the other side, I only have access to it as a linked server.

Is there any way I can do it from this side?

Thanks,

Simon|||You can create a stored procedure that selects the data from theview on the first server, inserts it on the second one in a temporary table and from that point on you can update the table using the temporary table. Depending on the time you want to allow between an update on the view and the necessary update on the table, you can create a job that will run on interval. It's quite a way around and probably not improving the performance of your server, but you can't fire a trigger from one server when data is changed on another.|||Pumping the entire set of data from one server to another on a periodic basis maybe...how should I put it?.. You can probably slightly alter the previous reply and have a permanent table on your side. Then with your scheduled task do an INSERT with LEFT OUTER JOIN from the view to your table where the key_field in your table is null.|||That sounds like a good enough solution to me, thanks.

I've never written a stored procedure before tho, and could do with some hints?

Thanks in advance,

Simon