FAQ: MS SQL Linked Server for Firebird/Interbase

[Kovalenko Dmitry]

Hi people. Here the list of frequently asked questions related Linked Server technology.

Can IBProvider Free Edition work with Linked Server?
No, Free IBProvider can't work with linked server in MSSQL

If you would like to write data into the Firebird or Interbase use IBProvider v2 or v3

What is the valid connection string to the linked server?

Add the following parameters into the connection string:

truncate_char=false;
support_odbc_query=true;
support_square_delim_name=1;
user=sysdba;
password=masterkey;
ctype=win1251

WIN1251 - RUSSIAN codepage. You should use a codepage of your own database.

More connection string parameters here:
http://www.ibprovider.com/eng/documenta ... y_001.html

Database location ("data source" or "location") must contains server name or 'localhost' (server_name:server_path_to_database_file)

What SQL syntax can i using for Linked Server?
SQL queries in SQL Management Studio must be compatible with Transact SQL

---
What do I need to do if I have found a bug?

Before report about any error please read this article:
http://www.ibprovider.com/eng/documentation/con_to_ms.html

If you will find a bug please send report which contains:
1. IBProvider version (see file properties)
2. IBProvider filename
3. OS version (WinXp, Win 200, Vista ...)
4. MSSQL version
5. Interbase/Firebird version
6. Database charset by default
7. Metadata of tables (include information about charsets of columns)
8. SQL query.
9. Error message text or screenshot
-----
This will save your and our time.

[Merkulov Andrew]

How to setup linked server in MS SQL Server 2005

1. Setup selected provider (IBPRovider v2 or IBProvider v3)

Double click on provider name:


Set Allow In Process and Dynamic Parameter on Provider Properties page:


If you are not seen IBProvider v2 in the Providers list than IBProvider not properly installed. You must install IBProvider Professional Edition under administrator right.

Explore Program Files/LCPI_IBprovider/bin folder and find IBProvider_v2_trial.dll.

Try run command "regsvr32 IBProvider_v2_trial.dll"

2. Create linked server using the linked server wizzard or the following simple script:

Code: Select all

declare @LINKED_SERVER_NAME varchar(max);
declare @PRODUCT_NAME varchar(max);
declare @IBPROVIDER varchar(max);
declare @DATA_SOURCE varchar(max);
declare @CN_STR varchar(max);

set @LINKED_SERVER_NAME = N'FIREBIRD_SAMPLES';
set @PRODUCT_NAME       = N'EMPLOYEE_FDB';
set @IBPROVIDER         = N'LCPI.IBProvider.2';
set @DATA_SOURCE      = N'localhost:employee.fdb';
set @CN_STR   = 'truncate_char=false; support_odbc_query=true;
            support_square_delim_name=1; user=sysdba; password=masterkey; ctype=win1251';

/* DROP LINKED SERVER */
IF EXISTS (SELECT srv.name FROM sys.servers srv
   WHERE srv.server_id != 0 AND srv.name = @LINKED_SERVER_NAME)   
   EXEC master.dbo.sp_dropserver @server=@LINKED_SERVER_NAME, @droplogins='droplogins'

/* CREATE LINKED SERVER   */
EXEC master.dbo.sp_addlinkedserver
    @server =     @LINKED_SERVER_NAME,
    @srvproduct = @PRODUCT_NAME,
    @provider =   @IBPROVIDER,
    @datasrc =    @DATA_SOURCE,
    @provstr =    @CN_STR

/* set up Extended properties of the Linked Server */
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
    @optname='data access', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
    @optname='rpc', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
    @optname='rpc out', @optvalue='true'
EXEC master.dbo.sp_serveroption @server=@LINKED_SERVER_NAME,
    @optname='use remote collation', @optvalue='true'

Read how to run queries here:
http://www.ibprovider.com/eng/documentation/con_to_ms.html

[Merkulov Andrew]

You received error message when queried data in SQL Management Studio:

Here the list of possible messages with solutions:

1. Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "LCPI.IBProvider.3" for linked server "LINKED_SERVER"
reported an error. Provider caused a server fault in an external process.

Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "LCPI.IBProvider.3"
for linked server "LINKED_SERVER".

Solution:
Set up Allow In Process and Dynamic Parameter in Provider Properties page

2. Msg 8501, Level 16, State 3, Line 2
MSDTC on server ‘SERVER_NAME' is unavailable.

Solution:
Run the service «Distributed Transaction Coordinator»

3. Msg 7301, Level 16, State 2, Line 10
Cannot obtain the required interface ("IID_IRowsetChange")
from OLE DB provider "LCPI.IBProvider.3" for linked server "FIREBIRD_SAMPLES"

Solution:
You trying write data into the Firebird through the IBProvider v3 or IBProvider v1. Please use IBPorovider v2. for update operations

[brettge]

Problem:

In SQL Express 2005, there is no provider property page to set the required Allow In Process and Dynamic Parameter properties for IBProvider. In SQL 2005, these properties are no longer in the registry, so they cannot be set manually in the registry, either.

Solution:

SQL 2005 includes a new stored procedure to set these properties. Run the following from the SQL management studio:

/* Set up Allow In Process, Dynamic Paramater on SQL Express 2005 */
EXEC master.dbo.sp_MSset_oledb_prop N'LCPI.IBProvider.3', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'LCPI.IBProvider.3', N'DynamicParameters', 1

This will set these properties correctly and allow IBProvider to function as a linked server from SQL 2005 Express. I haven't tested it, but I suspect this also works in other versions of SQL 2005.

[Merkulov Andrew]

How to bypass conversion errors while quering bad formatted data from Interbase?

For example for date value "'1/15/0272" MS SQL has raised the "Error converting data type DBTYPE_DBTIMESTAMP to datetime"):

A Solution here:
http://www.ibprovider.com/forum/site/viewtopic.php?t=600

[Merkulov Andrew]

Questions related to SQL Server Service pack 2:

Why I can't see tables list for Linked server?

The Linked Servers node in Object Explorer now displays system catalogs and user database objects. As soon as the linked server is created, you can expand its node to show tables and views on the remote server. This feature is supported in SQL Server 2005 Express Edition SP2.

Where the 'Test Connection' button?
The New Linked Server dialog box now includes a Test Connection button.
You must install SQL Server Service pack 2

Features list for SQL Server SP2:
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

[Merkulov Andrew]

New samples for linked server are available:
- Select, insert, update and delete record
- CTE (Common Table Expressions) - creating dinamic result set (dinamic view)
- CTE and recursion
- OPEN Cursor
- PIVOT/UNPIVOT operators
- TOP operator
- TRY .. CATCH and error handling

Download them here:
http://www.ibprovider.com/download/Samples/LinkedServer.Tests.rar