How to add Linked Server in SSMS?

[alextt]

How can I access SalesForce database from SSMS? I managed to add a new System DNS ODBC connection and successfully connect to SalesForce database but not sure how to register it as a linked server and use the SSMS to query SAlesFroce database? Could anyone help please?

Thanks

[AlexP]

Hello,

To create a link from SSMS to Salesforce, you should configure our ODBC driver https://www.devart.com/odbc/salesforce/docs/index.html on the machine with SQL Server (using DSN is required). Connect to the local server in SSMS, select Server Objects->Linked Server in the object tree, select New Linked Server in the shortcut menu, select Micosoft OLE DB Provider for ODBC Driver in the provider field, specify the created DSN in the Data Source field, specify any names in the Linked Serv and Product Name fields, click OK. If you haven't entered login and password in the DSN settings, you should specify them on the security tab (Remote Login With Password).
If you get any errors on connection to the server, please send us screenshots of the error messages.

[dziemlak]

If you get any errors on connection to the server, please send us screenshots of the error messages.

I get the following error with the Salesforce Marketing Cloud connector when adding as a Linked Server.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "EXACTTARGET".
OLE DB provider "MSDASQL" for linked server "EXACTTARGET" returned message "[Devart][ODBC][ExactTarget]"Devart.ODBC.ExactTargetEngine.dll" can't be loaded". (Microsoft SQL Server, Error: 7303)

(Also in the DSN settings when any values are places in the User:Pass fields the 'Test Connection' button returns 'Success' even if the creds are bogus and the Server filed is blank.)

[AlexP]

Try to disable the Allow inprocess option in MSDASQL settings of SSMS

[Jake24]

I am also having the same problem. I tried to disable the Allow in process option in MSDASQL settings of SSMS and this still gives me the same error message.

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SALESFORCE".
OLE DB provider "MSDASQL" for linked server "SALESFORCE" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

[AlexP]

Which authentication are you using in SSMS: Windows or SQL Server? In addition, make sure you have created a system DSN.

[Jake24]

I am using Windows authentication.

I was able to add the Linked Server. I was putting the Data Source in the Product name by mistake. But I am still having problems with the Linked Server working. Now I am getting the following error:

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "SF". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)

[AlexP]

We can't reproduce the issue. A similar issue was discussed at https://blogs.msdn.microsoft.com/dataac ... ernamequo/ . Perhaps, the provided solutions may help you resolve this behavior.

[fmbma99]

I'm having trouble with the trial version of the odbc connector for Salesforce. I created the DSN and it has a successful test connection. When I try configuring the Linked Server in SSMS (SQL 2012), i get an error that it "cannot initialize the data source ... devart.salesforceEngine is not registered...error 7303"

[AlexP]

Did you try the actions described above?

[fmbma99]

I get a 7302 error when I disable InProcess, and the 7303 error I showed above with InProcess enabled. Do I need to enter a Provider String? I also tested one of the other ODBC drivers for SalesForce and was able to create a working linked server. Yours is less expensive, so I'm hoping I can get yours working for evaluation.

[AlexP]

Try to change the settings of the SQL Server service. On the LogOn tab, select Local System Account.

[wgrymuza]

Hi

I'm having similar problem.
I've configured ODBC driver to our salesforce instance.
I've added linked server, btu couldn't connect. I've disabled 'Allow inprocess' on MSDASQL providers settings and now I can connect to linked server.
Now, when I try to expand list of tables on the server, i get this error:
-----------------

Code: Select all

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
-->An exception occured while executing a Transact-SQL statement or batch
--> --> Cannot obtain the schema rowset "BDSCHEMA_TABLES" for OLE DB provider "SVMX" for linked server "SVMX". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error 7311)

-----------------

Can someone help please?

//EDIT:
When i enabled 'Allow inprocess' I no longer can connect to linked server...

[AlexP]

Which authentication are you using in SSMS: Windows or SQL Server?

[raranchado]

I had to restart the sql instance for the connection to work. But expanding the catalog of the linked server yields the same error
"Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "SF". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)"

But querying salesforce table directly works using 4 part-notation works.
ie. select * from [linked server]...[sales force table]

I'm getting some results from the direct query but I'm getting this error also on some columns
"OLE DB provider "MSDASQL" for linked server "[linked server]" returned message "Requested conversion is not supported.".