FAQ: MS SQL Linked Server for Firebird/Interbase

Discuss the Linked Server tehcnology, MS SQL Server and Business Intelligence environment

FAQ: MS SQL Linked Server for Firebird/Interbase

Postby Kovalenko Dmitry » 26 Nov 2005, 01:18

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

Use LCPI.IBProvider.3 for obtain a maximum comfort in work with Firebird and Interbase through MSSQL Linked Servers.

You can use LCPI.IBProvider.3.Lite also. But you can get degradation of performance for modifying operations (insert, update and delete) - MSSQL will use a "Client Cursor Engine", and unexpected problems with them.

So, we recomend to use LCPI.IBProvider.3

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.
Last edited by Kovalenko Dmitry on 28 Apr 2009, 21:48, edited 3 times in total.
Kovalenko Dmitry
Site Admin
 
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Postby Merkulov Andrew » 04 Dec 2007, 16:12

How to setup linked server in MS SQL Server 2005

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

Double click on provider name:
Image

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

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
User avatar
Merkulov Andrew
Site Admin
 
Posts: 222
Joined: 17 Nov 2005, 20:15

Postby Merkulov Andrew » 04 Dec 2007, 17:00

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
User avatar
Merkulov Andrew
Site Admin
 
Posts: 222
Joined: 17 Nov 2005, 20:15

Using IBProvider with SQL 2005 Express

Postby brettge » 07 Feb 2008, 19:51

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.
Brett G.
brettge
 
Posts: 5
Joined: 07 Feb 2008, 01:01

Postby Merkulov Andrew » 08 Feb 2008, 01:50

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
User avatar
Merkulov Andrew
Site Admin
 
Posts: 222
Joined: 17 Nov 2005, 20:15

Postby Merkulov Andrew » 08 Feb 2008, 11:29

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
User avatar
Merkulov Andrew
Site Admin
 
Posts: 222
Joined: 17 Nov 2005, 20:15

Postby Merkulov Andrew » 08 Feb 2008, 12:10

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
User avatar
Merkulov Andrew
Site Admin
 
Posts: 222
Joined: 17 Nov 2005, 20:15

Re: FAQ: MS SQL Linked Server for Firebird/Interbase

Postby Kovalenko Dmitry » 21 Sep 2010, 21:11

Tips for IBProvider v3

Define in connection string the "nested_trans=true" for avoid a "set xact_abort on" in your SQL scripts.
Kovalenko Dmitry
Site Admin
 
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: FAQ: MS SQL Linked Server for Firebird/Interbase

Postby Kovalenko Dmitry » 07 Sep 2011, 13:09

Tips for IBProvider v3 and MSSQL2008

You can define in connection string the "dbtime_rule=2" for receive values from TIME-columns with milliseconds.
Kovalenko Dmitry
Site Admin
 
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: FAQ: MS SQL Linked Server for Firebird/Interbase

Postby Kovalenko Dmitry » 23 Oct 2012, 11:58

Common rules for work with MSSQL x64

1. You should install IBProvider 64bit on to your computer with MSSQL x64

Linked server to Firebird database (32 or 64 bit)

General: IBProvider requires the 64bit client for Firebird server

1. Find the fbclient.dll from Firebird x64 installation kit. At current time recommended to use fbclient.dll from FB 2.5.2.
2. Copy fbclient.dll to system32 directory. Use Windows Explorer for this copy operation!
3. Define in connection string the "dbclient_library=fbclient.dll"

fbclient.dll (2.5.2) requires the VS2005 CRT (x64). Visit your customer area, download and install packages "Microsoft Visual C++ Redistributable Package (64bit)" with version 8.0.xxx.xxx

Linked server to Interbase XE database (32 or 64 bit)

General: IBProvider requires the 64bit client to Interbase XE server

1. Find the ibclient64.dll in your Interbase XE installation kit.
2. Copy it to system32 directory. Use Windows Explorer for this copy operation!
3. Define in connection string the "dbclient_library=ibclient64.dll"

ibclient64.dll requires the Visual Studio CRT (x64). Visit your customer area, download and install packages "Microsoft Visual C++ Redistributable Package (64bit)".

----
Advanced tips.

1. You can copy server client DLL (fbclient.dll, ibclient64.dll) in other directory than c:\windows\system32. For example - in "c:\server_clients_x64\". In this case you should define in "dbclient_library" full path to your server client DLL - "dbclient_library=c:\'server_clients_x64\fbclient.dll" or "dbclient_library=c:\'server_clients_x64\ibclient64.dll"
Kovalenko Dmitry
Site Admin
 
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk

Re: FAQ: MS SQL Linked Server for Firebird/Interbase

Postby Kovalenko Dmitry » 04 Apr 2013, 09:11

Look topic with discussion of the problem with DBSCHEMA_COLUMNS
Kovalenko Dmitry
Site Admin
 
Posts: 824
Joined: 17 Nov 2005, 20:51
Location: Russia, Lipetsk


Return to Using IBProvider in MS SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest