Connect to Salesforce Data as a Linked Server

Question

Post reply

Connect to Salesforce Data as a Linked Server

dataintegration

Mr or Mrs. 500

Points: 510
More actions
August 17, 2015 at 10:45 pm

#310988

Comments posted to this topic are about the item Connect to Salesforce Data as a Linked Server

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 1

So basically you need to buy the SF ODBC driver to do this, right?

http://www.cdata.com/drivers/salesforce/order/odbc/

Sam Lachterman SSC Veteran Points: 217 More actions · Answer 2

Yes, which is completely ignored by the author. The website for cdata doesn't even list the price for the product with SQL Server remoting (you have to request a quote), so it can't be cheap.

Subtle content marketing?

Robert Sterbal SSChampion Points: 11053 More actions · Answer 3

I think this is what Steve was referring to about the gray line between content and marketing.

Standard

Professional

Enterprise / Cloud / OEM

For use with desktop clients like Excel, Access, Word,etc.

$99

(with subscription)

Professional

Enterprise / Cloud / OEM

For use with Web apps, BI, Analytics, ETL, server tools.

$499

(with subscription)

Enterprise / Cloud / OEM

For BI, ETL, Analytics Tool Vendors, Cloud and Linked Server deployments.

Request Quote

412-977-3526 call/text

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

So basically the 'article' is an install guide/set up instructions for a very specific driver? We use DBAmp for the same purpose and the provide a nice guide but I won't be publishing it here 🙂

Steve.

dataintegration Mr or Mrs. 500 Points: 510 More actions · Answer 5

I may be mistaken, but I believe that DBAmp allow you to replicate salesforce data to a local instance of SQL server, correct? Replication/sync is a different problem entirely.

This solution is different - it offers the ability to create a SQL server linked server (https://msdn.microsoft.com/en-us/library/ms188279.aspx?f=255&MSPPError=-2147217396) with live access to salesforce data (no local replication). Basically it makes salesforce data look like SQL (or MySQL), without doing any kind of local copying etc.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Absolutely it lets you do that. *But* it also works effectively like an OLEDB driver, so you can easily create a linked server (or multiples, one for PROd, sandboxes etc) that is then usable within SQL. To add to that, the folks from DB Amp even give you some added functionality n stored procs and an external executable, basically allowing for use of the SF Bulk API's (which believe me, if you've ever tried to do any type opf update or insert with Salesforce, you want to be using the bulk API's).

So i guess I'm saying yeah, it can be used strictly for replication, but is that all it can do? No, not at all.

Steve.

Damir Khamitov SSC Journeyman Points: 94 More actions · Answer 7

As I understand Eric shows how to connect to a standalone Salesforce database server.

Is there a way to create a linked server to "Salesforce as a Service" database at https://login.salesforce.com ?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

August 18, 2015 at 8:24 am

#1820768

Thanks for the information.

Sam Lachterman SSC Veteran Points: 217 More actions · Answer 9

As a customer, DbAmp makes a great product and has great support.

dataintegration Mr or Mrs. 500 Points: 510 More actions · Answer 10

Hi Steve,

Yes, I know what you mean about the Bulk API - the CData driver uses it internally (and transparently) as well.

One thing that might not be obvious about the article - the linked server feature is common across all of the CData ODBC drivers. So, while this article focuses on Salesforce, the same linked-server capabilities can also be used for NetSuite, DynamicsCRM (on-prem & online), QuickBooks, SharePoint, SugarCRM, etc.

tnpich Ten Centuries Points: 1223 More actions · Answer 11

Does this require the API available from the SalesForce Enterprise edition or is independent of that?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 12

doesn't require anything local other than the driver (whether that be cdata or dbamp or whoever). these interface with the API, but that's in the cloud.

Steve.

AD's SSC Veteran Points: 218 More actions · Answer 13

Hi,

I thought of doing a POC to test the speed of CDATA ODBC driver from SSIS.

Here is some analysis (please note it is on non-prod environment.)

I used it in SSIS for fetching 8 column from salesforce (Account) table to SQL database.

It took around 8 minutes for inserting 828,000 records in SQL table from SF table.

All columns are string/datetime/bool.

Is it normal speed or am I missing something?

Thanks

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 14

We use DBAmp, and one of the reasons we chose it was the licensing model. You pay for each SalesForce production instance, not per SQL Server. So we can pay once and access our Salesforce data from as many SQL servers as we like, plus all of the sandboxes (of the same production org). All access by any developer or application goes through a SQL Server linked server, so there's no extra charge per developer or app deployment.

Access to the bulk API is another great feature. We can download a copy of every table nightly without worrying about hitting any transaction count limits.

But to provide connectivity to mobile apps you would have to publish a web service that went through your SQL Servers. If that was your goal you might prefer the CData royalty-free distribution model that would let mobile apps go straight to Salesforce.