Salesforce
Interact with the Salesforce API using SQL Server
- Install the SQLHTTP database on your SQL Server
- Obtain an App ID, App Secret from developer.salesforce.com
- Obtain a User Security Token using the top navigation bar go to your name > Setup > Personal Setup > My Personal Information > Reset My Security Token
- You might also need to adjust your Salesforce app OAuth policies by setting Permitted Users to “All users may self-authorize” and relaxing IP restrictions
- Create the stored procedures documented below
- Execute the following SQL statement:
1 2 3 4 5 6 7 8 |
EXEC usp_SalesForce_Auth_Init @Profile = 'enter-profile-name-of-your-choosing', @ClientID = 'salesforce-app-id', @ClientSecret = 'salesforce-app-secret', @UserName = 'salesforce-username', @Password = 'salesforce-password', @UserSecurityToken = 'salesforce-user-security-token' |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE PROCEDURE usp_SalesForce_Auth_Init( @Profile varchar(100), @ClientID varchar(500), @ClientSecret varchar(500), @UserName varchar(500), @Password varchar(500), @UserSecurityToken varchar(500)) AS IF SQLHTTP.net.Split(SQLHTTP.net.SQLHTTPVersion(), '/', 2) < 4.5 BEGIN RAISERROR('Due to Salesforce higher security standard implementation, .Net Framework 4.5 is required as well as SQL Server 2012 or higher', 16, 1) RETURN END EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientID', @Value = @ClientID EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'UserName', @Value = @UserName EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Password', @Value = @Password EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'UserSecurityToken', @Value = @UserSecurityToken EXEC usp_Salesforce_Auth_Refresh @Profile GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
CREATE PROCEDURE usp_Salesforce_Auth_Refresh (@Profile varchar(100) ) AS SET NOCOUNT ON DECLARE @URL nvarchar(MAX) DECLARE @Body nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @PasswordWithUserSecurityToken varchar(500) SET @PasswordWithUserSecurityToken = SQLHTTP.net.AuthParam(@Profile, 'Password') + SQLHTTP.net.AuthParam(@Profile, 'UserSecurityToken') SET @URL = 'https://login.salesforce.com' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'services', 'oauth2', 'token' EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT, @Profile, 'grant_type', 'password', 'client_id', '#ClientID', 'client_secret', '#ClientSecret', 'username', '#UserName', 'password', @PasswordWithUserSecurityToken EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @Accept = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 BEGIN EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response END ELSE BEGIN DECLARE @BearerToken varchar(MAX) DECLARE @InstanceURL varchar(MAX) SELECT @BearerToken = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'access_token' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'BearerToken', @value = @BearerToken SELECT @InstanceURL = [value] FROM SQLHTTP.net.Json_To_NodeTable(@Response) WHERE [Name] = 'instance_url' EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'InstanceURL', @value = @InstanceURL END GO |
1 2 3 4 5 6 7 8 9 10 11 |
CREATE PROCEDURE usp_Salesforce_Auth_Header( @Profile varchar(100), @HttpSessionID uniqueidentifier) AS DECLARE @AuthorizationHeaderValue varchar(MAX) SET @AuthorizationHeaderValue = 'Bearer ' + SQLHTTP.net.AuthParam(@Profile, 'BearerToken') EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
CREATE PROCEDURE usp_SalesForce_Query( @Profile varchar(100), @APIVersion varchar(6), @SelectStatement varchar(MAX), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS IF @APIVersion NOT LIKE 'v[0-9]%' BEGIN RAISERROR('Invalid API Version. Example: v20.0', 16, 1) RETURN END IF ISNUMERIC(SUBSTRING(@APIVersion, 2, LEN(@APIVersion))) = 0 BEGIN RAISERROR('Invalid API Version. Example: v20.0', 16, 1) RETURN END DECLARE @URL nvarchar(MAX) DECLARE @QueryString varchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = SQLHTTP.net.AuthParam(@Profile, 'InstanceURL') EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'services', 'data', @APIVersion, 'query' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'q', @SelectStatement SET @URL = @URL + '/' + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Salesforce_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @Accept = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
- API Resources – Fetch
- API Versions – Fetch
- Event Monitoring – Fetch
- EventLogFile – Query
- Field Values from an External Object – Fetch
- Objects – Fetch
- Organization Limits – Fetch
- Parameterized Search for a String – Fetch
- Query Performance – Fetch
- Search Result Layout – Fetch
- SObject Fields and Metadata – Fetch
- SObject Metadata – Fetch
- SObject Record – Create
- SObject Record – Delete
- SObject Record – Update
- SObject Record Field Values – Fetch
- User Password – Set
- User Password Expiration- Fetch
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.