Question on 64-Bit ODBC drivers and SSMS and linked databases

%3CLINGO-SUB%20id%3D%22lingo-sub-845988%22%20slang%3D%22en-US%22%3EQuestion%20on%2064-Bit%20ODBC%20drivers%20and%20SSMS%20and%20linked%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-845988%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%2064%20bit%20SQL%2FServer%20with%20a%2064-bit%20database%20(verified%20via%20a%20query%20that%20I%20found%20-%20thank%20you)%20and%20an%20Oracle%2064-bit%20driver%20(latest%20version%2019.3).%26nbsp%3B%20I%20was%20successfully%20able%20to%20create%20an%2064%20bit%20ODBC%20connection%20and%20got%20the%20test%20connect%20to%20work.%26nbsp%3B%20So%20far%20so%20good%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20version%20information%20in%20detail%3A%3C%2FP%3E%3CP%3EThis%20is%20SQL%2FServer%2013.0.5337.0%20(2016%3F)%3C%2FP%3E%3CP%3ESSMS%20Version%2017.4%20installed%20on%20Windows%20Server%202016%3C%2FP%3E%3CP%3EOracle%2064%20bit%20client%20version%2019.3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20we%20go%20to%20create%20the%20linked%20server%20inside%20SSMS%2C%20the%20Oracle%20driver%20does%20not%20show%20up%20even%20though%20it%20shows%20up%20in%20the%2064%20bit%20ODBC%20manager%20and%20it%20works%20meaning%20that%20all%20the%20TNS%20stuff%20is%20properly%20set%20up%20(we%20are%20seasoned%20veterans%20setting%20those%20things%20up).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20read%20on%20a%20support%20site%20for%20DevArt's%20Sugar%20CRM%20systems%20that%20in%20order%20for%2064%20bit%20to%20be%20used%2C%20the%20database%2C%20SSMS%2C%20and%20the%20driver%20must%20be%2064%20bit.%26nbsp%3B%20However%2C%20I%20also%20read%20that%20SSMS%20is%20still%2032%20bit%20and%20this%20was%20verified%20by%20running%20task%20manager%20and%20indeed%20it%20says%20it's%20a%2032%20bit%20app.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20certainly%20could%20switch%20to%20using%20the%2032%20bit%20version%20of%20the%20Oracle%20client%2C%20but%20I%20would%20rather%20not%20for%20future%20compatibility%20reasons%20down%20the%20road.%26nbsp%3B%20The%20connection%20is%20very%20light%20weight%20and%20is%20only%20used%20to%20transfer%20very%20small%20(meaning%20single%20row)%20data%20back%20and%20forth%20between%20the%20two%20systems%20so%20this%20certainly%20would%20be%20easy%20work%20for%20a%2032%20bit%20ODBC%20connnection%2C%20but%20I%20was%20trying%20to%20future%20proof%20and%20go%20all%2064%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20found%20bits%20and%20pieces%20of%20this%20answer%2C%20but%20all%20the%20questions%20are%20years%20old.%26nbsp%3B%20We%20can%20update%20to%20a%20later%20version%20of%20SSMS%20if%20that%20is%20needed%2C%20I%20see%2018.2%20just%20got%20released%20and%20we%20certainly%20could%20use%20that%20as%20well.%26nbsp%3B%20Had%20I%20realized%20SSMS%20was%20free%20now%2C%20I%20would%20have%20downloaded%20and%20installed%20it%20myself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20have%20full%20admin%20access%20to%20the%20Windows%20server%20system%20but%20not%20the%20database%20itself%20as%20that%20is%20managed%20by%20a%20DBA%20who%20didn't%20know%20the%20answer%20to%20this.%26nbsp%3B%20I%20have%20set%20up%20linked%20servers%20before%20where%20I%20did%20have%20full%20DB%20privs%20(this%20is%20an%20upgrade%20situation)%2C%20so%20I%20pretty%20much%20know%20what%20I%20want%20to%20do%2C%20just%20not%20sure%20how%20to%20do%20it%20and%2For%20if%20it%20can%20be%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-845988%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIntegration%20Services%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

We have 64 bit SQL/Server with a 64-bit database (verified via a query that I found - thank you) and an Oracle 64-bit driver (latest version 19.3).  I was successfully able to create an 64 bit ODBC connection and got the test connect to work.  So far so good ...

 

Here is the version information in detail:

This is SQL/Server 13.0.5337.0 (2016?)

SSMS Version 17.4 installed on Windows Server 2016

Oracle 64 bit client version 19.3

 

When we go to create the linked server inside SSMS, the Oracle driver does not show up even though it shows up in the 64 bit ODBC manager and it works meaning that all the TNS stuff is properly set up (we are seasoned veterans setting those things up).

 

I read on a support site for DevArt's Sugar CRM systems that in order for 64 bit to be used, the database, SSMS, and the driver must be 64 bit.  However, I also read that SSMS is still 32 bit and this was verified by running task manager and indeed it says it's a 32 bit app.

 

I certainly could switch to using the 32 bit version of the Oracle client, but I would rather not for future compatibility reasons down the road.  The connection is very light weight and is only used to transfer very small (meaning single row) data back and forth between the two systems so this certainly would be easy work for a 32 bit ODBC connnection, but I was trying to future proof and go all 64 bit.

 

I have found bits and pieces of this answer, but all the questions are years old.  We can update to a later version of SSMS if that is needed, I see 18.2 just got released and we certainly could use that as well.  Had I realized SSMS was free now, I would have downloaded and installed it myself.

 

I do have full admin access to the Windows server system but not the database itself as that is managed by a DBA who didn't know the answer to this.  I have set up linked servers before where I did have full DB privs (this is an upgrade situation), so I pretty much know what I want to do, just not sure how to do it and/or if it can be done.