Non-standard linked servers--how to set up?

[jsteph]

Hi all,
I have sql-2005 and I wish to link a Borland Interbase database. I have a proper ADO provider string that I use from VB applications.

I would like to somehow link this Interbase db so I can read it's tables from stored procedures in sql-2005. How can I do this? The default Linked Servers list has a very short and specific list of types of servers one can link, and Interbase didn't make the cut.

Thansk for any help,
--Jim

 

[NoCoolHandle]

openrowset (look it up in books on line, but it does alow you to add a provider string as you query a remote datasource - i think all oledb drivers are supported.)

 

[jsteph]

I got it to link via the provider string in sql 2000, but not in 2005, I think it's because the 2005 instance is on a 64-bit box.

But even in 2000 it doesn't behave like a normal linked server. It shows up as a linked server and even lists the tables, but I can't reference it in a normal sql statement.

The OpenQuery and OpenRowset do both work, but they are extreeeemly tedious to work with.
--Jim

 

[NoCoolHandle]

If you have a valid linked server the way you reference your table is important.

the 4 arguments are
1. servername
2. database
3. object owner/schem
4. object nme

if your datasource doesn't accept a specific argumnet, you need to ommit it. but leave the dots..

so for instance oracle doesn't support database names

server..owner.table

excel doesn't have databases or owners

server...sheet1$


try different versions and see if you can hit the right combo.

Rob

 

[jsteph]

Yep, I tried all that first.

I think the odbc provider just dropped the ball:

If I use the dots alone it says "You've used a four part identifier but didn't specify a schema or database.." or something like that.

Yet...when I specify the database and schema, or specify just the database leaving the schema dot empty, or vice-versa, the message is "This provider does not support specifying a schema or database"

Those are the kind of catch-22 message that infuriate me.
--Jim

 

[NoCoolHandle]

Jim, i tend to agree with your call on the odbc driver and specifically how it works with the linked server type query.,.


That leads me back to the original idea. What about a straight openrowset query..

e.g. select * from openrowset('providername','connectionstring','select * from abc) as abc

 

[jsteph]

I am using the OpenRowset and OpenQuery. The problem with those--and it's a huge problem--is that they don't take string variables for the sql argument nor do they take concatenated sql arguments, at least not with some bizarre string-manipulation.

For example:

declare @idcode varchar(10)
set @idcode = 'ABC'
Select * From OpenRowset('provider, etc...','SELECT * FROM SomeTable Where IDCode = ''' + @idcode + '''')
Gives an errors and does not work.

Even:
declare @sql varchar(1000)
set @sql = 'Select * From Sometable'
Select * From OpenRowset('provider,@sql)
Gives Error and does not work.

I saw an example that used a lot of string acrobatics with Replace(), etc, to finally trick OpenRowset or OpenQuery into accepting a variable, but the syntax is so confusing as to be not feasible, the code is extensively difficult to read and/or maintain. I tried this and it does work, but I'm just hoping I can do better.

I really want to be able to use sql on this linked-server's tables in a normal way without resorting to contortionist measures.
--Jim