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.