0

This must be simple but I can't find the documentation. I have an ODBC connection to an Intersystems Cache' database that works fine in MS Access, but I can't see how to do the same in SQL Server (15.0.18930.0).

In Access, I did this: Blank Desktop Database, Create, External Data tab, ODBC Database, Link to the data source, (Select Data Source) --> Machine Data Source --> click on ODBC data source we already created. Then it gives me a list of tables to link to, and we're done.

The data source was System DSN, with various fields and a connection string like

DRIVER={InterSystems ODBC35};SERVER=xxxx;PORT=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;

How is this done with SQL Server?

Thanks!

Mike Arrh
  • 145
  • 9
  • 1
    What do you mean by SQL Server 18.10? There is no such thing. The latest version of SQL Server is 2019, which is version 15, and the latest build is 15.0.4198.2. In truth it is very rare that Microsoft increase the minor build number of the product (I think the only time they did was SQL Server 2008R2) and so even when we get the version 18 (SQL Server 2026?) it's highly unlikely we'd have a version 18.10. – Thom A Apr 13 '22 at 19:19
  • Hmm. In "About" it has a list of things, and for SQL Server Management Studio it says 15.0.18930.0. What I wrote was at the top right, above the list. I'll amend the title. – Mike Arrh Apr 13 '22 at 20:16
  • SQL Server Management Studio <> SQL Server, SSMS is just an IDE like application. To get the version of SQL Server you need to run `PRINT @@VERSION;`. – Thom A Apr 13 '22 at 20:57
  • Well, you first have to install + setup the drivers Intersystems on that SAME server where SQL server is running. And you need to ensure that you install the x64 bit version of those drivers (where as for Access, you had to install the x32 bit drivers). Once you done that, then you have to setup what is called a linked server. But, you have to download + install + setup the Intersystems ODBC drivers. Your desktop computer, or now where the SQL server is running does need those drivers, and they are not installed by default, nor part of windows. – Albert D. Kallal Apr 13 '22 at 23:09
  • @AlbertD.Kallal The drivers are installed there and I can test reaching the database fine. But how to do it from SQL Server now? – Mike Arrh Apr 14 '22 at 19:14
  • You can add a linked server - but, the version of access data engine installed will have to be x64 bits, since that is almost for sure running x64 bit SQL server. https://stackoverflow.com/questions/44375670/sql-server-linked-server-to-ms-access-dsn-architecture-mismatch-error?noredirect=1 – Albert D. Kallal Apr 14 '22 at 20:51
  • 1
    of course the above comment was for linking SQL server to Access - you want to add a new linked server in SQL - and use the cache drivers and that DSN settings. – Albert D. Kallal Apr 14 '22 at 20:52

1 Answers1

1

How is this done with SQL Server?

For SQL Server, write an SSIS package that uses the ODBC driver. Note that you may need to run the package in 32bit mode if the ODBC driver is 32bit. Then use that package to load a SQL Server table, or use an SSIS Data Streaming Destination to enable the SQL Server to dynamically run and read data from the SSIS package.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67