Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: Link to another SQL database table

    Hello All,
    I have a question on linking tables with SQL Server. I have SQL Server 2000 with database A on machine A. I have SQL Server 2000 with database B on machine B. I want to link tables from the database on machine A into a database on machine B. I am not quite sure how to do this - I believe this can be done as I have seen it before. Any information and or resources to direct me to would be greatly appreciated.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can add a linked server using Enterprise Mangler, or using sp_addlinkedserver from within Query Analyzer.

    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Nov 2003
    Posts
    10

    Thanks for the info

    Thanks for the information, I appreciate the responses.

    I just have a follow on question. It seems that you can link an entire SQL server but is there any way around this if you cannot have full access to all the databases and tables within?

    For example, the SQL Server I need to link to has several databases. I need to link to 4 tables in one database. I will not have the authority to link in the entire database. With Access I can link to a database and select the tables I need to link in, with the SQL Server method I don't see that I can do this (it just may be that I am not doing this right - not too familiar with SQL Server).

    Any ideas?
    Thanks

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    For the linked server privilege to access entire database is not required, you can control the privileges for any logon pertaining to a table in the database.

    If the underlying user has required privilege to access the table, then LS connection will be successful and only data can be retrieved from permitted tables.

    Refer to the books online for more information on Linked servers and its security.
    H
    TH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A linked server gives one server (machine) access to another machine. The user is what determines the permissions, so even though you only expect to be able to "see" or use four tables, depending on the login/user being used you may have access to some other number of tables. You might have a user on your local system that has no permissions at all on the remote system. You might have another local user that would have administrator rights on the remote system, therefore they could access every table.

    This probably seems a bit complex after dealing with the much simpler security model that MS-Access (actually Jet, the underlying database engine) provides, but it is actually much more powerful.

    -PatP

  7. #7
    Join Date
    Nov 2003
    Posts
    10

    More Help...

    Thanks for the information - and thanks to all the responses I am now getting further. In a testing environment I have created the user, limited access to the tables and currently I have used the linked server feature to link in the database.

    The main point of doing this was so that I could create a view in my main database to combine data from the linked in database tables. This where I am running into a problem. I keep receiving a message "Could not find "servername" in sysservers, please run sp_addlinkedservers". I have followed the article provided in this thread but was not successful, I cannot get any of the items to execute, I receive syntax errors.

    I am not sure if I am in over my head here. Any help would be appreciated.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Usually at least, linked server information isn't security sensitive. If it is ok, can you post what you tried to execute and what error messages you received? Without at least some hints, I can't help much!

    -PatP

  9. #9
    Join Date
    Apr 2007
    Posts
    17
    Hi all,

    For what it's worth now to this post, I have written an entry on my blog on how to link SQL Server databases... Hope this helps people doing similar.

    Surfjungles Technology Blog: Linking SQL Server databases together via linked servers

    Kind regards,
    Jason.

  10. #10
    Join Date
    Oct 2009
    Posts
    27
    @surfjungle - Nice blog post.

    To further the discussion you might find the information at this link of value as well.

    SQL Server Programmers and Consultants
    Microsoft SQL Server Database Consultants and Application Developers - SQL Programmers Chicago

  11. #11
    Join Date
    Apr 2007
    Posts
    17
    Hi sql-programmers,

    Thanks for the feedback. If have also linked the servers via the Management Studio (this was what I did initially) as your link has suggested but found that for SQL Server 2008 at least (your link was for 2005 ) needed to have additionally configuration performed for the rpc and rpc out options. So, if doing this through the Management Studio, it may be necessary to ensure that these options are also catered for.

    Kind regards,
    Jason.

Posting Permissions

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