ODBC Driver for Salesforce: Access Linked Server in SSMS 17
Posted: Mon 27 Aug 2018 17:05
Hi. I face a similar problem as described in this forum post.
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:
However, While I can connt to the linked 64-bit source I cannot access the linked server object in SQL Server Management Studio 17.x (32 bit - AFAIK there is no 64-bit version of SSMS). When I try to expand the Server Object tree view I get the following error:
and after reboot
Or when I login to SSMS as SA:
Trying to query a linked table using `..` operator fails with the same error message: Cannot create an instance...
I have tried a few solutions but nothing seems to work:
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!
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!