Posted by mkarbarz@gmail.com on 02/06/07 23:53

On Feb 6, 6:18 pm, "AlterEgo" <altereg...@dslextreme.com> wrote:
> 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
>
> <mkarb...@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?- Hide quoted text -
>
> - Show quoted text -

thanks for all your help; indeed with OPENROWSET I was able to get
the query working with no problems (or at least the problems I'm
having are not related to the tables, rather with Error converting
data type DBTYPE_DBDATE to datetime, but that's a topic for another
thread if I can't figure it out)

[Back to original message]