Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  a trigger issue

Author  Topic  adam Starting Member 4 Posts Posted - 2008-01-09 : 05:00:10 Hi All, I have created a FOR INSERT trigger on a table (TBLCHECK_CL) on Sql Server 2005 that updates a table on Oracle via the linked server which i have created with OraOLEDB.Oracle provider. The name of linked server is "ORACLE_LNK". That trigger works great when I update the table TBLCHECK_CL from sql server management studio with insert statements -- the trigger fires and the table on Oracle is correctly updated. However, when I update the table on Sql Server from within a client application, the trigger fires but I get the following error and process does not work: "Heterogeneous queries require 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." Here is my trigger code: ----------------------------------------------------- USE [YTM08] GO SET QUOTED_IDENTIFIER ON GO ANSI_NULLS ON go ANSI_WARNINGS ON CEATE TRIGGER [dbo].[TBLCHECK_CL_I] ON [dbo].[TBLCHECK_CL] FOR INSERT AS BEGIN INSERT INTO ORACLE_LNK..SECTOR.TBLCHECK ( stock_name, stock_code, insert_date ) ( SELECT STOCKNAME, STOCKCODE, GETDATE() FROM INSERTED ) END ---------------------------------------------------------------------- Any help would be greatly appreciated. Adam