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:


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'

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'



Stored Procedures:


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

CREATE PROCEDURE usp_SalesForce_Auth_Init( @Profile varchar(100),

@ClientID varchar(500),

@ClientSecret varchar(500),

@UserName varchar(500),

@Password varchar(500),

@UserSecurityToken varchar(500))

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


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

CREATE PROCEDURE usp_Salesforce_Auth_Refresh (@Profile varchar(100) )

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


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

CREATE PROCEDURE usp_Salesforce_Auth_Header( @Profile varchar(100),

@HttpSessionID uniqueidentifier)

DECLARE @AuthorizationHeaderValue varchar(MAX)

SET @AuthorizationHeaderValue = 'Bearer ' + SQLHTTP.net.AuthParam(@Profile, 'BearerToken')

EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue


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

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)

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



Available API Calls:

API Library > Salesforce

Install Now Free

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.