Дата публикации: 09.12.2013
Обновлено: 11.10.2018

Регистрация и работа со связанным сервером MSSQL 2012

Введение
Тестовое окружение
Подготовка к настройке связанного сервера
Настройка связанного сервера
Общие правила работы с Linked Server
Работа со связанным сервером из «Microsoft SQL Server Management Studio»
Запрос на выборку данных с использованием явной транзакции
Выборка данных с использованием автоматической транзакции
Выборка данных с использованием параметров
Модификация данных
Вставка нового ряда
Обновление ряда
Удаление ряда
Выполнение скрипта
Создание VIEW на основе связанного сервера
Вызов хранимой процедуры
Выполнение хранимой процедуры возвращающей множество
Выполнение хранимой процедуры возвращающей OUT-параметры
Выполнение запросов через инструкцию «EXEC AT»
Уровни изоляций транзакций
Работа в транзакции с уровнем изоляции «READ COMMITTED»
Работа в транзакции с уровнем изоляции «REPEATABLE READ»

Введение

В этой статье мы пошагово рассмотрим все этапы регистрации связанного сервера MSSQL 2012 к базе данных Firebird/InterBase и работу с ним.

Тестовое окружение

  • MSSQL 2012 Express R2 x64 (English). Windows Server 2008 R2 x64 (Russian) c English UI.
  • Firebird 2.5.3. 26709 x64 SuperClassic.
    • Тестовая база данных: employee.fdb (3 диалект) из поставки FB2.5

Подготовка к настройке связанного сервера

1. Установите IBProvider на сервер с MSSQL

Используйте инсталляторы правильной разрядности:

  • Для 64-битного MSSQL нужно установить 64-битный IBProvider
  • Для 32-битного MSSQL нужно установить 32-битный IBProvider

Рекомендации:

  • В случае 64-битной операционной системы, настоятельно рекомендуется установить оба провайдера – 32 и 64 бита.
  • Для работы с MSSQL не рекомендуется использовать «IBProvider Lite».
  • Установите так же «Samples» и «Binaries of examples» — они понадобятся при проверке вашей FB/IB базы данных. Потом их можно будет деинсталлировать.

2. Установка клиента InterBase/Firebird на сервер с MSSQL

Начиная с версии v3.27, IBProvider может напрямую подключаться к Firebird через TCP/IP без помощи серверного клиента (fbclient.dll). Для этого нужно указать в строке подключения «dbclient_type=fb.direct».

Модули с серверными клиентами:

32-битный клиент 64-битный клиент
InterBase gds32.dll ibclient64.dll
Firebird fbclient.dll (32-битная версия!) fbclient.dll (64-битная версия!)

Вы можете найти эти DLL в дистрибутивах вашего InterBase/Firebird сервера.

В случае Firebird, постарайтесь не перепутать файлы.

Обратите внимание, что серверные клиенты используют дополнительные DLL «Visual C++». Вы можете скачать установочные файлы «Microsoft Visual C++ Redistributable Package» с сайта IBProvider-a.

Варианты установки клиента

1. Скопировать DLL в системный каталог Windows.

Системный каталог для 32-битных DLL Системный каталог для 64-битных DLL
32-битная Windows C:\Windows\System32
64-битная Windows C:\Windows\SysWOW64 C:\Windows\System32
В случае 64-битной Windows, рекомендуется установить обоих клиентов – для 32-х бит и 64-х бит.

2. Скопировать DLL в отдельный каталог.

Пример для 64-битного клиента InterBase:

  • На любом не системном диске создайте каталог (например) d:\InterBaseClient\64bit
  • Скопируйте в этот каталог ibclient64.dll
  • Проверьте права доступа (на уровне файловой системы) к ibclient64.dll
В случае 64-битной Windows, рекомендуется установить (в разные каталоги) обоих клиентов – для 32-х бит и 64-х бит.

3. Проверьте корректность установки провайдера и серверного клиента

Если у вас нет опыта установки и настройки IBProvider-а и серверного клиента для работы с InterBase/Firebird, то имеет смысл проверить этап (1) и (2) без участия MSSQL.

Пример проверки для «Windows 2008 R2 64bit».

На компьютере с MSSQL создайте файл «test_connection.vbs» со следующим VBS-скриптом:

option explicit

dim cn

set cn=createobject("ADODB.Connection")

cn.Provider="LCPI.IBProvider.3"

'fbclient.dll from system directories of Windows (System32/SysWOW64)
cn.ConnectionString _
 ="location=HOME2:e:\database\employee.fdb;" & _
  "user id=sysdba;" & _
  "password=masterkey;" & _
  "dbclient_library=fbclient.dll;" & _
  "dbclient_library_64=fbclient.dll"

on error resume next

call cn.Open()

if(err.number<>0)then
 errorFlag=true

 wscript.echo "ERROR: ["&err.Source&"] "&err.Description

 dim e,n

 n=0

 for each e in cn.Errors
  n=n+1
  wscript.echo "["&n&"] "&e.Source&" - "&e.Description
 next 'e

 call wscript.quit(1) 
end if

on error goto 0

wscript.echo "Provider: "&cn.Properties("Provider Name").Value&" "&cn.Properties("Provider Version").Value

wscript.echo "OK!"

call wscript.quit(0) 

NOTE: в строке подключения должны быть ваши значения свойств «location», «user id», «password» и «dbclient_library». Описание всех свойств инициализации можно посмотреть здесь.

Запустите ваш файл «test_connection.vbs» из консоли (cmd.exe).

Тестирование 64-битного доступа к базе данных:

Тестирование 32-битного доступа к базе данных:

Добейтесь того, чтобы этот скрипт начал отрабатывать без ошибок.

4. Проверка подключаемой базы данных

Перед тем как подключать вашу FB/IB базу данных к MSSQL, нужно проверить отсутствие ошибок в её системных таблицах.

В противном случае – IBProvider или MSSQL могут отказаться работать с вашей базой данных.

В поставке дистрибутива IBProvider-а есть VBS-скрипт «prog_check_system_tables.wsf», который находит часть известных ошибок.

Для установки этого скрипта, в инсталляторе нужно разрешить установку «Examples» и «Binaries of examples».

Скрипт находится в каталоге: «<ProgramFiles>\LCPI\IBProvider.3\TestCode\ActiveX\IBP\test_system\prog»

Скрипт запускается из консоли (cmd.exe) через «cscript.exe».

Параметры запуска, которые будут указаны для проверки нашей базы данных:

/db_location «home2:e:\database\employee.fdb» /db_user sysdba /db_password masterkey /db_client fbclient.dll

В тестовой базе employee.fdb, которую мы будем подключать к MSSQL, этот скрипт нашел одну ошибку:

Основным результатом работы скрипта является пара файлов – «check_system_table.log» и «check_system_table.sql».

Находим в «check_system_table.log» описание ошибки:

46. [EMPLOYEE] — {TABLE}
1. [EMP_NO]
domain [EMPNO] already processed
………
10. [SALARY]
domain [SALARY] already processed
11. [FULL_NAME]
domain [RDB$9]
VARCHAR
charset_id: 0
charset_name: [NONE]
ERROR: Wrong char count: 0. BytesPerChar: 1. FieldLength: 37

FULL_NAME – это вычисляемая текстовая колонка. По непонятной причине, в описании этой колонки не определено количество символов.

В сгенерированном файле «check_system_table.sql» предлагается скрипт для исправления ошибки:

/*1 ——————————— */
/*
VARCHAR DOMAIN USED AT {TABLE}[EMPLOYEE].[FULL_NAME]
CSET_NAME : [NONE]
CSET_ID : [0]
BYTES_PER_CHAR : [1]
FIELD_LENGTH : [37]

CURRENT CHAR_LENGTH: [0]
*/

UPDATE RDB$FIELDS
SET RDB$CHARACTER_LENGTH=37
WHERE RDB$FIELD_NAME=
(SELECT RDB$FIELD_SOURCE
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME=’EMPLOYEE’ AND
RDB$FIELD_NAME=’FULL_NAME’) AND
RDB$CHARACTER_LENGTH=0;

/* ——————————— */
COMMIT;
/**/

Внимательно изучаем сгенерированные запросы для исправления базы данных и накатываем этот скрипт на нашу базу данных (например, через IBExpert).

Снова запускаем «prog_check_system_tables.wsf». На этот раз скрипт не нашел ошибок. Но это не значит, что их там нет. В процессе работы связанного сервера, возможно будут выявлены другие проблемы.

Внимание!
  • В случае вашей базы данных, все может оказаться гораздо сложнее.
  • Сначала потренируйтесь на тестовой базе данных.
  • Перед исправлением ошибок, рекомендуется сделать ПОЛНЫЙ бакап вашей базы данных.

5. Сконфигурируйте координатор распределенных транзакций (MSDTC)

Для выполнения запросов через связанный сервер нужна постоянно запущенная служба MSDTC.

  1. Откройте консоль для управления службами на сервере с MSSQL
  2. Найдите службу координатора
  3. Установите ей автоматический запуск и запустите её.

Настройка связанного сервера

1. Запустите «SQL Server Management Studio»

2. Подключитесь к MSSQL, на котором будет настраиваться связанный сервер

3. Настройте использование OLE DB провайдера

Найдите IBProvider в списке доступных провайдеров. Если его там нет, то попробуйте обновить список (Refresh). Если не помогло — значит на первом этапе вы не допустили ошибки.

Откройте диалог настройки свойств:

Поставьте галочки напротив следующих настроек:

  • Dynamic Parameters
  • Nested queries
  • Allow In Process (обязательно!)
  • Supports ‘Like’ operations

Закройте диалог (OK).

4. Создание связанного сервера

Перейдите на элемент «Linked Servers» и выберите в меню пункт «New Linked Server…»:

Откроется диалог для настройки связанного сервера.

Заполните поля на странице основных настроек.

«Linked Server»

Имя связанного сервера, которое будет использоваться в запросах. В нашем случае это будет FB_EMPL.

Provider

Выберите «LCPI OLE DB Provider for InterBase [v3]».

«Product Name», «Data Source»

Укажите в этих полях любой текст. В нашем случае, здесь будет продублировано имя связанного сервера – «FB_EMPL».

«Provider String»

Здесь определяются свойства для настройки работы провайдера. Рекомендуемая настройка:

Свойство Значение Заметка
auto_commit true Разрешаем автоматический старт и завершение транзакций
nested_trans true
truncate_char false
dbtime_rules 1 Только для MSSQL 2008, 2012+.
support_odbc_query true
asynch_fetch 2 Только для MSSQL 2005, 2008, 2012. Использовать для ускорения загрузки больших множеств.
dbclient_library gds32.dll
ibclient64.dll
fbclient.dll
Укажите имя или полный путь к DLL серверного клиента.
ctype Укажите имя кодовой страницы для текстовых данных вашей БД.
ctype_none Для БД, созданных с использованием кодовой страницы NONE. Укажите кодовую страницу ваших текстовых данных.
schema_ldr_cfg__check_constraints 0 Запрещаем публикацию сведений о CHECK-ограничениях в схемах метаданных.
schema_ldr_cfg__descriptions 0 Убираем описания объектов из схем метаданных для устранения проблем с ошибкой 8152.
ignore_err_param 4 Ослабляем проверку параметров запросов. Необходимо для запуска хранимых процедур.

В нашем случае, строка подключения выглядит так:

auto_commit=true;nested_trans=true;truncate_char=false;dbtime_rules=1;support_odbc_query=true;asynch_fetch=2;dbclient_library=fbclient.dll;ctype_none=ASCII;schema_ldr_cfg__check_constraints=0;schema_ldr_cfg__descriptions=0;ignore_err_param=4;

