Friday, July 15, 2005

What is a Four-part name in SQL Server?

When accessing a linked server in SQL Server, a four-part name should be used. This naming convention requires the 4 fields (linkserver, catalog, schema, and table) to be specified in a hierarchal format, such as:

SELECT *
FROM LINKSERVER_NAME.CATALOG_NAME.SCHEMA_NAME.TABLE_NAME

Please note the field names should be specified in UPPERCASE, especially when accessing a DB2 Database, otherwise the following error will occur:

Server: Msg 7314, Level 16, State 1, Line 1OLE DB provider 'LINKSERVER_NAME' does not contain table '"CATALOG_NAME"."SCHEMA_NAME"."TABLE_NAME"'. The table either does not exist or the current user does not have permissions on that table.OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='LINKSERVER_NAME', TableName='"CATALOG_NAME"."SCHEMA_NAME"."TABLE_NAME"'].

No comments: