Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100

    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
    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
    Last edited by gvee; 08-22-07 at 05:30.
    George
    Home | Blog

  2. #2
    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:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100
    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

  4. #4
    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:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100
    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

  6. #6
    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:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100
    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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Almost exactly like ours
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100
    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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,100
    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
    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

  11. #11
    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:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •