How to add Linked Server in SSMS?

How to add Linked Server in SSMS?

Postby alextt » Wed 25 May 2016 14:32

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
alextt
 
Posts: 1
Joined: Wed 25 May 2016 14:25

Re: How to add Linked Server in SSMS?

Postby AlexP » Mon 30 May 2016 07:49

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.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby dziemlak » Fri 10 Jun 2016 18:29

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.)
dziemlak
 
Posts: 1
Joined: Fri 10 Jun 2016 18:19

Re: How to add Linked Server in SSMS?

Postby AlexP » Mon 13 Jun 2016 09:27

Try to disable the Allow inprocess option in MSDASQL settings of SSMS
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby Jake24 » Tue 14 Jun 2016 20:53

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)
Jake24
 
Posts: 2
Joined: Tue 14 Jun 2016 20:48

Re: How to add Linked Server in SSMS?

Postby AlexP » Wed 15 Jun 2016 08:38

Which authentication are you using in SSMS: Windows or SQL Server? In addition, make sure you have created a system DSN.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby Jake24 » Wed 22 Jun 2016 20:33

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)
Jake24
 
Posts: 2
Joined: Tue 14 Jun 2016 20:48

Re: How to add Linked Server in SSMS?

Postby AlexP » Mon 27 Jun 2016 11:01

We can't reproduce the issue. A similar issue was discussed at https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/08/09/error-while-creating-linked-server-cannot-obtain-the-schema-rowset-dbschema_tables_info-for-ole-db-provider-sqlncli10-for-linked-server-linkedservernamequo/ . Perhaps, the provided solutions may help you resolve this behavior.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby fmbma99 » Tue 05 Jul 2016 19:03

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"
fmbma99
 
Posts: 2
Joined: Tue 05 Jul 2016 18:55

Re: How to add Linked Server in SSMS?

Postby AlexP » Wed 06 Jul 2016 09:59

Did you try the actions described above?
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby fmbma99 » Thu 07 Jul 2016 17:57

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.
fmbma99
 
Posts: 2
Joined: Tue 05 Jul 2016 18:55

Re: How to add Linked Server in SSMS?

Postby AlexP » Mon 11 Jul 2016 10:29

Try to change the settings of the SQL Server service. On the LogOn tab, select Local System Account.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby wgrymuza » Wed 20 Jul 2016 10:25

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...
wgrymuza
 
Posts: 1
Joined: Wed 20 Jul 2016 10:18

Re: How to add Linked Server in SSMS?

Postby AlexP » Tue 16 Aug 2016 06:39

Which authentication are you using in SSMS: Windows or SQL Server?
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: How to add Linked Server in SSMS?

Postby raranchado » Wed 24 Aug 2016 22:38

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.".
raranchado
 
Posts: 1
Joined: Wed 24 Aug 2016 22:27

Next

Return to ODBC Drivers