2

We have a POS system running a Firebird database and the rest of the business is on SYSPRO which is SQL Server based.

I would like to run queries and views between the databases as both databases have unique keys which can be matched.

Do I do this through a Linked Server? I have tried a few variations of linked server and I keep getting various error messages. So this is what I have so far:

EXEC master.dbo.sp_addlinkedserver 
@server = N'OMNI', 
@srvproduct=N'OMNI',
@provider=N'MSDASQL', 
@datasrc=N'C:\Omni\Company\Data\databasefile.FDB',
@provstr=N'Driver={Firebird/InterBase(r) driver};Dbname=C:\Omni\Company\Data\databasefile.FDB;CHARSET=NONE;UID=SYSDBA;'

I have also tried connecting using Excel and I can see the database and view the tables using the following connection string:

Provider=MSDASQL.1;Persist Security Info=True;Data Source=Omni;Extended Properties="DSN=Omni;Driver={Firebird/InterBase(r) driver};Dbname=C:\Omni\Company\Data\databasefile.FDB;CHARSET=NONE;UID=SYSDBA;";Initial Catalog=Omni
Improve this question
6
  • Linked servers can be unreliable. Distributed queries (across linked servers) usually perform quite badly. It's a completely valid approach to replicate your Firebird data into a SQL Server database and just do cross database queries. Then you might find you want to take it up a notch and build a data warehouse. Commented Dec 15, 2016 at 12:01
  • Hey Nick. Thanks for the feedback. So what would be the best approach to replicating the databases? Would I create a SQL Sever Agent hourly task to check for new data? My background to all this is that I can build queries, but replicate, triggers and those things, I still need to learn. ;-) Commented Dec 15, 2016 at 12:12
  • What errors do you get? Commented Dec 15, 2016 at 12:28
  • Hi Mark. There are a few. 1 is User error eg no authorisation, 2. others are that it cannot find the .dll file, 3. cannot find the DSN called OMNI. Commented Dec 15, 2016 at 13:25
  • 1
    Basic replication is just copying the tables of interest locally with a tool like SSIS. You can start by truncating and reloading the data every day. Next step is to pick a window (say 3 days) and just delete and reload 3 days based on a reliable date (i.e. transaction date). Have a think about that but first chase down the linked server approach and confirm whether or not it works for you. To troubleshoot linked servers you need to list out the things you've tried and what error messages you get. Commented Dec 15, 2016 at 23:36

2 Answers 2

Reset to default
2

Ok here is the answer (I have a short patch of hair left after this ;-) )

I cannot answer the technical questions as to why this works, but it works.

Firstly you need to install

Firebird 2.5

Then the 32 Bit Driver

32 Bit

Then the 64 Bit Driver

64 Bit

Then you go to you Command Prompt (administrator). Goto where you installed Firebird 2.5. In my case it was

C:\Program Files (x86)\Firebird\Firebird_2_5\bin\

Then enter:

fbguard.exe - a

This will get a version of the Firebird server going.

Then it is a simple setup of an ODBC link.

If you are trying to link to a local instance of the database, do not forget to put

localhost:\Company\Data\Companydata.fdb
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

The short answer is yes, it works (having the correct driver, sql side).

Let me try to simplify this scenario. In fact, it is not clear what kind of installation is the OP speaking about.

SQL Server must use either ODBC or OLE DB drivers. unfortunately, official Firebird drivers are dated 2017. Some third parties do them, devart, ibprovider, ibphonix, etc. I did my tests using the ibprovider ones.

The initial post is showing this connection string (partial): "Dbname=C:\Omni\Company\Data\databasefile.FDB".

Said that the traditional style for connecting via TCP/IP is "IP address/port:database_file_path", the OP's seems to face with an embedded Firebird installation. The difference is that, in this latter case, there is no Firebird service answering requests. Everything is done via a DLL, that's usually named fbembed.dll.

Installing Firebird as a service, as proposed, certainly fix the issue, because, after this installation, there is a (Windows) service always running and taking care of databases. However, if you do not want/need this service, MS SQL Server can work with the "embedded" installation too.

The main rule, I hardly discovered, is: the bunch of needed Firebird DLLs (but fbembed.dll) must be installed in the Windows system folder (in the manual it is reported exactly the contrary...).

To sum up, I have built a working server in this way.

0) I have benn always useing the ibprovider driver because their developers provide a great support service. And, some basic instructions can be found here too: https://www.ibprovider.com/eng/documentation/mssql_2012.html

1) I started downloading the official embedded package.

2) In the data folder, I have put the database, the fbembed.dll, and the firebird.msg

3) In the windows system folder, I have registered the minimum firebird DLL needed: icuuc30, icuin30, icudt30, and ib_util.

4) I've then created a linked server (yep, OPENROWSET is exactly the same) using the following string (if you need the fbclient.dll, simply copy/paste the embeded.dll and rename it):

EXEC master.dbo.sp_addlinkedserver @server = N'myName', @srvproduct=N'myName', @provider=N'LCPI.IBProvider.3', @datasrc=N'myName', @provstr=N'location=E:\mypath\MYDB.GDB;dbclient_library_64=E:\mypath\fbembed.dll;auto_commit=true;nested_trans=true;truncate_char=false;dbtime_rules=1;support_odbc_query=true;asynch_fetch=2;ignore_err_param=4;schema_ldr_cfg__check_constraints=0;schema_ldr_cfg__descriptions=0;dbclient_type=fb'

5) Security isn't a concern. The DLL does not manage it, BUT you need to pass a userid/password pair: 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myName',@useself=N'False',@locallogin=NULL,@rmtuser=N'SYSDBA',@rmtpassword='anytring'

6) you do need a couple of special options to be set to True: Collation, Allow inprocess, RPC, RPC Out.

Yes it works. And it saves at least a couple of MB ram as you haven't an always-run service.

Last but not least, thanks to the ibprovider.com developers that help me to sort it out.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.