Query table from another ms sqlserver

I'm really not a database person, so forgive me if this question.

I'm using Visual Studio 2008 and I am trying to view tables 开发者_Go百科on another server database.

Example:

I have my aspnetdb.mdf database, and my anaylsis.mdf database.

What I am trying to do within Visual Studio is read table columns that are inside aspnetdb.mdf from anaylsis.mdf.

How is this done?

Thanks

In order to have access to any object in other server, you need to create a linked server to that server as below:

in your aspnetdb go to server Objects -> Linked Servers ->Right click -> new linked servers . a window will be opened that you have to fill the information in the general tab as below: Linked Server: anaylsisDB Server Type: otherdata source Provider: SQL Native Client ProductName :SQL Data source: anaylsis(This is the server name which you want to connect to)

After you finished with this tab, in the left side of the page goto secutity tab and fill the information as below: In the bottom of the page select be made using this secutity content and insert the anaylsis server username and password.

click ok and now you have made a linked server to the anaylsis. so you can use any objects in anaylsis with this format:

anaylsisDB.[Databasename].dbo.[tableName] or anaylsisDB.[Databasename].dbo.[ViewName] Here anaylsisDB is the name of the linked server that we have made to the anaylsis server.

SELECT * FROM [linkedServer].[database].[dbo].[someTable]

You find some usefull information in the T-SQL Documentation on MSDN:

If the table or view exists outside the instance of SQL Serverl, use a four-part name in the form linked_server.catalog.schema.object. For more information, see sp_addlinkedserver (Transact-SQL).

Source: http://msdn.microsoft.com/en-us/library/ms177634(v=SQL.100).aspx#c089161a-53bf-46d4-a2da-51252dd10e3f_c

A useful way to do that is to use VIEWS, you create view with DATAS from other databases and use it like a kind of table in your Database..

MSDN

You can either setup a linked server on the the same server you are running the sp on which will give it an alias i.e.

select * 
from Server2.Database2.dbo.SomeTable

Or you can use the OpenRowset command. I always try and use the Linked server option although, the OpenRowset is useful if you don't have server admin access.

Both are obviously dependant on the servers being able to communicate ok.