Работа с данными из удаленных источников

Использование функции OPENROWSET для установления соединения с любым источником данных

Используйте функцию OPENROWSET для того, чтобы открыть любой реляционный или не реляционный источник данных. SQL Server всегда устанавливает соединения с удаленными источниками данных с помощью интерфейса OLE DB. Удаленный источник данных должен поддерживать OLE DB, иначе соединение не будет установлено.

Функция OPENROWSET позволяет указать параметры конфигурации соединения, чтобы управлять соединением SQL Server с удаленным источником данных. Можно также воспользоваться определяемой поставщиком строкой запроса, в которой можно указать, какой ресурс следует запросить.

Устанавливаем соединение с базой данных Microsoft Office Access при помощи функции OPENROWSET

1. Запустите SQL Server Management Studio и откройте окно New Query (Новый запрос).
   Примечание. По умолчанию файлы Northwind.mdb и Employees.xls устанавливаются вместе с другими файлами примеров в следующий каталог: C:\Documents and Settings\User\My Documents\Microsoft Press\Sql2005SBS_AppliedTechniques\Chapter08

   Если вы установили файлы примеров в другой каталог, необходимо внести в код соответствующие изменения.
2. Введите следующий сценарий в окне New Query (Новый запрос): (Измените путь к файлу, чтобы он соответствовал размещению файла Northwind.mdb на вашем компьютере. Этот пример находится в папке SqlScripts под именем OPENROWSET.sql ).

   SELECT OrderInfo.OrderID, OrderInfo.CustomerID, OrderInfo.EmployeeID 
      FROM OPENROWSET( "Microsoft.Jet.OLEDB.4.0",
                       "C:\Documents and Settings\User\My Documents\Microsoft Press
                               \Sql2005SBS_AppliedTechniques\Chapter08\Northwind.mdb'; 
                      "Admin";'',
                      "SELECT OrderID, CustomerID, EmployeeID FROM Orders") As OrderInfo
3. Выполните сценарий:

Результат, возвращенный OPENROWSET можно использовать в любой инструкции T-SQL, в которой ожидается табличный результат.

Использование SQL Server для чтения данных из нескольких источников

Следующие шаги описывают представление с функциями, аналогичными методу ADO.NET, о котором рассказывалось в начале этой лекции, для соединения с несколькими источниками данных из среднего яруса. Однако сейчас этот код будет использовать SQL Server для чтения данных из нескольких источников. Код для этого примера включен в файлы примеров в папку SqlScriptExamples под именем ReadDataFromMultiple Sources.sql. Необходимо изменить следующие шаги, чтобы они соответствовали источникам данных, которые действительно существуют в вашей сети (базе данных Access, SQL Server или Oracle).

1. Запустите SQL Server Management Studio и установите соединение с сервером SQL Server 2005 и базой данных, в которой будет создано представление.
2. Введите необходимый для определения представления код T-SQL.

   CREATE VIEW GlobalSalesData AS
