If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > sp_addlinkedserver

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
sp_addlinkedserver

Hey guys and gals,

I got asked a question yesterday about queries that JOIN between two databases on a single server... Simple enough
Code:
SELECT e1.employee_number
FROM Database1.dbo.employees As e1
 INNER
  JOIN Database2.dbo.employees As e2
    ON e1.employee_number = e2.employee_number
But what about joining tables from two separate servers?
I figured that this would use full 4 part naming
Code:
SELECT e1.employee_number
FROM Server1.Database1.dbo.employees As e1
 INNER
  JOIN Server2.Database2.dbo.employees As e2
    ON e1.employee_number = e2.employee_number
But I got the following error message
Quote:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'Server2' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
So off I went to trusty BoL and Google to see what sp_addlinkedserver was all about and came up with the following
Code:
EXEC sp_addlinkedserver 
   'Server2',
   N'SQL Server'
Is this syntax correct?

I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
  1. Is this process reversible?
    I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
  2. Does the link affect all databases on a single server, or just the one I'm playing with?
  3. How do I connect to a specific database on a specific server?
    Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
EDIT: highlighted in pink
__________________
George
Home | Blog

Last edited by gvee; 08-22-07 at 05:30.
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by georgev
I have yet to execute it because I have a couple of other questions that I was hoping you people could help me with first...
  1. Is this process reversible?
    I've not found a sp_removelinkedserver or similar yet (it's probably staring me right in the face!)
  2. Does the link affect all databases on a single server, or just the one I'm playing with?
  3. How do I connect to a specific database on a specific database?
    Is it a simple case of linking the two servers and then using 4 part naming to do so?
Hmm, I think that pretty much covers it!
1) Yes - check out sp_dropserver
2) It doesn't affect databases at all. What do you mean? The link provides permissions to the remote server as per the security context you specifiy when you create a linked server login.
3) Your question makes no sense . Do you mean a specific database on a specific instance? If so then yes - it is that simple.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
  1. sp_dropserver - I knew it'd be something as simple as that.
  2. Twas a stupid question - it affects the server, not the database (sysservers kinda gives that away)
  3. See edit above... I realise I knew the answer to thsi already - was just confused in my own head about it!
  4. NEW: Can you give a server an alias to be used in queries? So instead of "Server1.database1..." I could use "George.database1..."
I'm going to have a play with it shortly and see what I need to do about permissions etc... <insert classic Arnie quote>
__________________
George
Home | Blog
Reply With Quote
  #4 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Quote:
Originally Posted by georgev
NEW: Can you give a server an alias to be used in queries? So instead of "Server1.database1..." I could use "George.database1..."
I don't know - I am not familiar with 6.5 I think you might be able to but I'm not sure - I've never had a good reason to.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #5 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Server1 has SQL server 2000 on it, it has a number of databases including a couple 6.5's.
Server2 has SQL server 2005. Handful of databases, all started life in 2005
__________________
George
Home | Blog
Reply With Quote
  #6 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Actually - the @server parameter is the logical name and @datasrc is the physical name. So yes - you can alias. I've never bothered - I find it hard enough remembering servernames let alone what the servers are aliased as.
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #7 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Well, our servers are all named like "<3 character server type code>-<4 character code for state (LIVE/DEVL etc)-<2 character location code><ServerNumber>" so aliasing would be a nice thing
__________________
George
Home | Blog
Reply With Quote
  #8 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Almost exactly like ours
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #9 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Oh, it's actually <2 character OWNER code>, not location!
How silly of me

Right, finally got some time to play with linked servers now
tyk
__________________
George
Home | Blog
Reply With Quote
  #10 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Code:
SET NOCOUNT ON
EXEC sp_addlinkedserver
     @server	= [XXX-XXXX-XXXXX]
   , @srvproduct= 'SQL Server'
PRINT 'Linked server created'
GO

EXEC sp_addlinkedsrvlogin [XXX-XXXX-XXXXX
PRINT 'logins ....'
GO

SELECT * FROM [XXX-XXXX-XXXXX].[xxxxx-Test].dbo.Test WHERE firstname = 'george'
PRINT 'SQL Statement returned ' + Convert(varchar,@@RowCount) + ' rows'
GO

EXEC sp_dropserver [XXX-XXXX-XXXXX], 'droplogins'
PRINT 'Server and logins dropped'
GO

--SELECT * FROM sysservers
SET NOCOUNT OFF
Quote:
Linked server created
logins created
Server: Msg 17, Level 16, State 1, Line 2
SQL Server does not exist or access denied.
Server and logins dropped
So I have access via windows authentication on both servers... This is trying to connect from the 2000 box to the 05 one.
Any suggestions as to what I'm missing?
__________________
George
Home | Blog
Reply With Quote
  #11 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Here's the code I use:

Code:
EXEC sp_addlinkedserver 
@server=MyAlias', 
@srvproduct=N'',
@provider='SQLNCLI', 
@datasrc='MyRealServerName
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On