[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Four-part naming for Interbase linked server query

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)
go

sp_tables_ex
'WESTONDEV'
go

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!
0
Graniteloan
Asked:
Graniteloan
  • 4
  • 2
1 Solution
 
mcp111Commented:
0
 
pedros7Commented:
Don't think the problem is on the Easysoft ODBC Driver.

What is the configuration on the link?
0
 
GraniteloanAuthor Commented:
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pedros7Commented:
OK, It seems its not set up as a linked server!
Have a look at this link to connect the Interbase to SQL Server: http://www.ibprovider.com/eng/documentation/con_to_ms.html

HTH

0
 
pedros7Commented:
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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_23xd.asp
=--==-=-=-=-=-=-


0
 
GraniteloanAuthor Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now