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

Hi Michael,

Thanks for your help with this! I've tried using Access but found that Access doesn't update the odbc-linked tables dynamically - it has to either be done manually or done using the Windows Scheduler. Unfortunately, the Windows Scheduler also needs to lock the Access db exclusively and that was creating a problem when the Access db was also linked to another platform. I tried creating a Linked Server in SQL server using an Access db anyway and starting running into other problems so I'm just backing quietly out of the rabbit hole I've gone down.

I really hope I can get Adagio hooked directly to SQL Server - we were told that it's definitely possible and I have to think that there's someone out there who's done it.

Our test had the 2017 SQL Database and Adagio database on the same machine. Let's call it the Adagio Server, and the provider string worked for setting up the Linked Server.

You might want to give that a try.

SQL 2017 won't have any problem with a remote machine accessing the SQL database on the Adagio Server.

This is promising. It gives me an error though: "Missing ADSPROF". I replaced both "D:\DataFolder" entries you had with the data folders I'm using, both using drive letters and UNC path (I'm assuming I'm supposed to do that) and confirmed that ADSPROF is in the data folder.

Interestingly, I tried the DNS connection again and was able to expand the Catalogs to show a default db and under that I'm seeing the table that I'm after, APVendorMaster__ap92avms, but it says that it "contains no columns that can be selected or the current user does not have permissions on that object". Is this expected? I've confirmed that the user that SQL server is running under has full permissions to the entire drive.

Not expanding is expected, this from above..

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]

Sorry, I should have clarified. The "contains no columns that can be selected" error I'm getting is when I try to Script Table as | Select To | New Query Editor Window (having updated the [LinkedServerName] and [TableName] values to the ones I'm using).

Edit. (sorry, it's been a busy Friday). I'm getting the "missing ADSPROF" error when using the Connection String and the "contains no columns" error when using the 64-bit DSN.

Please see last entry above - any comments?

Have you setup SQLServer/SQLExpress 2017 on the server that has Adagio data & software installed?

I do not believe that you can have the SQLServer remote from the location of the Adagio data/software.