In SQL SERVER 2005 I tried this:
- EXEC sp_addlinkedserver 'SAMPLESERVER', 'Oracle', 'MSDAORA', 'SAMPLESERVER'
- EXEC sp_addlinkedsrvlogin 'SAMPLESERVER', 'FALSE', NULL, 'user_name', 'password'
Then I connected to remote server. So that I can do insert, update, delete, select to remote server. There is no problem for this issue. The connection is normal.
When I use update, delete, insert commands in a trigger, trigger created successfully. But for example; after inserting to a SQL SERVER table, trigger gives error. error is:
OLE DB provider "MSDAORA" for linked server "SAMPLESERVER" returned message "ORA-01041:internal error. hostdef extension doesn"t exist
my trigger is :
ALTER TRIGGER [SAMPLETRIGGER] ON
SQLSERVERTABLE AFTER UPDATE AS
BEGIN
INSERT INTO SAMPLESERVER..USERNAME.ORACLETABLE
VALUES(3,123, 'XX', 111, 'XX', GETDATE(), NULL, NULL,NULL,NULL)
END
NOTE: the insert code runs successfully without trigger.
