Page 1 of 2
How to add Linked Server in SSMS?
Posted: Wed 25 May 2016 14:32
by 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
Re: How to add Linked Server in SSMS?
Posted: Mon 30 May 2016 07:49
by 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.
Re: How to add Linked Server in SSMS?
Posted: Fri 10 Jun 2016 18:29
by dziemlak
AlexP wrote: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.)
Re: How to add Linked Server in SSMS?
Posted: Mon 13 Jun 2016 09:27
by AlexP
Try to disable the Allow inprocess option in MSDASQL settings of SSMS
Re: How to add Linked Server in SSMS?
Posted: Tue 14 Jun 2016 20:53
by 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)
Re: How to add Linked Server in SSMS?
Posted: Wed 15 Jun 2016 08:38
by AlexP
Which authentication are you using in SSMS: Windows or SQL Server? In addition, make sure you have created a system DSN.
Re: How to add Linked Server in SSMS?
Posted: Wed 22 Jun 2016 20:33
by 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)
Re: How to add Linked Server in SSMS?
Posted: Mon 27 Jun 2016 11:01
by 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.
Re: How to add Linked Server in SSMS?
Posted: Tue 05 Jul 2016 19:03
by 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"
Re: How to add Linked Server in SSMS?
Posted: Wed 06 Jul 2016 09:59
by AlexP
Did you try the actions described above?
Re: How to add Linked Server in SSMS?
Posted: Thu 07 Jul 2016 17:57
by 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.
Re: How to add Linked Server in SSMS?
Posted: Mon 11 Jul 2016 10:29
by AlexP
Try to change the settings of the SQL Server service. On the LogOn tab, select Local System Account.
Re: How to add Linked Server in SSMS?
Posted: Wed 20 Jul 2016 10:25
by 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...
Re: How to add Linked Server in SSMS?
Posted: Tue 16 Aug 2016 06:39
by AlexP
Which authentication are you using in SSMS: Windows or SQL Server?
Re: How to add Linked Server in SSMS?
Posted: Wed 24 Aug 2016 22:38
by 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.".