[dba-SQLServer] Accessing Tables in another server

Once linked you can reference any database and table in the linked
server so long as you have access so the syntax would look like this

SELECT * FROM LinkedServerName.Database.dbo.TableName

On 1/17/07, David Emerson <newsgrps at dalyn.co.nz> 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
> > >
> > >_______________________________________________
> > >dba-SQLServer mailing list
> > >dba-SQLServer at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > >http://www.databaseadvisors.com
> > >
> > >
> > >
> > >
> > >
> > >_______________________________________________
> > >dba-SQLServer mailing list
> > >dba-SQLServer at databaseadvisors.com
> > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > >http://www.databaseadvisors.com
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...