[dba-SQLServer] Accessing Tables in another server

Francisco Tapia fhtapia at gmail.com
Thu Jan 18 13:03:44 CST 2007


Tables, Views, Stored Procedures, they all follow that format so long
as you have rights .

On 1/18/07, JWColby <jwcolby at colbyconsulting.com> wrote:
> That holds for views as well?
>
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
> Tapia
> Sent: Thursday, January 18, 2007 1:38 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [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...
> _______________________________________________
> 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...



More information about the dba-SQLServer mailing list