MSSQL 6.5(too old) is it possible to connect?

[hamacker]

Hi ALL,

New job here, I need to migrate old dababase MSSQL 6.5 w/ BDE+Delphi7 to FirebirdSQL/Lazarus.
Old Delphi use BDE w/ SQL Links to access MSSQL version 6.5 with several stored procedures, it´s run fine because BDE use C:\MSSQL\BINN\ntwdblib.dll directly to talk. But in my tests, Zeos not.
I try ODBC drivers, but not installable in Win10/11, just Win7.

There is a possibility to use zeos to access MSSQL 6.5 or other method that I can do that?

[marsupilami]

Hmm - I see several possibilities:

• Continue to use the BDE for migrating the data. Read the data using the BDE and write it to Firebird using Zeos.
• Use Zeos 7.2 for connecting. Zeos 7.2 still has support for the ntwdblib.dll. But be careful and double check if the data was transferred correctly. Also Zeos 7.2 has no support for Numeric/Decimal fields.
• Try to use Zeos 8.0 with freetds. Recent builds can be found their automated build system. Take care because these buildes require the C++ runtime environment according to the version of C++ they were built with.

If you try to access the data using Zeos, you might run into problems regarding the way Zeos reads metadata. The queries we use might not work on this old server. In that case we would have to check what to do. The oldest SQL server we test from time to time is SQL Server 2000.

Another option might be to check if you somehow can migrate the database to a newer version of SQL Server without changing the compatibility level of the database. Maybe newer versions (SQL Server 2005?)can read the old database files or something like that. If the database is small enough, you might be able to use an express edition.

[hamacker]

What is FreeDTS?
It´s free to use?
How to install in Windows enviroment?
There is no FreeDTS in protocol of zconnection (zeos8).

Hmm - I see several possibilities:

• Continue to use the BDE for migrating the data. Read the data using the BDE and write it to Firebird using Zeos.
• Use Zeos 7.2 for connecting. Zeos 7.2 still has support for the ntwdblib.dll. But be careful and double check if the data was transferred correctly. Also Zeos 7.2 has no support for Numeric/Decimal fields.
• Try to use Zeos 8.0 with freetds. Recent builds can be found their automated build system. Take care because these buildes require the C++ runtime environment according to the version of C++ they were built with.

If you try to access the data using Zeos, you might run into problems regarding the way Zeos reads metadata. The queries we use might not work on this old server. In that case we would have to check what to do. The oldest SQL server we test from time to time is SQL Server 2000.

Another option might be to check if you somehow can migrate the database to a newer version of SQL Server without changing the compatibility level of the database. Maybe newer versions (SQL Server 2005?)can read the old database files or something like that. If the database is small enough, you might be able to use an express edition.

[marsupilami]

What is FreeDTS?

FreeTDS basically is a free reimplementation of the API the ntwdblib.dll uses. See www.freetds.org.

It´s free to use?

Yes - it is free to use.

How to install in Windows enviroment?

You can download the library from their automated build system, if you only need one of the UTF-8, ISO-8859-1 or ISO 8859-2 character sets. Unfortunately they keep the build artifacts for one month only there.
You can also download a build from our files section. FreeTDS doesn't need a special installation. Just put the libraries in a folder and point Zeos to sybdb.dll / lilbsysbdb-5.dll.

There is no FreeDTS in protocol of zconnection (zeos8).

In Zeos 8 just use the mssql protocol.

[hamacker]

Well, not good.
I try ct.dll and sybdb.dll with zeos8 and result is:
System Error, (OS Code 193
I see another files like to bsqlodbc.exe, maybe to create another ways via ODBC, but I not understand how to execute them.

Maybe if I have this DLLs more than 10 years ago, but I´am not lucky.
I keep search over the internet and test some DLLs that I found like dblib_2000.dll and libsybdb-5.dll but without sucess again.

So I quit and test IB Pump from
https://github.com/CleverComponents/Interbase-DataPump
to transfer from BDE Database to Firebird, but it´s fail without sense in correct query. IB Pump is writen for Delphi7 and IBX components, maybe too old.

When I still run with BDE, the only way is write my own transfer program. If you know another program to do transfer, tip will be welcome.

Thanks a lot.

[marsupilami]

Hmmm - error code 193 is "%1 is not a valid Win32 application.". Maybe the dll file was destroyed at some part? Usually you want to use sybdb.dll. ct.dll is for another API (ctlib API).

I do have a data pump component that can be used to transfer data. It is not open source (yet) and not that simple to use. But it can be used in customizable ways. Let me know and I can upload it.

[Fr0sT]

Why not export data as good old plain SQL script?

[hamacker]

This version MSSQL Enterprise Manager does not generate SQL Script for data, only meta. But there is a transfer data utility to another MSSQL. I will try if MSSQL 2019 can import it from this transfer or from backup/restore.

[marsupilami]

I will try if MSSQL 2019 can import it from this transfer or from backup/restore.

Even if SQL Server 2019 cannot read the backup, maybe older versions can? Do you have access to older versions? How big is the database?

[Fr0sT]

This version MSSQL Enterprise Manager does not generate SQL Script for data, only meta.

You mean, 6.5? You can try installing a bit more fresh version like 2000 and do backup/restore to it. Then export data as SQL. Old SQL server versions still can be found on Internet.
And you always can write your own app to transfer the data, it's really not so hard

[hamacker]

Yes, I need to found this versions:
MSSQL 6.5->7.0->2000->2005->2008R2

not peace of cake found them, but I will try.

[Fr0sT]

Yeah, pretty hard task. Recently I was surfing the web searching for versions > 2000 and found express 2005 at last but that was not easy. But you can stop at 2000 as it has SQL data export already.
Btw, have you tried DB managers like DBeaver? Probably it could connect to 6.5 and do export

[Fr0sT]

BTW, I have 2000 and 2005 installed and I can check if 6.5's backup is restorable by these versions. Just provide some dummy test backup

[hamacker]

Yes, all of them refuse to connect using Win10, maybe is different using old windows versions.
Just BDE may something different using Win10, because connect without any error. I think because BDE uses dblib correctly and others no way to use it, maybe prefer ctlib or newer and in win10 there is a hidden replacement for microsoft products, I suspect.
In old delphi there is two BDE for two differnts delphi, regular BDE for Delphi Workstation and BDE with SQL Links that came only in Delphi Enterprise+ . Only with SQL Links that I can connect DB2, MSSQL, Oracle,...

Now Is more clear that I need to do, thanks a lot.