[dba-SQLServer] Accessing Tables in another server

I managed to get separate linked servers for each database by setting 
the server type to Other Data Source, selecting Microsoft OLD DB 
Provider for SQL Server as the provider name, setting the data source 
to JOHNSONVILLE (the name of the server), and setting the provider string to:
DRIVER=SQL Server;Server=JOHNSONVILLE;APP=Microsoft Open Database 
Connectivity;DATABASE=mySQLbe;UID=sa

Now in the linked servers details I can see the correct tables and 
views.  One of the linked servers is called JVILLE2000.

However I still am having troubles accessing the data.

If I have this in a stored procedure (or even in Query Analyser):

SELECT JVILLE2000.dbo.ttmpLoadAllocatAllLoads.YrMth
FROM JVILLE2000.dbo.ttmpLoadAllocatAllLoads

I get an error - Invalid object name 'JVILLE2000.dbo.ttmpLoadAllocatAllLoads'

If I try:

SELECT JVILLE2000.egasSQLbe.dbo.ttmpLoadAllocatAllLoads.YrMth
FROM JVILLE2000.egasSQLbe.dbo.ttmpLoadAllocatAllLoads

The number name 'JVILLE2000.mySQLbe.dbo.ttmpLoadAllocatAllLoads' 
contains more than the maximum number of prefixes. The maximum is 3.

How do I access the data in the linked servers?

David

At 18/01/2007, you wrote:
>OK, we are getting closer.  I created a linked server in EM.  In the
>linked Server General tab I put the server name, selected SQL server
>as the server type, and use logins current security context on the
>security tab.  The linked sever created ok but when I look at the
>tables in EM it only shows them for one database.  I actually have
>two databases on that server that I want to be able to link to.  I
>can see all databases in the server registration area.
>
>How can I make tables from both databases available?
>
>David
>
>At 17/01/2007, you wrote:
> >In EM
> >Security --> Linked Servers
> >
> >cheers
> >
> >Michael M
> >-----Original Message-----
> >From: dba-sqlserver-bounces at databaseadvisors.com
> >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
> >Emerson
> >Sent: Wednesday, 17 January 2007 3:12 PM
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] Accessing Tables in another server
> >
> >Thanks Arthur,
> >
> >I thought it would be already linked if it was showing in Enterprise
> >Manager as a registered server.  Is there a step I am missing?
> >
> >David
> >
> >At 17/01/2007, you wrote:
> > >Try establishing Server2 as a linked server.
> > >Try referencing S2's table as \\S2\database\table.
> > >
> > >Let me know if these go wrong. I can do this. I may need to know your
> > >specific names, etc.
> > >
> > >That said, there are certain things you cannot do, such as use RI
> > >across databases. Bummer, but that is the case (AFAIK).
> > >
> > >----- Original Message ----
> > >From: David Emerson <newsgrps at dalyn.co.nz>
> > >To: dba-SQLServer at databaseadvisors.com
> > >Sent: Tuesday, January 16, 2007 10:31:47 PM
> > >Subject: [dba-SQLServer] Accessing Tables in another server
> > >
> > >SQL2000.
> > >
> > >I have two servers registered in enterprise manager.  I would like to
> > >run a stored procedure from a database on one server which gets data
> > >from a table on the second server.  For example, assuming we have:
> > >
> > >Stored Procedure1 in database1 on server 1
> > >Table2 in Database2 on Server 2
> > >
> > >I would like to do something like this in Database1
> > >
> > >Select *
> > >  From Server2.Database2.dbo.Table2
> > >
> > >The error message I get when trying to save the procedure is "The
> > >number name contains more than the maximum number of prefixes.  The
> > >maximum is three."
> > >
> > >How can I reference the data on the other server?
> > >
> > >Regards
> > >
> > >David Emerson
> > >Dalyn Software Ltd
> > >Wellington, New Zealand