Publish date: 2003-06-24
Updated: 2018-10-26

Registration and work with Linked Server of MSSQL 2000.

This guide is out of date. Recommended to read a new.

Creating a linked server

  1. Install IBProvider on a server where MSSQL works.
  2. Launch «SQL Server Enterprise Manager».
  3. Connect to MSSQL server that will work with InterBase database.
  4. Go to tree item «Sequrity\Linked Servers».

    Sequrity\Linked Servers

  5. Run menu item «New Linked Server».
  6. Fill the fields:

    Linked Server Linked server name which will be used in SQL query texts.
    Provider Name LCPI OLE DB Provider for InterBase [v2]
    Data Source Absolute path to InterBase database.
    Provider String Database connection string. Required parameters are:


    Linked Server Properties

  7. Press «Provider Options» button. Check the items:
    • Dynamic Parameters
    • Allow InProcess
    Close dialog by pressing «OK».
  8. Go to page «Server Options». Check the items:
    • Data Access
    • RPC
    • RPC Out
    • Use Remote Collation
    RPC markers indicate that provider supports call-in of stored procedures. Check that «support_odbc_query=true» appears in the connection string.
  9. Close dialog «Linked Server Properties — New Linked Server» by pressing «OK».

If you correctly set the connection parameters, then you can look over tables and views of the connected database.

SQL Server Enterprise Manager

How to execute queries to database from «SQL Query Analyzer»

Launch «SQL Query Analyzer» and connect to MSSQL server on which you have registered the linked InterBase server.

SELECT query

begin distributed transaction;

select * from IB_EMPL...EMPLOYEE;

commit;

Parameterized SELECT query

begin distributed transaction;

declare @first_name as varchar(32);

set @first_name='Scott';

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

commit;

Data modification

When executing SQL queries (INSERT, UPDATE, DELETE) MSSQL tries to start nested transaction. The provider returns error if transaction is active already. Therefore, before executing such queries, you need to execute command «SET XACT_ABORT ON», or you can reject explicit control of transactions at all by indicating «auto_commit=true» in connection string.

Inserting row

set xact_abort on;

begin distributed transaction;

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

commit transaction;

Updating row

set xact_abort on;

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

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

commit;

Deleting row

set xact_abort on;

begin distributed transaction;

declare @country varchar(32);

set @country='Mars';

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

commit;

Call-in of stored procedure

MS SQL always uses quoted names to call stored procedures. Use gfix to convert database employee.gdb into third dialect.

Obtaining SP result as dataset (odbc_call_sp=0) 

begin distributed transaction;

declare @dept_head char(3);

set @dept_head='100';

exec IB_EMPL...SUB_TOT_BUDGET @dept_head;

{call IB_EMPL...SUB_TOT_BUDGET(@dept_head)};

commit;

Obtaining SP result through OUT-parameters. Use special linked-server IB_EMPL_1 with parameter odbc_call_sp=1 

begin distributed transaction;

declare @dept_head char(3);
declare @tot_b numeric(15,2);
declare @avg_b numeric(15,2);
declare @min_b numeric(15,2);
declare @max_b numeric(15,2);

set @dept_head='100';

exec IB_EMPL_1...SUB_TOT_BUDGET @dept_head,@tot_b <b>output</b>,@avg_b output,@min_b output, @max_b output;

print 'tot_b='+convert(varchar(32),@tot_b);
print 'avg_b='+convert(varchar(32),@avg_b);
print 'min_b='+convert(varchar(32),@min_b);
print 'max_b='+convert(varchar(32),@max_b);

{call IB_EMPL_1...SUB_TOT_BUDGET(@dept_head,@tot_b output,@avg_b output,@min_b output, @max_b output)};

print 'tot_b='+convert(varchar(32),@tot_b);
print 'avg_b='+convert(varchar(32),@avg_b);
print 'min_b='+convert(varchar(32),@min_b);
print 'max_b='+convert(varchar(32),@max_b);

commit;
If you use IBProvider v3, then you can use automatic determination of SP result type. For this you need to set odbc_call_sp=2.

How to execute queries to linked server through ADO

Queries execution from «SQL Query Analyzer» even if it is of any interest, then only from the category of theoretical.Real benefit can be obtained only if we will be able to work with InterBase data through MSSQL from real applications.

For experiments we used ADO and Windows Script Host components and found out as following:

  • It was impossible to execute parameterized query to SP. IN-parameters values must be substituted directly in the query text.
  • As a consequence — the result set is the only method of obtaining SP results (odbc_call_sp=0). That is to say, prestored procedure must have ‘SUSPEND’ keyword.

Usefull links

Publish date: 2003-06-24. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.
All documentation