I'm new to connecting to Adagio data, so apologies in advance.

My customer has Adagio ODBC 9.0A, and I would like to retrieve data using a SQL linked server. The first attempts failed, as I tried to use the ODBC driver on 64 bit SQL. After finding out that the 64 bit driver is only available after 9.1, and customer does not want to update to a later version because they do not want to break existing Crystal Reports, I decided that I would just install a 32 bit version of SQL on the server. Still no luck. I installed 32 bit SQL Express 2008. When I try to created the linked server, the SQL service actually stops running entirely. I also installed 32 bit SQl Express 2012. When trying to create a linked server on that instance, it never seems to fully create. I left the process executing for 27 hours and finally had to manually stop it.

I don't seem to be having any trouble setting up the DSN.

Ultimately, my goal is just to retrieve AR and inventory data for reporting. We have a cloud based reporting solution and I would like to extract pertinent Adagio data and store it in the cloud database. Having the data in SQL tables is ideal, as that is how we currently handle data extractions from other systems.

Am I missing something when trying to set up this linked server in SQL? I have set up several ODBC linked servers before using other drivers. I can't seem to get my hands around this one. Any help or advice would be appreciated.

Adagio ODBC 9.0A does not work with Linked Servers.

Adagio ODBC 9.2A (2015.10.27) does work with Link Servers.

You may have both 9.0A AND 9.2A installed at the same site.

Different names are used for the Drivers (AdagioODBC and AdagioODBCV2), we did not want to break Crystal Reports that used ODBC 9.0A

At least I feel like I'm not crazy anymore...

I'll see about getting the V2 driver installed.

Thank you for the reply.

Michael,

Can I just update the Adagio ODBC 9.0a to 9.2A with no ill effects to operations that are using the 9.0A. I am wanting to be sure I don't break other operations and I know little about ODBC (this is our only client using ODBC).

Or do I need to take special steps to preserve the 9.0a.

Thanks,
David

There are no conflicts between ODBC 9.0A and 9.2A components that are installed. Both are based on the SIMBA engine. SIMBA changed component names and so did Softrak.

Did that work for you Dave?

I ask because I have 9.2A and can't get it working with 64-bit SQL Server 2017. When I try to set up a linked server it just hourglasses forever. Adagio is on the network rather than being local to this server - could that be an issue?

Edit. the DSN is setup and appears correct. It works with Excel.

You must use a 64Bit DSN for 64-bit SQL Server 2017

Maybe it is working with Excel because you are using a 32 Bit DSN? In Excel File | Help | will show if are running 32 or 64 bit Excel

For the DSN table selection do not use * and do not tick All. Try with AR9* or some known table name.

Mi Michael, we're using a 64-bit System DSN with a specific table listed, AP92AVMS. We're using the UNC path for the Data Directory rather than the drive letter.

Edit. we just updated the permissions on the entire network drive to give the account access to the whole thing, just in case there was a permissions issue. Still no change. For the Provider we're using "Microsoft OLE DB Provider for ODBC Drivers" - I'm assuming that's correct.

Edit. if I terminate SSMS and restart it I see the Adagio entry under the Linked Servers and it has a Catalogs folder underneath it but that's all I get. If I try to test the server or expand the Catalogs folder it hangs again. So it appears to getting part of the way through the process

Please attempt to have Excel connect to the data using

From Other Sources
From Data Connection Wizard
ODBC DSN
Select the DSN mentioned above for AP92AVMS

Does the rows come back to Excel?


Oops.. see that you say earlier in thread this does work !

I will see if I can get a SQL2017 up and running to test. May take a bit of time.

For now a 2 step process might be all that will work.

1) Data to Access Database using Adagio ODBC
2) Then SQL 2017 Linked Server using the Access Database

We duplicated the SQL 2017 problem with the DSN, no immediate fix but try this workaround.

Rather that use a DSN use a fully formatted Provider String when defining the Linked Server

Provider is
Microsoft OLEDB Provider for ODBC Drivers

Product Name is selected from list
AdagioDataSourceDriver

Data Source is not used

Provider string as follow adapted for your situation

DRIVER=AdagioDataSourceDriver;
USERID=SYS;
TABLESALL=FALSE;
RAWPASSWORD=1;
PASSWORD=SYS;
OPTIMIZE=LIVE;
NAMES=LONG;
DBSELELECTOR=SAM;
DBQ=D:\DataFolder SAM;
DBFILTER={AL92APIN};
DBDIRECTORY=D:\DataFolder;
COLUMNS=COMMON

The column names do not expand for the linked server.

Script Table as | Select To | New Query Editor Window will create a script but will need to be edited into the form

Select * from [LinkedServerName]...[TableName]

Using information that is correct for your Server & Table names