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 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).
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.
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.
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!
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.