Using External Entities with Physical Table Name refering a Linked Server

Using External Entities with Physical Table Name refering a Linked Server

  
If you have an external entity that is a reference to another DB Server using a Linked Server you have two options for your Physical Table Name:

1. Create a View on your DB Server that refers the destination table on the Linked Server. Define the Physical Table Name on the External Entity with the name of your recently created View.

2. Define the reference to the foreign table directly on the Physical Table Name. The syntax you should use is the following:
ServerName].[DatabaseName].[Owner].[TableName

NOTE: If you decide to use this last option, you should use an alias on every Advanced Query you use in Service Center that refers the External Entity.
For example, you should use:
SELECT TABLE_ALIAS.[ID] FROM {TABLE_LINKED} AS TABLE_ALIAS
instead of,
SELECT {TABLE_LINKED}.[ID] FROM {TABLE_LINKED}

If you don't follow this note you may get the following error:
"Error in advanced query <AdvQueryName> in <ActionName> (SELECT {TABLE_LINKED}.[ID] FROM {TABLE_LINKED}): The number name 'ServerName.DatabaseName.Owner.TableName' contains more than the maximum number of prefixes. The maximum is 3."
I would just like to point out, that using the Integration Studio, the physical table name complies with the "ServerName.DatabaseName.Owner.TableName" naming convention, and hence, it won't be required to change it. However, Microsoft SQL server requires the use of table name ALIAS when using dblink servers, as specified in the post above, in order to overcome the reported error message:

"Error in advanced query <AdvQueryName> in <ActionName> (SELECT {TABLE_LINKED}.[ID] FROM {TABLE_LINKED}): The number name 'ServerName.DatabaseName.Owner.TableName' contains more than the maximum number of prefixes. The maximum is 3."

Although in Simple Queries this problem doesn't arise, in Advanced Queries, when using foreign entities in a select statment, the use of table name ALIAS is not optional. Furthermore, the verify function will not detect this requirement and will generate Runtime errors.