Linked tables like in Access?
-
Hypothetical Situation:
Is there any way to query one database from another database?
EG, the Finance Analyst maintains his own SQL Server database SQLFIN. He wants to read Sales Rep data from a table in the Office Manager's database SQLPERS. Can you do this without using Access?
I have an application that was written in Access that is now exceeding performance limitations. I did not design this application, therefore I want to change as little as possible. It previously pulled information from SQL Server into Access tables. I intend to first create views in SQL Server, and then to link these views in Access with the linked tables having the same name as the Access tables.
The problem is that some of the data in the Access tables comes from a magical fantasy table that does not exist in the SQL tables. I want to put this magical fantasy table in a new SQL database for reporting. I want this reporting database to be able to link to the production database instead of having to copy this data and maintain it, so I can create a view in the reporting database.
I hope my question makes sense... Thanks.
Scott
-
This can be done via a linked server (see BOL). The syntax is easy
SELECT ID
FROM [servername].[dbname].[owner, usually dbo].[tablename]
or
SELECT ID
FROM testserver.Northwind.dbo.Orders
This can be used to also join tables from multiple servers and can be used in a view or stored procedure.
Everett Wilson
ewilson10@yahoo.com -
And referring to an object in a database on the SAME server is as easy as:
select stuff from databasename.objectowner.ojectname
-
Thanks everybody! This is so much faster than looking in BOL
Scott
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply