Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
Simon
Simon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/de..._adda_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>
|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
>
|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon