Posted by AlterEgo on 02/06/07 23:18

Marek,

In order to use a linked server, the server has to be capable of a four part
namespace. Your example was only showing a three part namespace.

You were using:
LINKEDSERVERNAME.SCHEMA.TABLE

You need to be able to specify:
LINKEDSERVERNAME.DATABASE.SCHEMA.TABLE

Do the ODBC drivers and databases you are using support a four-part name. If
they don't, then you will have to use OPENROWSET.

I don't know about SQL 2005, but in SQL 2K you can not inherit any part of
the namespace if the database is on a different server. The following won't
work:

LINKEDSERVERNAME.DATABASE..TABLE

-- Bill

<mkarbarz@gmail.com> wrote in message
news:1170801310.273492.21720@m58g2000cwm.googlegroups.com...
> On Feb 6, 5:09 pm, "Steve" <morrisz...@hotmail.com> wrote:
>> On Feb 6, 1:25 pm, "mkarb...@gmail.com" <mkarb...@gmail.com> wrote:
>>
>> > Hello,
>>
>> > I need to come up with a stored procedure that will allow me to read
>> > data from another database. The database I need to read the data from
>> > is a UniData residing on a Unix server. The stored procedure needs to
>> > reside on my sql 2005 server. The task is very simple in Access as we
>> > have ODBC connections set up to the UniData via Informix (or IBM)
>> > UniData ODBC drivers. I can easily combine my UniData and Sql Server
>> > tables from within access. However, I can't seem to find a way to
>> > replicate the same behavior in MS SQL Stored Procedure without the use
>> > of Access. Is that even possible?
>>
>> > Thanks,
>>
>> > Marek
>>
>> Linked server or OPENROWSET
>> In SQL Server 2005 Books Online see the topic: Distributed Queries
>
> I started playing around with the linked server and I got it to the
> point where I can see the tables from UniData, but when I try to query
> it using LINKEDSERVERNAME...TABLE I get 'Invalid schema or catalog
> specified for provider 'MSDASQL'." error. When I try to specify
> schema using LINKEDSERVERNAME.SCHEMA.TABLE I get 'Invalid object name
> LINKEDSERVERNAME.SCHEMA.TABLE'; do you know why these errors occur
> and how to get around them?
>

[Back to original message]