Can't join tables from two databases using linked servers when DBs on same SQL instance

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
.

Relevant Pages

  • RE: full-text search failed with one million of key words (Sqlserv
    ... Research with "toto" was just a test. ... In real life, my database contains ... for only one book) And the table i want to query looks like this: ... I have to make big tests with sql server 2005 express (i'm waiting credits ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Error 25081: Incorrectly generating identities after a merge
    ... database similarly to SQL Server CE Replication. ... On the SQL Server 2000 database with replicated tables from the original ... The I synchronized that database and ran the same query: ... >> synchronization. ...
    (microsoft.public.sqlserver.ce)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Using a worksheet as a table in an SQL query
    ... I'll probably just dump the SQL Server data to a spreadsheet and join the two spreadsheets. ... You could use Access to link to both sources and create a crosstab query. ... There are ways to enter the data into their database via the application, but it would require them to go to many different windows instead of being able to enter the data all on one screen. ... There is not a great deal of data they would enter on the spreadsheet, but I'm not sure how slow that would make the query. ...
    (microsoft.public.excel.programming)
  • Re: Issue with a table that is 75%-80% of the database size
    ... SQL Server 2000 does not support table partitioning. ... Have you tried database differential backup? ... your query response time and backup time. ...
    (microsoft.public.sqlserver.server)
Loading