Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
SQL Server I/O: Remote Queries
Last updated Mar 28, 2003.
Sometimes the data you're after isn't in your database. Sometimes it isn't on your server. Sometimes it isn't even on SQL Server at all.
In these situations, you could call on the application developer to write individual queries against other databases, servers or platforms. The developer could take that data and put it together on the screen for the user.
But if you're a DBA, you really don't want to give that kind of work to the developer. You want to deliver a single solution for the developer, regardless of the location of the data.
The good news is that you've got options. I'll show you how to get data from other databases, servers and sources, and even how to join them together. The bad news is that the access comes with a price – and that price can be really poor performance.
The first possible situation is that the data you're after might not be in your own database. This is a simple fix, and only involves using three part names. The three part name of a database object has this format:
To demonstrate, I've created another database with one table on my system like this:
CREATE DATABASE pubs2 GO USE pubs2 GO CREATE TABLE authorsinfo ( au_id varchar(15) , foodgroup varchar(50) ) GO INSERT INTO authorsinfo VALUES ( '172-32-1176' , 'Chocolate' ) GO
I also entered one value, which combines one of my author's id fields with a favorite food group (chocolate, what else?).
If I want to see data from those two databases, all I have to do is join them with a three part name:
SELECT a.au_fname + ' ' + a.au_lname as 'Name', b.foodgroup as 'Favorite Food Group' FROM pubs.dbo.authors a INNER JOIN pubs2.dbo.authorsinfo b ON a.au_id = b.au_id GO Name Favorite Food Group ------------------------------------------------------------- -------------------------------------------------- Johnson White Chocolate (1 row(s) affected)
You can also create views with these types of statements. In fact, in one application I had I kept OLTP data in one database, and created an entirely different database with nothing but views in it. I did this to enable a simple security mechanism, and to hide the complexity of the data that had a requirement to be case-sensitive. The views were stored in a non-case sensitive database, allowing the users to access it without running into problems with the column headings.
If the data is stored in another server entirely, you can access it using a remote server and a stored procedure. Remote servers need to be on a fairly fast connection, or your queries will really pay the price; but if the application calls for infrequently accessed data, it can be a lifesaver. You can set up a remote server graphically in Enterprise Manager, or use the sp_addlinkedserver stored procedure. You set up the first server, and then perform the same procedure on the second server. The final step is to set up a user from the first server on the second, which provides your security. Books Online shows the entire process in code:
EXEC sp_addlinkedserver ServerName1, N'SQL Server' EXEC sp_addlinkedserver ServerName2 EXEC sp_configure 'remote access', 1 RECONFIGURE GO
Stop and restart the first SQL Server, and then run this code on the second server:
-- The example shows how to set up access for a login 'sa' -- from ServerName1 on ServerName2. EXEC sp_addlinkedserver ServerName2, local EXEC sp_addlinkedserver ServerName1 EXEC sp_configure 'remote access', 1 RECONFIGURE GO -- Assumes that the login 'sa' in ServerName2 and ServerName1 -- have the same password. EXEC sp_addremotelogin ServerName1, sa, sa GO
On your first server, you now can access stored procedures on the second. The query runs on the second server and returns only the results to the first.
You can also use a linked server to access another SQL Server.
The real power here is that you can access data that isn't in a SQL Server at all. You can use Enterprise Manager or a few stored procedures to create the linked server.
Here's an example of using a linked server to get data from a Microsoft Access database. This stored procedure sets up the linked server (this only has to be done once) I call "Writing," and then queries the Notes field from a table in the Access database called tblProjects:
EXEC sp_addlinkedserver @server = 'Writing' , @Srvproduct = 'Access' , @Provider = 'Microsoft.Jet.OLEDB.4.0' , @datasrc = 'c:\temp\writing.mdb' GO SELECT Notes FROM Writing...tblProjects GO
You can find all the @Provider strings in Books Online under the subject of sp_addlinkedserver.
If you'd like to access Access (I love saying that) without setting up a linked server, there are a couple of remote query methods you can use. Here's an example of using the OPENROWSET command, which you can treat just like a table:
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0' , 'c:\temp\writing.mdb'; 'admin'; , 'SELECT Notes FROM tblProjects')
The format for this command depends on the provider you're talking to, but all OLE DB formats are supported, including ODBC connections. In the third line, you can see the provider string, and in the fourth line you enter the "database" name and credentials for login. The final line has the remote SELECT that creates the dataset the outer SELECT uses as a table.
In addition to OPENROWSET, you can also use OPENDATASOURCE as well. Here's an example of using that to access an ODBC connected datasource called ProdServer:
SELECT * FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=ProdServer;User ID=Buck;Password=passwd' ).pubs.dbo.authors
You can join the results of these queries to local tables, enabling a really distributed architecture.
All this flexibility comes with a price. Depending on several factors these queries can have really awful performance. If the link is slow, the query processor is busy, or the connection model is poor, they can be quite slow.
On the other hand, it's really useful to be able to access data from various sources without having to resort to multiple client installations or using code.
One more thing: security is also a concern. To properly implement a good security plan, use linked servers. They provide greater flexibility in security options.
Brian Moran has a good article on "Heterogeneous" queries from SQL Server 7, but the info is still good for SQL Server 2000. You can find it here.
InformIT Tutorials and Sample Chapters
Raul Sharma has an article explaining linked servers further here at InformIT.