Если вы хотите подключаться к Firebird без использования fbclient.dll, используйте следующую строку подключения:

auto_commit=true;nested_trans=true;truncate_char=false;dbtime_rules=1;support_odbc_query=true;asynch_fetch=2;dbclient_type=fb.direct;ctype_none=ASCII;schema_ldr_cfg__check_constraints=0;schema_ldr_cfg__descriptions=0;ignore_err_param=4;

Вы можете проверить вашу строку подключения в тестовом скрипте из пункта «Проверка корректности установки провайдера и серверного клиента».

«Location»

Путь к подключаемой базе данных. В нашем случае база данных находится на сервере HOME2 в файле «e:\database\employee.fdb»:

HOME2:e:\database\employee.fdb

При подключении к Firebird без использования fbclient.dll (в строке подключения указано «dbclient_type=fb.direct») имеет смысл точно указать тип протокола подключения и версию TCP/IP:

inet4://HOME2/e:\database\employee.fdb

Это ускорит процесс подключения в сетях с одновременной поддержкой TCP/IP v4 и v6.

Переключитесь на страницу «Security» и укажите логии и пароль подключения к базе данных:

Переключитесь на страницу «Server Options»:

Рекомендуется установить свойства:

  • «Collation Compatible»=true
  • «RPC»=true
  • «RPC Out»=true

На этом настройка связанного сервера завершена – нажимаем «OK».

Если все было настроено правильно, то связанный сервер будет создан и появится в дереве объектов:

Общие правила работы с Linked Server

1. В SQL запросах, выполняемых через связанный сервер, следует указывать имена таблиц и колонок в верхнем регистре. Соответственно, в вашей базе данных Firebird/InterBase названия всех объектов, с которыми вы будете работать через связанный сервер, должны быть «upper-case».

В случае «mixed-case» имен объектов базы данных Firebird/InterBase, у вас возникнут проблемы связанные с ошибками в генераторе гетерогенных запросов MSSQL. Причина: в некоторых случаях, MSSQL генерирует запросы без использования кавычек для квотирования имен.

2. Для упрощения кода, будет использоваться уровень изоляции транзакций «по-умолчанию»: read_committed.

Работа со связанным сервером из «Microsoft SQL Server Management Studio»

Нажмите «New Query» для перехода в окно редактирования и запуска SQL скриптов.

Запрос на выборку данных с использованием явной транзакции

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;

Результат работы:

Если из строки подключения убрать «schema_ldr_cfg__check_constraints=0», то запрос к таблице EMPLOYEE будет генерировать следующее сообщение:

Msg 1046, Level 15, State 1, Line 1
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ‘)’.


(42 row(s) affected)

Это связанно с тем, что MSSQL, на стадии подготовки запроса, запрашивает у IBProvider-а различную информацию о таблице EMPLOYEE. В том числе, он получает информацию о CHECK-ограничении, которое и провоцирует данное сообщение:

salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country)

Указав в строке подключения «schema_ldr_cfg__check_constraints=0», мы полностью исключаем из схем метаданных сведения о CHECK-ограничениях. Как следствие – устраняем причины для такого рода сообщений.

Свойство инициализации «schema_ldr_cfg__check_constraints» доступно начиная с IBProvider v3.18.

Выборка данных с использованием автоматической транзакции

Поскольку в нашей строке подключения было указано «auto_commit=true», то мы можем опустить явный запуск и коммит транзакции.

select * from FB_EMPL...EMPLOYEE;

Результат работы запроса будет аналогичным – 42 строки.

В промышленном коде, рекомендуется всегда явно стартовать и завершать транзакции.

Уровень изоляции «автоматической транзакции» определяется в свойстве инициализации «auto_commit_level».

Выборка данных с использованием параметров

begin distributed transaction;

declare @first_name as varchar(32);

