Link to Borland Interbase

Question

Link to Borland Interbase

PaulSp

SSCrazy

Points: 2499
More actions
January 21, 2008 at 7:49 am

#185826

Hi
We are currently using SQL Server 2000 both for creating databases, and as an integration tool with an Oracle Server (we pull data down into SQL Server). We now have a need to pull data from a bought in Borland Interbase database (Club Runner).
Does anybody know how I might achieve this? What steps do I need to take - do I need to install the client etc? What kind of link should I use?
Many thanks
Paul

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Key DBA SSCertifiable Points: 6029 More actions · Answer 1

PaulSp,

I have not worked with Interbase systems and I am not sure if you would need an Interbase client install on your SQL Server machine.

If there is not an Interbase client available, look into whether or not there is an ODBC driver for Interbase. If one exists, you can set up a 2 step data access approach by first configuring an ODBC Connection on the SQL Server machine, using the Interbase ODBC driver. Second, use an ODBC Linked Server which will use the ODBC configuration for accessing data from your Interbase database source.

Hope This Helps

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

PaulSp SSCrazy Points: 2499 More actions · Answer 2

Hi Damon

Thanks for your reply. I've done what you suggest and am getting pretty close. I've setup an ODBC driver, and installed the interbase client software. When I click test in ODBC, the connection is successful.

My problem now is configuring the linked server. The ODBC driver is called 'CRODBC'. The server the database is on is called 'RRServer'. The username and password have been specified in the ODBC driver configuration.

I think it should look something like:

sp_addlinkedserver 'LSName', ' ', 'MSDASQL', 'SystemDSN'

so:

sp_addlinkedserver 'CR', ' ', 'MSDASQL', 'CRODBC' ???

It returns an MSDASQL error 7399 with no info. Note the ODBC tests successfully.

Any ideas where I'm going wrong?

Many thanks again

Paul

Key DBA SSCertifiable Points: 6029 More actions · Answer 3

Paul,

It looks like you are on the right track. It is good news if your System DSN tests successfully in the ODBC Admin tool.

The syntax *always* gets me with linked servers.

From the syntax you provided, everything appears to be correct. Double check and make sure that case matches in the Linked Server strings to those in the System DSN.

Regarding the Error 7399 ...

A few suggestions from:

Help on Error 7399: OLE DB provider 'MSDASQL' reported an error

http://www.dbforums.com/showthread.php?t=481470

Possible Problems:

Possible permissions issue ... Is the account used to start SQL Server and SQL Server Agent a domain account with permissions to the remote machine?

Possible solution, kind of a vague response, but something to go on ... I found the problem. It looks like the Service acct that SQL Server was running under was set to interactive. I changed that to a Service acct in the admin role andeverything worked!

--

You may also be able to get more detailed information from either the SQL Errorlog and/or Windows Event Log entries.

As a long shot, you may also need to verify your MDAC version and components.

Microsoft provide a tool to identify the MDAC version. Download the MS component checker from http://www.microsoft.com/downloads/details.aspx?familyid=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en

--

Finally there is a Microsoft Knowledge base article, and it looks to be permisions related as well.

PRB: SQL Server Link to Remote Database Fails with Error 7399

http://support.microsoft.com/default.aspx/kb/241267

--

Hopefully something from above will get your linked server working for you.

Keep us posted on your progress.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

PaulSp SSCrazy Points: 2499 More actions · Answer 4

Hi Damon

Many thanks for all your help. I read through and checked all permissions. I contacted the ODBC driver company and have got a new License Key. It now works. Fantastic!!!

Best wishes

Paul

PaulSp SSCrazy Points: 2499 More actions · Answer 5

Hi again Damon

I was so pleased to see the tables, I didn't think about query syntax.

For Oracle I would use:

select * from LINKEDSERVERNAME..SCHEMA.TABLENAME

For Interbase tables there is no Schema. So tried variations:

select * from LINKEDSERVERNAME.TABLENAME

Any ideas?

Paul

Key DBA SSCertifiable Points: 6029 More actions · Answer 6

Checking with the driver vendor, good show! Now on to the next technical hurdle ...

Ah yes, OPENQUERY ... let me grab my notes ...

You are really close ... you will need a couple of dots "." and you should be good to go to be able to "see" the table(s).

There are two ways to "talk" with a Linked Server

1) 4 Part Names

-- 4 part name using Linked Server:

-- Fully qualified four-part table name in the form of:

-- linked_server_name.catalog.schema.object_name

SELECT

top 10

CLIENTID

,SUBSCNUM

,PERSON#

-- 4 part name:

from DB2_DATABASES.FHCPDAYTONA.ODBCLIB.RXHISTRY08

*I don't see many folk posting their admiration for the 4 part names, I believe that OPENQUERY is much more efficient, BUT you can use SQL Syntax with 4 part names.*

--

2) OPENQUERY

select * FROM OPENQUERY

(

DB2_DATABASES,

'

select

count(*)

from

ODBCLIB.RXHISTRY08

'

)

*NOTE* You are asking for everything back in the "select * FROM OPENQUERY" line. Everything between the first ' and the last ' will need to be written using the SQL Syntax of the engine that the ODBC Driver is connecting through. For example if you used OPENQUERY using an Oracle ODBC connection to get the current date from the Oracle database it would look something like:

select * FROM OPENQUERY

(

Oracle_9i,

'

select

sysdate

from dual

'

)

*My Oracle test system is, um, sleeping, that's it. Apologies in advance is the syntax isn't perfect.*

Another important thing that I have learned the hard way, is that OPENQUERY does NOT like the SQL Server comment syntax of:

--

or the

/*

*/

... you WILL get an error, so comment your OPENQUERY block above the OPENQUERY line.

If you could, please post some sample syntax when you get things working, in the event someone else is in your shoes.

Happy Coding

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

PaulSp SSCrazy Points: 2499 More actions · Answer 7

Hi Damon

Cracked it! It was just as you suggested, but the problem lay with the ODBC driver. I needed to uncheck Schema, and check NullSchema. Seems a bit belt and braces - but it worked.

Thanks for all your time and help.

Best wishes

Paul

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

Paul,

Fantastic!

I am glad someone else can benefit from my "pack rat" mentality of taking T-SQL syntax notes.

Now maybe you won't be dreaming about the ODBC connection screens this weekend.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

PaulSp SSCrazy Points: 2499 More actions · Answer 9

Hi Damon

I absolutely won't. It's always great to get something working.

Best wishes

Paul