Hi All,
From my SQL 7 Server I have a Linked Server pointing to an Informix Dynamic Server 9.3 Database. I can See the Database and all its Tables as well as running select statements for tables in the Informix DB from the SQL Database through Query Analyser. My problem is however whenever I run an Insert statement on the Informix tables within Query Analyser from my SQL 7 Server...
insert test(NameID, Contact, Position)
Values('NAR', 'Contact', 'Postion')
I get the following Error Message....
Server: Msg 7399, Level 16, State 1, Procedure ARAUTHORITY_TEST, Line 12
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
I am using Query Analyser for testing the data transfer from SQL to Informix as I have a trigger that sends information from SQL 7 to Informix. This trigger produces the same error. The syntax for the trigger is as follows...
CREATE TRIGGER ARAUTHORITY_TEST ON NAR.dbo.Test
FOR INSERT AS
IF
(Select NameID from Inserted) = 'NAR'
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO AUTHTEST.Authority.auth.nar_test(NameID, Contact) Values ('NAR', 'Person')
IF @@error <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Is there something in my syntax that is causing this error or is it some configuration setting in either SQL or Informix??
Regards
Anthony
From my SQL 7 Server I have a Linked Server pointing to an Informix Dynamic Server 9.3 Database. I can See the Database and all its Tables as well as running select statements for tables in the Informix DB from the SQL Database through Query Analyser. My problem is however whenever I run an Insert statement on the Informix tables within Query Analyser from my SQL 7 Server...
insert test(NameID, Contact, Position)
Values('NAR', 'Contact', 'Postion')
I get the following Error Message....
Server: Msg 7399, Level 16, State 1, Procedure ARAUTHORITY_TEST, Line 12
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
I am using Query Analyser for testing the data transfer from SQL to Informix as I have a trigger that sends information from SQL 7 to Informix. This trigger produces the same error. The syntax for the trigger is as follows...
CREATE TRIGGER ARAUTHORITY_TEST ON NAR.dbo.Test
FOR INSERT AS
IF
(Select NameID from Inserted) = 'NAR'
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO AUTHTEST.Authority.auth.nar_test(NameID, Contact) Values ('NAR', 'Person')
IF @@error <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Is there something in my syntax that is causing this error or is it some configuration setting in either SQL or Informix??
Regards
Anthony