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.

ThanksYou can add a linked server using Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_1ozd.asp), or using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp) from within Query Analyzer.

-PatP|||http://www.databasejournal.com/features/mssql/article.php/3085211 for detailed explanation.



HTH|||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|||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|||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|||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!

-PatP