3. Установите нерегламентированное соединение с базой данных Microsoft Access PreSalesDB, имейте в виду, что надо указать полный путь к файлу базы данных.

   SELECT PreSales.CustomerID, PreSales.Date, PreSales.Amount
      FROM OPENROWSET(
           "Microsoft.Jet.OLEDB.4.0",
           "D:\PreSalesDB.MDB";'Admin';'Pass@word1',
           "SELECT CustomerID, Date, Amount, Quarter FROM Opportunities
      ORDER BY Date DESC') As PreSales
   UNION

   Microsoft.Jet.OLEDB.4.0 - это поставщик доступа к данным, который используется для доступа к удаленному источнику данных. Замените в этом коде путь к файлу, имя файла, имя пользователя и пароль на значения, соответствующие вашей среде.
4. Установите нерегламентированное соединение с сервером Microsoft SQL Server 2005, на котором размещена база данных SalesDB.

   SELECT Orders.CustomerID, Orders.Date, Orders.Amount
      FROM OPENROWSET(
           "SQLOLEDB",
           "Server=Sales; Trusted_Connection=yes;",
           "SalesDB.Sales.Orders") As Orders
     UNION

   SQLOLEDB - это поставщик доступа к данным, который используется для доступа к удаленному источнику данных SQL Server. Установка параметра Trusted_Connection на yes означает, что код будет проверять подлинность пользователя, используя учетные данные Windows для удаленного сервера. Замените имена сервера и базы данных на соответствующие значения для вашей среды.
5. Установите нерегламентированное соединение со сторонним сервером базы данных (в данном примере используется поставщик OLE DB Oracle), на котором размещена база данных PostSalesDB.

   SELECT PostSales.CustomerID, PostSales.Date, PostSales.Amount
     FROM OPENROWSET(
        "msdaora",
        "Data Source=PostSalesDB;User Id=LowPrivilegeUser; Password=SomePwd;",
        "SELECT CustomerID, Quarter, Date, Amount from Support") As PostSales

Здесь msdaora - это поставщик доступа к данным, который используется для доступа к удаленному источнику данных. Замените поставщик доступа к данным, источник данных, имя пользователя и пароль на значения, соответствующие вашей среде.

Результаты каждого запроса соединяются, чтобы обеспечить возвращение одного результирующего набора.

Чтение объектов при помощи функции OPENROWSET

Функция OPENROWSET принимает два различных набора параметров для определения конфигурации строки соединения:

• Первая синтаксическая конструкция позволяет повторно использовать одну и ту же строку соединения, созданную любым приложением, которое использует для соединения с источником данных OLE DB. Определим строку соединения с синтаксисом, определяемым поставщиком OLE DB:

  OPENROWSET("provider name", "provider specific string", "object|query")
• Вот второй синтаксической конструкции мы указываем по отдельности источник данных, идентификатор пользователя и пароль, которые следует использовать:

  OPENROWSET("provider name", "datasource"; "userid"; "password", "object|query")

  Последний параметр (показанный в предыдущем примере кода как object | query) показывает, что для возвращения ответа нужен именно удаленный источник данных. Можно указать, что нужно извлечь объект данных, например, таблицу или представление, или результат данного запроса, как показано ниже (выделено полужирным шрифтом). (Код из этого раздела можно найти в примерах в папке SqlScriptExamples в файле OPENROWSETSyntaxExamples ).

  SELECT PostSales.CustomerID, PostSales.Date, PostSales.Amount
     FROM OPENROWSET(
          "msdaora",
          "Data Source=PostSalesDB;User Id=LowPrivilegeUser; Password=SomePwd;",
          "SELECT CustomerID, Quarter, Date, Amount from Support") As PostSales

  Источник для извлечения данных можно указать, используя трехкомпонентное имя, как показано ниже полужирным шрифтом, в котором SalesDB идентифицирует каталог (или базу данных), Sales идентифицирует схему, а Orders идентифицирует таблицу (или представление), которые следует возвратить.

  SELECT Orders.CustomerID, Orders.Date, Orders.Amount
     FROM OPENROWSET(
          "SQLOLEDB",
          "Server=Sales; Trusted_Connection=yes;",
          "SalesDB.Sales.Orders") As Orders
     WHERE Orders.Quarter = @Quarter

  Важно. Полное уточненное имя используется, чтобы корректно идентифицировать извлекаемый ресурс или объект в источниках данных, которые упорядочивают свои данные в каталогах, схемах и объектах данных.

  Полное уточненное имя состоит из четырех идентификаторов:

  < ИмяСервера >.< ИмяКаталога>.< ИмяСхемы >.< ИмяРесурса >

Использование функции OPENDATASOURCE для установления соединения с любым источником данных

Функция OPENDATASOURCE также позволяет устанавливать соединения с источниками данных, данные в которых упорядочены по каталогам, схемам и объектам. Главное отличие от функции OPENROWSET -это способ вызова функции OPENDATASOURCE. Обе функции возвращают результирующий набор OLE DB.

Функция OPENDATASOURCE занимает место компонента <Имя-Сервера> в полном уточненном имени (четырехкомпонентном), чтобы идентифицировать объект, который нужно извлечь из удаленного источника данных, как показано ниже. Имейте в виду, что необходимо изменить эти примеры, чтобы они соответствовали вашим серверам и базам данных. Следующий код можно найти в файлах примеров под именем UseOPENDATASOURCE ToConnectToAnotherServer.sql в папке SqlScriptExamples.

SELECT Orders.CustomerID, Orders.Date, Orders.Amount
   FROM OPENDATASOURCE(
        "SQLOLEDB",
        "Server=Sales; Trusted_Connection=yes;").SalesDB.dbo.Orders 
   WHERE Orders.Quarter = @Quarter

В приведенном выше примере функция OPENDATASOURCE замещает имя сервера в полном уточненном имени таблицы Orders.

Следующий код показывает, как можно извлечь данные из файла Excel. Этот пример находится в папке SqlScripts под именем UseOPENDATASOURCEtoExtractXL.sql ).

SELECT Employees.FirstName,
       Employees.LastName,
       Employees.Title,
       Employees.Country 
   FROM OPENDATASOURCE(
       "Microsoft.Jet.OLEDB.4.0",
       "Excel 8.0;DATABASE=C:\Documents and Settings\User\My Documents\ 
                                   Microsoft Press\Sql2005SBS_AppliedTechniques\ 
                                   Chapter08\EmployeeList.xls')...[Employees$] AS Employees 
   WHERE LastName IS NOT NULL ORDER BY Employees.Country DESC

Примечание. Даже при открытии файла Excel используется четырехкомпонентный синтаксис имени. В коде SQL предыдущего примера имена каталога и схемы опущены, тем не менее, разделитель (.) должен присутствовать. Именно по этой причине в запросе перед именем страницы Excel необходимы кавычки "..." (Employees$).