Re: Write trigger to update linked server

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Larry Chan (anonymous_at_discussions.microsoft.com)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 01:44:14 -0700

I have recreate the trigger with

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
go

CREATE TRIGGER sm_inv_list_update_trg
ON inv_list
FOR UPDATE
AS
        
begin
UPDATE T4.eFrontData.dbo.partcost
SET CostEach =
a.std_cost+a.std_direct_dolrs+a.std_ovhd_dolrs+a.std_util_d
olrs
FROM inserted a, inv_master b, T4.eFrontData.dbo.partcost c
WHERE a.part_no = b.part_no
AND a.location = 'MAINWH'
AND b.status = 'P'
AND a.part_no = c.OrdPart_ID
end

But still got the error when I update one of the record.
Also I have set the Attribute of Connections in the Linked
Server.
>-----Original Message-----
>Try recreating the trigger from SQL Server Query
Analyzer, with the
>specified settings ON. For example:
>
>SET ANSI_NULLS ON
>SET ANSI_WARNINGS ON
>GO
>CREATE TRIGGER....
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>"larrychan" <anonymous@discussions.microsoft.com> wrote
in message
>news:941201c496ef$80d8d2a0$a601280a@phx.gbl...
>I write a trigger on a table and update another table in a
>linked server. There is an error message .
>
>SQLSTATE = 37000
>[Microsoft][ODBC SQL Server Driver][SQL Server]
>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 quer...
>
>
>Anyone can help on this?
>
>
>.
>

Relevant Pages

  • Re: NEED TO CALL AS400 S.P
    ... That is to use a trigger on a table and perform an INSERT/UPDATE which will execute the trigger. ... What an error message! ... > what your OLE DB provider is feed the AS400 with? ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.data.oledb)
  • Re: How to syncronize two tables?
    ... What is the error message? ... Pro SQL Server 2000 Database Design - ... > want to sync them with trigger. ...
    (microsoft.public.sqlserver.programming)
  • Re: Error on Trigger Launch
    ... It was an error message from Microsoft SQL Server Managment Studio ... trigger, ... If an UPDATE statement affects multiple rows, ...
    (comp.databases.ms-sqlserver)
  • Re: help with DDL trigger. Moved from other newsgroup.
    ... something about this trigger causes a prior table "tblA" to ... Here is the error message that I get on inserting into tblA with the ... you use XQuery, these settings must be on: ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Error on Trigger Launch
    ... trigger, ... the row unique or they alter multiple rows" ... That does not sound like a SQL Server error message. ...
    (comp.databases.ms-sqlserver)