Oracle linked server invalid table name

linked-server

i have created a linked server from sql server and i am now trying to access the table in oracle using the following trigger to insert data into oracle. i cannot refer the table name in oracle. getting the intellisense as invalid tablename.

CREATE TRIGGER ifs_limss  ON  Culligan_Dev.dbo.C_LIMS_IFST   AFTER INSERT AS 
BEGIN

SET NOCOUNT ON;

INSERT INTO IFS.IFSAPP.C_LIMS_IFS
    (id, [name])

SELECT I.id, I.[name]
FROM INSERTED AS I

-- Insert statements for trigger here

END
GO

Best Answer

With a linked server reference you must use the four part name format, for instance SELECT * FROM server.database.schema.object. In your example it will be looking for an object called IFSAPP.C_LIMS_IFS in a local database called IFS. If that is your database name then you need to prepend the server name, if that is the linked server name then you need to insert the database name after it.

In some linking schemes you attach directly to one database not the whole server, in which case you can leave the database to default, but you must give all four parts just with that one blank, perhaps IFS..IFSAPP.C_LIMS_IFS is what you need.

To get better help you need to edit some further detail into your question:

  • What process did you follow to link the servers? Perhaps include the SQL server statements, with authentication detail removed of course, or a screenshot of you used a GUI.

  • What are the server, databases, and schema names, in Oracle, and the name you have to the looked server in SQL Server? (I've tried to guess a bit from your current SQL statements but if they are wrong my guess may be). Including the connection details mentioned above may implicitly include this information.

Also, I'm not sure I would expect intellisense to work well over linked servers so perhaps be wary of relying on that.

Related Question