I installed the ODBC Driver for Salesforce and created a linked server in SSMS using ODBC following the documentation.
Testing to connect to the 64-bit Salesforce ODBC source in SSMS 2012/17.8.1 running SQL Server 2016 64-bit works. When I use the 32-bit ODBC source no connection can be established and the following error is shown:
Code: Select all
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SF".
OLE DB provider "MSDASQL" for linked server "SF" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)
Code: Select all
Error: "Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server
Code: Select all
The OLE DB provider MSDASQL for linked server "Salesforce" reported an error. Access denied. Cannot obtain the required interface"IID_IDBSchemaRowset" from OLE DB provider MSDASQL for linked server "Salesforce" (Microsoft SQL Server, Error: 7399)
Or when I login to SSMS as SA:
Code: Select all
Cannot create an instance of OLE DB provider "MSDASQL" for linked server "SF". (Microsoft SQL Server, Error: 7302)
Code: Select all
SELECT TOP 1 * from SFDC...[Contact]
- MSDASQL provider: disable Allow inprocess [done]
- Restart SQL Server [done]
- create procedure sp_tables_info_rowset_64 [done, does not fix the issue]
I believe the problem is, the devart driver requires the same bitness (as mentioned in the installation guide, see page 41). But I wonder why SMSS doesn't accept the 32-bit ODBC source which would match the architecture of the application. Can anyone confirm or show me way to use my Salesforce source as a linked server? I am open to alternative solutions. Thank you!