|[ Team LiB ]|
Recipe 14.8 Reference Data from More than One SQL Server Database in an ADP
Although at first glance this seems to be a problem, the solution is readily at hand with SQL Server's three-part naming convention. You are probably already familiar with the OwnerName.ObjectName syntax for referring to SQL Server objects, which is needed when users other than the owner (or creator) of that object wish to use the object. The three-part naming syntax is:
To refer to another SQL Server database in your ADP, follow these steps:
SQL Server allows users to access other databases residing on the same server when the three-part naming syntax is used. However, users must have been granted permissions in the source database if data is to be accessed with a direct SQL statement. SQL Server will return a permissions error message if those permissions have not been granted.
Working with data from multiple databases is easy in ADPs, even though you see the objects from only one database listed in the database window. Just remember to use the three-part naming syntax.
If the data you need is not just in another database but on another server, it gets a little more complicated. In this case, you need to set up a linked server in SQL Server to access the data. Linked servers in SQL Server use OLE DB providers, which means you are not limited to only SQL Server data. Linked servers allow you to use SQL Server as a gateway to many different data sources, just as you may use Access databases to link to multiple data sources.
|[ Team LiB ]|