Skip to content

Instantly share code, notes, and snippets.


Alex Evdokimenko aevdokimenko

Block or report user

Report or block aevdokimenko

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
aevdokimenko / id2alpha.sql
Last active Jan 15, 2019
T-SQL scalar function to generate pseudonymised character IDs from integer IDs
View id2alpha.sql
-- The function is used to convert employee ID into a (random)
-- character. To achieve randomness a seed should be generated by a caller.
-- Note: the number of IDs to be encoded cannot be greater than 62, otherwise
-- the function will return conflicting characters. To increase the number of handled
-- IDs extended the @chars variable
-- Usage:
-- select dbo.fn_id2alpha(1223, 42,
create function [dbo].[fn_id2alpha](@id int, @seed int, @n int)
aevdokimenko / example-181001.sql
Created Oct 1, 2018
Usage of sp_executequitedsql
View example-181001.sql
-- Paramertizing a call to sp_executesql
Declare @sql nvarchar(max)
declare @par1 int, @par2 nvarchar(255), @par3 datetime
select @par1 = 5, @par2 = 'K%', @par3 = GETDATE()
-- Doubling double single quotes
select @sql = '
select *
from openquery(LINKEDSERVER, ''
View sp_executequtedsql.sql
Create proc [dbo].[sp_executequotedsql]
@sql nvarchar(max),
@debug int = 0
select @sql = replace(replace(@sql, '"', ''''''), '`', '''')
if @debug = 1 print @sql
exec sp_executesql @sql
aevdokimenko / cases_stats.sql
Created Sep 27, 2018
Get stats on the cases in Salesforce using DBAmp + MS SQL
View cases_stats.sql
status, format(max(TTR), 'N') max,
format(min(TTR), 'N') min,
format(avg(TTR), 'N') avg
from (
select status, TTR = 1.0 * datediff(minute, createddate, isnull(closeddate, getdate()))/60
from openquery(salesforce,
'select status, createddate, closeddate
from case
where origin = ''GC'' and
aevdokimenko / Notification email.sql
Last active Jun 26, 2018
Sending results of job execution to email
View Notification email.sql
@job_id varchar(50), @recipients nvarchar(255),
@records int, @errors int, @r varchar(10),
@msg varchar(255), @subject nvarchar(255),
@query nvarchar(512), @procname nvarchar(255)
-- Find job id by its name
select @job_id = job_id
from msdb.dbo.sysjobs
-- replace with the name of your maintenance procedure
aevdokimenko / Get remote log.sql
Last active Jun 4, 2018
Gets remote log records using parameterized openquery
View Get remote log.sql
declare @sql nvarchar(1000), @lastdate varchar(20)
-- looking for the data for the last 72 hours
select @lastdate = datediff(S,'1970-01-01', dateadd(hour, -72, getdate()))
print '--> Executing GetLog -->
select @sql = '
select distinct
View Using dbo.FromUnixTime.sql
-- "Back to the future" date in 1955
select dbo.FromUnixTime(-446774400)
select dbo.FromUnixTime(1445385600)
-- Results:
-- 1955-11-05 00:00:00.000
-- 2015-10-21 00:00:00.000
aevdokimenko / Example for fn_GetSFDate.sql
Last active May 16, 2018
Example of using fn_GetSFDate
View Example for fn_GetSFDate.sql
declare @soql nvarchar(1024), @sql nvarchar(1024)
-- This is s SOQL statement with SQL datetime, it will return all user
-- records created today.
-- We use it for illustration purposes only, also demonstrating a technique
-- for providing dynamic arguments for OPENQUERY() parameter.
select @soql = 'select * from user where createddate > '
+ dbo.fn_GetSFDate(GETDATE())
-- We need to wrap it into another string, as openquery cannot be called
-- with parameters
aevdokimenko / fn_GetSFShortDate.sql
Created Apr 30, 2018
T-SQL function to return date in Salesforce short date format
View fn_GetSFShortDate.sql
-- =============================================
-- Author: Alex Evdokimenko
-- Create date: 2018-04-30
-- Requested by: DBAmp cookbook users
-- Description: Helper function to return date in Salesforce short date format
-- =============================================
Create FUNCTION [dbo].[fn_GetSFShortDate] (@d datetime )
aevdokimenko / TopChatter.sql
Last active Apr 30, 2018
Getting top Chatter groups for you SFDC org
View TopChatter.sql
-- Using locally copied tables by DBAmp
select name, membercount, id, LastFeedModifiedDate
from CollaborationGroup
where isarchived = 'false' and MemberCount > 100 order by membercount desc
-- or the same using openquery
select *
from openquery(salesforce, 'select name, membercount, id, LastFeedModifiedDate
from CollaborationGroup where isarchived = false and MemberCount > 100 order by membercount desc')
You can’t perform that action at this time.