function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
qmanqman 

Beta Code to connect to SQL Server

I've posted a new version of my code to connect SForce with SQL Server. The new code allows you use SQL Update, Delete and Insert statements as well as complex Select joins.

To try the beta, visit www.forceamp.com and register for the download.

Thanks,

Bill Emerson

bouscalbouscal

Can I join tables independant of what's available in the Reports within the application?

SELECT Account.Name, Account.Site, Account.Phone, Asset.Name, Event.ActivityDate
FROM Account
JOIN Asset ON (Asset.Accountid = Account.Accountid)
JOIN Event ON (Event.Accountid = Account.Accountid)
WHERE Asset.Name LIKE 'New%'
AND Event.Type IN ('Site Visit','Call')
AND Event.Completed = TRUE
AND Event.ActivityDate <= Asset.CreatedDate + 30
qmanqman

Yes.  You have to express the join using SQL Server syntax and you can use SQL Server Join hints to optimize performance.  You can also join with SFDC data with local tables.

If you don't have SQL Server, you can use the free SQLServer 2005 Express version from Microsoft.

Email me at bemerson@forceamp.com if you have more questions.

Thanks,

Bill
qmanqman

So, for example, this is a reworked version of your SQL that I just ran on my system:

SELECT T1.Name, T1.BillingCity, T1.Phone, T2.Name, T3.ActivityDate
    FROM SALESFORCE...Account as T1
   JOIN SALESFORCE...Asset as T2 ON (T2.AccountId = T1.Id)
   JOIN SALESFORCE...Event as T3 ON (T3.AccountId = T1.Id)
WHERE T2.Name LIKE 'New%'
       AND T3.Type IN ('Site Visit','Call')
-- AND T3.Completed = 'true' 
      AND DATEDIFF(day, T3.ActivityDate, T2.CreatedDate) <= 30

Bill