+ Start a Discussion
qmanqman 

New Version of DBAmp (Open Source SQL Access)

A new version (V2.1) of DBAmp as been posted at the DBAmp open source project. Details are available at www.forceamp.com . The new version provides UPDATE /DELETE support as well as some performance enhancements. Questions regarding DBAmp can be posted in this forum (Open Source).

Thanks,
Bill Emerson
forceAmp.com

Message Edited by qman on 10-28-2005 05:14 PM

kimkim
Just installed and up and running; impressed.
I had some problems with the previous version.

I like to test some more,
but I want to test and build some stuff on our sandbox/ test instance.

Can you give me an idea how I point to that or whether that is possible with dbamp?

Kim
qmanqman
Working on it ! Stand by... maybe later today or tomorrow.

I'll post a notice here when I put the new version up.

Bill
kimkim
Appriciate!

It works fine for the developer instance to na1 but not to tapp0 for the sandbox.

Cheers,
Kim
SpacewranglerSpacewrangler
Do you all know if DBAmp can be used with Microsoft Access as well as SQL?
benfishinbenfishin
Bill,
Not sure if this is a bug or not

This SQL works:
SELECT * FROM SALESFORCE...Task WHERE Id = '00T3000000Fwbzm'
(1 row(s) affected)

This the same SQL using OPENQUERY dosen't work:
SELECT * FROM OPENQUERY(SALESFORCE, 'SELECT * FROM Task WHERE Id = "00T3000000Fwbzm" ')

Gets the following error message:
Server: Msg 7350, Level 16, State 2, Line 1
Could not get the column information from the OLE DB provider 'DBAmp.DBAmp.1'.
[OLE/DB provider returned message: Error 13005 : Error translating SQL statement: line 1:26: unexpected token: Id]
OLE DB error trace [OLE/DB Provider 'DBAmp.DBAmp.1' IColumnsInfo::GetColumnsInfo returned 0x80040e14].

Does not seem to be able to find Task:Id in describeSObjects?
Not referencing base sObject?

I have tried 'id', 'Id' and 'ID'.
Ultimatley I am trying to do an update using OPENQUERY as stated in the most recient documentation (2.1)

By the way what additional functionality is provided in the Pro version?

-Benj

Message Edited by benfishin on 11-02-2005 09:11 AM

Message Edited by benfishin on 11-02-2005 09:15 AM

qmanqman

Hi Benj,

Try this:

Code:

update openquery(SALESFORCE,'select * from SALESFORCE...Task where Id=''jfjfdj'' ') set IsClosed='false'

  Note: there are no double quotes in this statement. There are two single quotes around the Id value; i.e. to embed a single quote in a quoted string, use two single quotes.

 I would also consider using a stored proc for this. You could base it on the examples in the DBAmp program directory. Take a look at the Create UpdateAnnualRevenue.sql for an example of how to do this. Then you could do something like: Code:

exec UpdateIsClosed 'jfjfdjdf','false' 


Bill

Bill

Message Edited by qman on 11-02-2005 09:12 AM

kimkim
Hi,

I saw you posted V2.2.
Thx for that.

But how do I point to the sandbox (test.salesforce.com or tapp0.salesforce.com)

I couldnt find anything in the documentation but I might overlooked...

cheers
kim
qmanqman

See page 5 of the doc, Pointing DBAmp to your Salesforce Test Instance.

Be sure to restart SQL Server after making the change in order to flush the dll cache.

Thanks,

Bill

kimkim
Hi Bill,

Works perfectly now.

I first installed from the source, which for some reason installed the 2.1 version.

the .exe seems fine.

Thx,
Kim
benfishinbenfishin
Bill,

Any way to speed up data retrieval?

-Benj
linebackerlinebacker
DBAmp is great. I never asked for, but can i write my local changed datasets back to Sforce?
How?


regards linebacker :)
benfishinbenfishin
lb,

Try something like this example:

INSERT INTO SALESFORCE...Task (OwnerId) VALUES ('00530010000fuzzAAY')

-Benj
qmanqman
I've been looking at this and there is a big problem with performance with some COM object marshalling (basically having to do with the Office Toolkit being apartment threaded and the DBAmp provider being free threaded.

I've got a solution working in the lab that makes a dramatic performance gain (30K rows in 2 mins vs. 16 mins.) but I've got one last bug to iron out before I can release it.

Stay tuned.

Bill
qmanqman
I think what you're looking for is a way to import from SQL Server into Salesforce (kind of the opposite of SF_Replicate). You can do it a couple of ways:

1. Use SQL Data Transform Services to export your SQL table into a csv, then use dataloader to upload this csv.

2. Write your own custom stored proc that compares the ID and SystemModstamp between the local table and the SALESFORCE...Object to find differences. Then use an update SQL statement to update the values you want up on the SALESFORCE side.

I am considering writing a SF_Import stored proc that does this, but I have kinda of a full plate right now getting DBAmp ready for the Winter '06 release.

Any open source'ers that want to take a stab at it (with a little guidance from me) ?

Bill
benfishinbenfishin
I take a stab at it.

-Benj
qmanqman
Cool!

Send me an email at bemerson@forceamp.com and let's talk design and issues.

Bill
qmanqman
Thanks, I'll contact you via email.
vermontvermont

Has anyone figured out how to connect to sandbox?

In the help it says:

By default, DBAmp points to your production Salesforce.com instance.  If you need to change DBAmp to point to your Sandbox instance or need to use a different endpoint for DBAmp, alter the Location parameter of your linked server.

The Location parameter is normally blank. If your Sandbox Instance is at https://aspen.salesforce.com then you would enter https://aspen.salesforce.com for the Location Parameter on the linked server properties page.

Which works well for the Linked Server, but DBAmp Configuration seems only to be able to connect to production. And so my stored procs don't work.

Any help is appreciated!

Thanks,

VT

forceAMPforceAMP

HI VT,

The DBAmp configuration program should not affect your stored procedures. What version of DBAmp are you running ?  You can also engage forceamp support directly by sending an email to support@forceamp.com

 

Thanks,