Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:
> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> > If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> > to
> > execute a query on SERVER_A referencing a table on SERVER_B is that
> > possible?
> >
> > Example
> >
> > First Server: SERVER_A, Database: Nuts, Table: Goodness
> >
> > Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> >
> > From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> > where I need to get some data from SERVER_A , database 'Nuts', table
> > 'Goodness'.
> >
> > Is that possible? How can I do it? Would I use a linked server? If so can
> > anyone show me a good resource on how to set up a linked server?
>
>