aevdokimenko

-- 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

-- 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, ''

aevdokimenko

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

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

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

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, 

aevdokimenko

-- "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

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

-- =============================================
-- 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

-- 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')