Four-part naming for Interbase linked server query

Posted on 2004-11-19
Last Modified: 2008-01-09
I've set up a linked server to an external InterBase 7 server in Enterprise Manager. I can see all of the tables and can query them using OPENQUERY(), however I can't seem to get four-part naming to work on this database.

Does anyone have any experience connecting to InterBase through MS SQL Server that might know what info I need for catalog and schema in the four-part naming convention or if it is even possible to query InterBase that way?

I executed the following from query analyzer:

DBCC TRACEON(7300, 3604)


And it showed NULL for TABLE_CAT and had nothing in the column for TABLE_SCHEM.

The server is linked using OLE DB for ODBC through an Easysoft ODBC driver, if that makes a difference.

Since this seems like a pretty specialized answer, I'm offering max points. Thanks in advance!
Question by:Graniteloan
    LVL 6

    Expert Comment

    LVL 6

    Expert Comment

    Don't think the problem is on the Easysoft ODBC Driver.

    What is the configuration on the link?

    Author Comment

    I already tried the driver from ibprovider. I have a good link to the database, just can't get four-part naming to work. What I'm wanting to know is what Interbase names I need to use in SQL Server for schema and catalog (the databaseName and userName part). I tried using the name of the DB and sysdba for those, but they didn't work.

    I noticed that doing the same thing to Oracle doesn't seem to use those names, just place holders (serverName..tableName). That didn't work for me either with Interbase.

    Pedros7 - I'm not sure what configuration info you're asking for. I have a system DSN setup using the Easysoft ODBC driver for Interbase and am currently using that name in the linked server dialog on the SQL Server using OLE DB provider for ODBC.
    LVL 6

    Expert Comment

    OK, It seems its not set up as a linked server!
    Have a look at this link to connect the Interbase to SQL Server:


    LVL 6

    Expert Comment

    LVL 6

    Accepted Solution

    In the case these are infrequent queries, you vcan use ad-hoc names: OPENROWSET and OPENDATASOURCE!
    see bellow the different ways to access the data:


    Accessing External Data
    To access data from an OLE DB data source, provide Microsoft® SQL Server™ 2000 with the following information:

    The name of the OLE DB provider that exposes the data source.
    Any information the OLE DB provider needs to locate the source of the data.

    Either the name of an object that the OLE DB data source can expose as a rowset, or a query that can be sent to the OLE DB provider that will cause it to expose a rowset. The objects that can be exposed as rowsets are known as remote tables. The queries that generate rowsets are known as pass-through queries.

    Optionally, you can supply SQL Server with valid login IDs for the OLE DB data source.
    SQL Server 2000 supports these methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements: the linked server name and the ad hoc computer name.

    Linked Server Names
    A linked server is a virtual server that has been defined to Microsoft® SQL Server™ 2000 with all the information needed to access an OLE DB data source. A linked server name is defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Local SQL Server logins are then mapped to logins in the linked server using sp_addlinkedsrvlogin. Remote tables can then be referenced by using the linked server name:

    As the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a rowset.

    As an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned rowset can then be used as a table or view reference in a Transact-SQL statement.

    Ad Hoc Names
    An ad hoc name is used for infrequent queries against OLE DB data sources that are not defined as a linked server name. In SQL Server 2000, the OPENROWSET and OPENDATASOURCE functions provide connection information for accessing data from OLE DB data sources.

    OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than a few times, define a linked server. Neither OPENDATASOURCE nor OPENROWSET provide all of the functionality of linked server definitions, including security management and the ability to query catalog information. Each time these functions are called, all connection information, including passwords, must be provided.

    OPENROWSET and OPENDATASOURCE appear to be functions; however, they are macros and do not support supplying Transact-SQL variables as arguments.

    The OPENROWSET function can be used with any OLE DB provider that returns a rowset, and can be used anywhere a table or view reference is used in a Transact-SQL statement. OPENROWSET is specified with:

    All the information needed to connect to the OLE DB data source.

    Either the name of an object that will generate a rowset, or a query that will generate a rowset.
    The OPENDATASOURCE function provides connection information as part of a four-part object name. This function supports only OLE DB providers that expose multiple rowsets using the catalog.schema.object notation. OPENDATASOURCE can be used in the same Transact-SQL syntax locations a linked server name can be used. OPENDATASOURCE is specified with:

    The name registered as the PROGID of the OLE DB provider used to access the data source.

    A connection string that specifies the various connection properties to be passed to the OLE DB provider. The connection string syntax is a sequence of keyword-value pairs. The basic syntax is defined in the Microsoft® Data Access Software Development Kit, and each provider documents the specific keyword-value pairs it supports. For more information about connection strings, see OPENDATASOURCE.
    Accessing Linked Servers
    After a linked server is created using sp_addlinkedserver, it can be accessed using:

    Distributed queries. Accessing tables in the linked server through SELECT, INSERT, UPDATE, and DELETE statements using a linked server-based name.

    Remote stored procedures. Stored procedures can be executed against the linked server using a four-part name.
    Servers running an instance of SQL Server can be defined as a remote server using sp_addserver. The remote server then can be referenced in remote stored procedure calls. The remote server component is maintained as a compatibility feature for existing applications.

    As applications are ported to SQL Server 2000, they may need to run for a period of time with some new code using distributed queries against a linked server definition and some legacy code using a remote server definition. Both linked servers and remote servers use the same name space, so either the linked server or the remote server definition has to use a name that is different than the network name of the server being accessed remotely. Define one of the entries with a different server name, and use sp_setnetname to associate that definition with the network name of the remote server.

    Note  The examples in this section use system stored procedures to configure linked servers because these system stored procedures succinctly show the parameters used. However, SQL Server Enterprise Manager also supports configuring linked servers. For more information, see Configuring Linked Servers.

    info found on:


    Author Comment

    Thanks for the help guys, but none of that really answered my question. I awarded pedros7 the points since he came the closest to the information I was looking for. I was mainly looking for how other people had used four-part naming to connect to an IB server through SQL Server. Specific examples of what others used for schema and catalog in that four-part name. If anyone runs across this in the future and can provide some information, please add it here for everyone's benefit.

    I'm using OPENQUERY for now and the performance seems to be decent. I hope to eventually have everything on SQL server, so this should hold me over until then.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now