SQLBase to SQL Server Drivers

[yubeck]

Hi,

I'm new to SQLBase - never used it before, and I'm really struggling to connect to it via SQL Server using either Linked server or SSIS package.
Our versions = sqlbase 11.7.3, sql server 2016 64 bit, visual studio 2015.
I have tried to download drivers but can only get v9 & v10 , every other download attempt results in 404 error (i keep reading that i need v11.7, but no idea where driver is for this).
So i install both of these driver packs on our server & update the .ini file.
For the Linked server approach then i never see any new sql provider, so not sure what im doing wrong here, and when i use the providers already there i get this error message (presumably both to do with bad provider, and wrong driver?):
OLE DB provider "MSDASQL" for linked server "GH" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".

I have tried to set this up in an SSIS package too using ODBC driver - this works for some tables, but for other tables (i.e. i am trying to retrieve just one column from a particular table, of data type DT_I4 at both source and destination) I am seeing the following error on execution and can't understand what the issue is or find the error state code on Microsofts website for more info:
[Audiogramhead [2]] Error: Open Database Connectivity (ODBC) error occurred. state: '22005'. Native Error Code: 0. [Gupta][ODBC Driver]Data type conversion error

Could anybody please help or provide suggestions on what to do or where to find right resources? I am just failing repeatedly with this and really need to get into the SQLBase database
I have tried to follow this post too as they seem to be wanting to do the exact same thing as me to AUDITBASE

Thank-you

[yubeck]

Sorry i also forgot to mention i downloaded the trial version (but this is v12.1) and ran through the same processes - this is what I am working with right now.

Thanks again

[Steve Leighton]

Firstly, I recommend you use OLE DB. I am using this right now for the exact same purpose and it works fine.
Secondly, you need to get your bitness right.

SQLBase 11.7 can be 32bit or 64bit , so the 32bit SQLBase OLE DB driver ( SQLBASEOLEDB.dll ) which comes with it, will not display or work as a Provider in 64bit SSMS.
SQLBase v12.1 can also be either 32bit or 64bit , so you need to be sure which one you are running. Use 32bit SSMS if you are running 32bit SQLBase.

Once you get the 2 bitnesses matched up, SQLBase OLE driver will display as a Provider in SSMS.

Then you can go here: viewtopic.php?f=20&t=58 for the correct SELECT, UPDATE, INSERT syntax .

You can go here: https://blogs.msdn.microsoft.com/docast ... -instance/ to see someone having exactly the same issue with 32bit Oracle <--> SQLServer

[yubeck]

Ahh great thank you, I'd obviously got the 32 bit version running It's now appeared, but I'm still having config issues, and can't seem to find anything in documentation. I'm getting the following 2 error messages upon testing the linked server connection. The first is when i put the 'C:\Program Files\Gupta\SQLBase 12.1\sql.ini' path in the 'provider string' box, the second is when its in the 'data source' box:

OLE DB provider "SQLBASEOLEDB" for linked server returned message "Cannot open database". (Microsoft SQL Server, Error: 7399)

OLE DB provider "SQLBASEOLEDB" for linked server returned message "Database/user/password is too large". (Microsoft SQL Server, Error: 7399)

I saw a similar post that suggested it may be looking for a different .ini file when you get the first error, so i tried adding the xxx=xxx line into the file, and I still got the same message.
However, there are no other .ini files on my server, i removed everything when i uninstalled. I don't know if I'm adding properties in the wrong boxes or missing some information? Are you able to give examples of what should be populated in each box? I'm not sure if I need to tick any of the options in the SQLBASEOLEDB provider properties either to get it working, or should these all be left unchecked? Would you be able to advise?
Thanks again.

[Steve Leighton]

Attached are 2 TSQL scripts to create a SQLBase <--> SQLServer OLE DB Linked Server .

One uses sql.ini to connect, and one uses the SQLBase host IP. You can use either.

Be sure to change :
-the SQLBase database name ( 'MYDATABASE' in the script ) ,
-the Linked Server name ( 'MYDATABASELINK' in the script ) ,
-the path to sql.ini ( if you use that script ),
-the SQLBase server IP ( if you use that script ),
-the SQLBase Username and password ( 'SYSADM' in the scripts )
-make sure SQLBase is up and running.
in which ever script you use .

You may need to register SQLBASEOLEDB.dll using regsrvr, but highly unlikely.

LinkedServerCreate - SQLBASEOLEDB - with SQL_INI.zip

LinkedServerCreate - SQLBASEOLEDB - with HOSTNAME.zip

[yubeck]

Thanks ever so much, this is great. Really appreciate your help

[wheely]

Hello,

At first: Thank you for sharing your SQL-scripts

I just run into a problem, I only got SQLBase 12.0 with OLEDB Driver 120, If i run a query on MS SQL all I get is:

Meldung 7399, Ebene 16, Status 1, Zeile 109
Der OLE DB-Anbieter 'SQLBASEOLEDB.120' für den Verbindungsserver 'SQLBASE' hat einen Fehler gemeldet. Zugriff verweigert.
Meldung 7350, Ebene 16, Status 2, Zeile 109
Die Spalteninformationen können nicht vom OLE DB-Anbieter 'SQLBASEOLEDB.120' für den Verbindungsserver 'SQLBASE' abgerufen werden.

Any idea about that?
Thank you!


EDIT: Sorry already got it:

Once you have created a Linked Server in SqlServer , be sure to 'Allow Inprocesses' ( Right click properties of SQLBASEOLEDB under 'Providers' in SSMS)

[wheely]

Hello,

Linked server works perfect with OLEDB but not on tables with columntype LONG VARCHAR. MSSQL immediately show an error after a SELECT-Query:

Der OLE DB-Anbieter 'SQLBASEOLEDB' für den Verbindungsserver 'GUPTA' hat die Meldung 'Klasse nicht registriert' zurückgeben.
Meldung 7341, Ebene 16, Status 2, Zeile 41
Der aktuelle Zeilenwert der [SQLBASEOLEDB].BESCHREIBUNG-Spalte kann nicht vom OLE DB-Anbieter 'SQLBASEOLEDB' für den Verbindungsserver 'GUPTA' abgerufen werden.

Did anyone run into the same problem?
Kind regards

[Steve Leighton]

..
Please clarify:

Are you Querying your Linked Server from SSMS or TeamDeveloper ?

From within SSMS, I can confirm that Selecting a LONG VARCHAR column DOES work using the following syntax:

Code: Select all

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
GO
--Select:
SELECT * FROM OPENQUERY( MYDATABASELINK, 'Select DESCRIPTION from SYSADM.INCIDENT where ID = 209284') 

in this case 'DESCRIPTION' column is Long Varchar, and returns the correct text.
Suggest that if you are querying from TeamDeveloper, then you post the code you are using as a zipped apl, so it can be tested.

[FRBhote]

Unable to connect. What could be wrong?

Command is:

EXEC master.dbo.sp_addlinkedserver @server = N'MYDATABASELINK', @srvproduct=N'SQLBase', @provider=N'SQLBASEOLEDB.DLL', @datasrc=N'SBTASK', @provstr=N'ini=D:\\SB122\\sql.ini;isolation=rc;ReadCommitted=R3'

1.png

2.png