Can't join tables from two databases using linked servers ..

Hi,

I've got two databases on the same SQL server instance and I've
created a linked server to that instance from my host SQL instance. I
was wondering if I can create a join between tables on the two
databases. It appears as though SQL server only recognizes the first
database referenced in the query. I've tried the following query:

select
A.fld1, B.fld2
from
linkedserver.databaseA.ownerA.tableA "A" JOIN
linkedserver.databaseB.ownerB.tableB "B"

But got this message:
OLE DB provider 'linkedserver' does not contain table
'"databaseB"."ownerB"."TableB"'. The table either does not exist or
the current user does not have permissions on that table.

If I switch the two lines around, the error occurs on the "A' database
instead of "B". If I only query one database, either one, all works
fine.

I tried creating an alias using the Client Network Utility and
referenced the alias for the B database in my query, but got the same
error. I also tried adding a second linked server using
sp_addlinkedserver, specifying database B in the catalog parameter,
but get the same error. If I run the sp_catalogs stored procedure, I
see both DatabaseA and DatabaseB in the result set.

Any ideas on where I am going wrong? Thanks.

Dan

On Feb 11, 4:01 pm, acesover wrote:
> Hi,
>
> I've got two databases on the same SQL server instance and I've
> created a linked server to that instance from my host SQL instance. I
> was wondering if I can create a join between tables on the two
> databases. It appears as though SQL server only recognizes the first
> database referenced in the query. I've tried the following query:
>
> select
>   A.fld1, B.fld2
> from
>   linkedserver.databaseA.ownerA.tableA "A" JOIN
>   linkedserver.databaseB.ownerB.tableB "B"
>
> But got this message:
> OLE DB provider 'linkedserver' does not contain table
> '"databaseB"."ownerB"."TableB"'.  The table either does not exist or
> the current user does not have permissions on that table.
>
> If I switch the two lines around, the error occurs on the "A' database
> instead of "B". If I only query one database, either one, all works
> fine.
>
> I tried creating an alias using the Client Network Utility and
> referenced the alias for the B database in my query, but got the same
> error. I also tried adding a second linked server using
> sp_addlinkedserver, specifying database B in the catalog parameter,
> but get the same error.  If I run the sp_catalogs stored procedure, I
> see both DatabaseA and DatabaseB in the result set.
>
> Any ideas on where I am going wrong? Thanks.
>
> Dan

PLEASE IGNORE: It was a typo in my second reference that was causing
the problem.