set @first_name='Scott';

select * from FB_EMPL...EMPLOYEE empl where empl.FIRST_NAME=@first_name;

commit;

Результат работы:

Модификация данных

При выполнении SQL-запросов INSERT, UPDATE и DELETE, MSSQL пытается стартовать вложенную транзакцию. IBProvider реализует поддержку вложенных транзакций через точки сохранения Firebird/InterBase. По-умолчанию эта поддержка отключена. Для её включения нужно указать в строке подключения «nested_trans=true».

Firebird поддерживает точки сохранения начиная с версии 1.5.

InterBase поддерживает точки сохранения начиная с версии 7.1.

Если вы работает со старой версией FB/IB, в которой отсутствует поддержка точек сохранения, то надо указать в начале скрипта:

SET XACT_ABORT ON;

Или вообще отказаться от явного управления транзакциями. В последнем случае, в строке подключения должно быть указано «auto_commit=true».

Вставка нового ряда

begin distributed transaction;

insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values ('Mars','Snickers');

commit transaction;

Результат работы:

Начиная с версии 3.50, IBProvider поддерживает DEFAULT-значения. Это позволяет правильно выполнять запросы вида «INSERT INTO … DEFAULT VALUES».

Создадим в тестовой базе данных таблицу:

CREATE TABLE TABLE6_5_DEF
(
 COL_SMALLINT SMALLINT DEFAULT 2,
 COL_INTEGER INTEGER DEFAULT 4
);

Теперь выполним следующие запросы через наш связанный сервер:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF default values;

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Так же, начиная с версии 3.50, корректно обрабатывается ключевое слово «DEFAULT» в списке значений колонок:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF values(22,DEFAULT);

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Последним примером, с демонстрацией поддержки DEFAULT-значений, будет частичное перечисление колонок таблицы:

begin distributed transaction;

insert into FB_EMPL...TABLE6_5_DEF (COL_SMALLINT) values(222);

select * from FB_EMPL...TABLE6_5_DEF;

rollback;

Результат работы:

Обновление ряда

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country;

commit;

Результат работы:

Удаление ряда

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

delete from FB_EMPL...COUNTRY where COUNTRY=@country;

commit;

Результат работы:

Выполнение скрипта

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

insert into FB_EMPL...COUNTRY (COUNTRY, CURRENCY) values (@country,'Mars');

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

update FB_EMPL...COUNTRY set CURRENCY='Beer' where COUNTRY=@country;

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

delete from FB_EMPL...COUNTRY where COUNTRY=@country;

select * from FB_EMPL...COUNTRY where COUNTRY=@country;

commit;

Результат работы:

Создание VIEW на основе связанного сервера

Создадим в тестовой базе MSSQL (с названием TEST) представление (VIEW) для таблицы COUNTRY:

use TEST;
go

create view V_COUNTRY (COUNTRY,CURRENCY)
as
select COUNTRY, CURRENCY
from FB_EMPL...COUNTRY;
go

Теперь выполним выборку из этого представления:

use TEST;
go

select * from V_COUNTRY;
go

Результат работы:

Давайте убедимся, что V_COUNTRY будет работать в рамках явной транзакции:

use TEST;
go

begin distributed transaction;

insert into FB_EMPL...COUNTRY (COUNTRY,CURRENCY) values('Russia', 'Ruble');

select * from V_COUNTRY where COUNTRY='Russia';

delete from FB_EMPL...COUNTRY where COUNTRY='Russia';

select * from V_COUNTRY where COUNTRY='Russia';

commit;

Результат работы:

Выборки из V_COUNTRY «видят» изменения в FB_EMPL…COUNTRY.

Вызов хранимой процедуры

У FB/IB есть два типа хранимых процедур:

  • Возвращающие результат в виде множества.
  • Возвращающие результат через OUT-параметры.

В обоих случаях, вы используете унифицированный синтаксис вызова:

EXEC name [param [,param …]];

