Skip to content

Instantly share code, notes, and snippets.

🎯
bau

Alex Evdokimenko aevdokimenko

🎯
bau
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
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
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
as
begin
select @sql = replace(replace(@sql, '"', ''''''), '`', '''')
if @debug = 1 print @sql
exec sp_executesql @sql
end
@aevdokimenko
aevdokimenko / cases_stats.sql
Created Sep 27, 2018
Get stats on the cases in Salesforce using DBAmp + MS SQL
View cases_stats.sql
select
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
aevdokimenko / Notification email.sql
Last active Jun 26, 2018
Sending results of job execution to email
View Notification email.sql
declare
@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
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 -->
SQL:'
select @sql = '
select distinct
partition,
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
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
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 )
RETURNS NVARCHAR(10)
AS
BEGIN
@aevdokimenko
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.