Все остальное за вас сделает IBProvider.

Выполнение хранимой процедуры возвращающей множество

В employee.fdb есть хранимая процедура, которая возвращает идентификаторы проектов сотрудника:

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
    FOR SELECT PROJ_ID
             FROM EMPLOYEE_PROJECT
             WHERE EMP_NO = :EMP_NO
             INTO :PROJ_ID
    DO
        SUSPEND;
END

Получим список проектов для сотрудника «Bruce Young»:

begin distributed transaction;

declare @emp_no integer;

set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE
             where FIRST_NAME='Bruce' and LAST_NAME='Young');

print 'emp_no='+convert(varchar(32),@emp_no);

exec FB_EMPL...GET_EMP_PROJ @emp_no;

commit;

Результат работы:

Выполнение хранимой процедуры возвращающей OUT-параметры

В employee.fdb отсутствуют хранимые процедуры, возвращающие OUT-параметры. Поэтому создадим собственную процедуру, которая будет вычислять разность двух INTEGER-чисел:

CREATE PROCEDURE SP_SUB(A INTEGER, B INTEGER)
RETURNS (RESULT INTEGER)
AS
BEGIN
 RESULT=A-B;
END

Вызываем эту хранимую процедуру через Linked Server:

begin distributed transaction;

declare @A integer;
declare @R integer;

set @A=4;

exec FB_EMPL...SP_SUB @A, 1, @R output;

print 'R='+convert(varchar(32),@R);

commit;

Результат работы:

Выполнение запросов через инструкцию «EXEC AT»

Для запроса к связанному серверу, который не поддерживается MSSQL, можно воспользоваться инструкцией EXEC:

{ EXEC | EXECUTE }
    ( { @string_variable | [ N ] 'command_string [ ? ]' } [ + …n ]
        [ { , { value | @variable [ OUTPUT ] } } [ …n ] ]
    ) 
    [ AS { LOGIN | USER } = ' name ' ]
    [ AT linked_server_name ]
[;]

Через «exec(…) at» можно выполнить любые запросы, которые поддерживаются IB/FB, включая DDL и DML запросы с параметрами. Например, запрос «UPDATE RETURNING»:

begin distributed transaction;

declare @emp_no bigint;

set @emp_no=(select EMP_NO from FB_EMPL...EMPLOYEE
             where FIRST_NAME='Robert' and
                   LAST_NAME='Nelson');

print 'emp_no='+convert(varchar(32),@emp_no);

declare @fn varchar(32);

exec('update EMPLOYEE
      set FIRST_NAME=upper(FIRST_NAME)
      where EMP_NO=?
      returning NEW.FIRST_NAME',
     @emp_no,
     @fn OUTPUT) at FB_EMPL;

print 'first_name='+@fn;

rollback;

Результат работы:

Для работы с OUTPUT параметрами требуется IBProvider с версией не ниже 3.19 (сборка 15830). В строке подключения должно быть указано «ignore_err_param=4».

Уровни изоляций транзакций

По умолчанию, распределенная транзакция будет иметь уровень изоляции «READ COMMITTED». Вы можете указать другой уровень изоляции, используя команду:

SET TRANSACTION ISOLATION LEVEL <isolation_level_name>;

В случае распределенных транзакций, поддерживаются следующие уровни изоляции:

  • READ COMMITTED
  • REPEATABLE READ
В промышленном коде рекомендуется использовать уровень изоляции «REPEATABLE READ».

Работа в транзакции с уровнем изоляции «READ COMMITTED»

set transaction isolation level READ COMMITTED;

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;

Работа в транзакции с уровнем изоляции «REPEATABLE READ»

set transaction isolation level REPEATABLE READ;

begin distributed transaction;

select * from FB_EMPL...EMPLOYEE;

commit;

Дата публикации: 09.12.2013. Права на материал принадлежат: IBProvider. При перепечатке ссылка на сайт https://www.ibprovider.com/rus обязательна.