Base-64 conversion in SQL Server

create function dbo.ConvertFromBase64String (@Base64Value varchar(max))
returns varbinary(max)
as
begin
    return cast('' as xml).value('xs:base64Binary(sql:variable("@Base64Value"))', 'varbinary(max)');
end;
go

create function dbo.ConvertToBase64String (@BinaryValue varbinary(max))
returns varchar(max)
as
begin
    return cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryValue"))','varchar(max)');
end;
go
declare @b varbinary(max) = cast('hello' as varbinary(max));
select @b; -- 0x68656C6C6F

declare @b64 varchar(max);
select @b64 = dbo.ConvertToBase64String(@b);
select @b64; -- aGVsbG8=

declare @result varbinary(max);
select @result = dbo.ConvertFromBase64String(@b64);
select @result; -- 0x68656C6C6F

declare @t varchar(max);
select @t = cast(@result as varchar(max));
select @t; -- hello

Javascript default parameters

"use strict";

// no IE support
// no Safari support

function doSomething(a = 1, b = 2) {
	console.log(`a=${a}`);
	console.log(`b=${b}`);
}

doSomething(); // 1, 2
doSomething(10); // 10, 2
doSomething(undefined, 20); // 1, 20
doSomething(10, 20); // 10, 20
doSomething(null, null); // null, null

// default value can be a function call, like an argument exception:
function go(val = missingValue("val")) {

}

function missingValue(paramName) {
	throw new Error(`Parameter ${paramName} is missing`);
}

try { go(34); console.log("ok"); } catch (ex) { console.log(ex); } // ok
try { go(); console.log("ok"); } catch (ex) { console.log(ex); } // error message

Send AWS SES email with javascript

Using the node-ses package:

"use strict"; 

var ses = require('node-ses'),
    client = ses.createClient({  
        key: 'mykey',  
        secret: 'mysecret' , 
        amazon: "https://email.us-west-2.amazonaws.com"
    }); 

// Give SES the details and let it construct the message for you.  
client.sendEmail({ 
    to: 'someone@example.com', 
    ,from: 'someone@example.com', 
    , cc: 'someone@example.com', 
    , bcc: ['canbearray@example.com, 'someone@example.com'] 
    , subject: 'Test Message ' + new Date() 
    , message: 'your <b>message</b> goes here' 
    , altText: 'plain text' 
    }, function (err, data, res) { 

        if (err) { 
            console.log("ERROR"); 
            console.log(err); 
        } else { 
            console.log("Done"); 
        } 
    }
);

GUID nonclustered index

Need a globally unique key for a table, but don’t want to risk performance issues arising from a random clustered index? And you don’t want to deal with similar keys by using newsequentialid?

The solution is to make a clustered index out of a regular identity integer, and use a GUID as a nonclustered primary key:

/*
Identity is the clustered index, but not the primary key.

Primary key has a non-clustered index.

The records are physically inserted in the database in order by the clustered key, but the primary key as a random GUID is still indexed.
*/

create table dbo.SomeTable
(
    CX int not null identity
    ,SomeTableKey uniqueidentifier not null
    ,...
);
go
alter table dbo.SomeTable
add constraint PK_SomeTable primary key nonclustered (SomeTableKey);
go
create unique clustered index CIX_SomeTable on dbo.SomeTable (CX);
go
alter table dbo.SomeTable
add constraint DF_SomeTable_Key default (newid()) for SomeTableKey;
go

View Job History in SQL Server

create procedure util.ViewJobHistory
(
    @ExecutionDate date = null
    ,@JobName sysname = null
)
as
begin
    declare @executionDateInt int;
    set @executionDateInt = year(@ExecutionDate) * 10000
        + month(@ExecutionDate) * 100
        + day(@ExecutionDate);

    select
        JobName
        ,JobSuccess
        ,RunDateFormatted + ' ' 
            + left(RunTimePadded, 2)
            + ':' + substring(RunTimePadded, 3, 2)
            + ':' + right(RunTimePadded, 2) [RunDateTime]
        ,DurationSeconds + (60 * DurationMinutes) + (60 * 60 * DurationHours) [TotalDurationSeconds]
    from
    (
        select
            sj.name [JobName]
            ,case sjh.run_status
                when 1 then 'Success'
                else 'FAIL'
            end [JobSuccess]
            ,left(cast(sjh.run_date as varchar(8)), 4)
                + '-'
                + substring(cast(sjh.run_date as varchar(8)), 5, 2)
                + '-'
                + right(cast(sjh.run_date as varchar(8)), 2) [RunDateFormatted]
            ,right('000000' + cast(sjh.run_time as varchar(6)), 6) [RunTimePadded]
            ,sjh.run_duration / 10000 [DurationHours]
            ,(sjh.run_duration - (sjh.run_duration / 10000 * 10000)) / 100 [DurationMinutes]
            ,(sjh.run_duration - (sjh.run_duration / 100 * 100)) [DurationSeconds]
        from msdb..sysjobs sj
        join msdb..sysjobhistory sjh on sj.job_id = sjh.job_id
        where isnull(@executionDateInt, sjh.run_date) = sjh.run_date
        and isnull(@JobName, sj.name) = sj.name
        and sjh.step_id = 0
    ) x
end;
go

Shrink image with ImageMagick

@ECHO OFF
SETLOCAL
FOR /R %%G IN (*.jpg) DO CALL :process "%%G"
GOTO :end

:process
    SET _inname=%1
    SET _outname=%_inname:~0,-5%_1024.jpg"

    identify -format %%w %_inname% >width.txt
    identify -format %%h %_inname% >height.txt

    set /p width=<width.txt
    set /p height=<height.txt
    DEL width.txt
    DEL height.txt
    ECHO Processing %_inname% ...

    if %width% gtr %height% call :landscape %_inname% %_outname%
    if %height% geq %width% call :portrait %_inname% %_outname%
    rem convert %_inname% %_inname%
    EXIT /B

:landscape
    convert %_inname% -resize 1024x -quality 90 %_outname%
    EXIT /B

:portrait
    convert %_inname% -resize x1024 -quality 90 %_outname%
    EXIT /B

:end

SQL Server Flatten and Unflatten

Assuming you have a table type named SingleColumnText which contains (obviously) a single column of text, named [TextValue]:

create function util.Flatten
(
    @input util.SingleColumnText readonly
    ,@delimiter nvarchar(max)
)
returns nvarchar(max)
as
begin
    declare @result nvarchar(max);
    select @result = coalesce(@result + @delimiter, '') + TextValue
    from @input
    order by PK;

    return @result;
end;
create function util.Unflatten
(
    @input nvarchar(max)
    ,@delimiter nchar(1)
)
returns table
as
    return
    (
        select
            row_number() over (order by n) - 1 [Idx]
            ,substring(@input, n, charindex(@delimiter, @input + @delimiter, n) - n) [TextValue]
        from util.Numbers
        where n <= len(@input) 
        and substring(@delimiter + @input, n, 1) = @delimiter
    );
go

Add timestamp to photo using ImageMagick

In a Windows batch file:

    rem dptnt.com/2009/04/how-to-add-date-time-stamp-to-jpeg-photos-using-free-software/

    @ECHO OFF
    SETLOCAL
    FOR /R %%G IN (*.jpg) DO CALL :process "%%G"
    GOTO :end

    :process
     SET _inname=%1
     identify -format %%w %_inname% >dttmpfile
     set /p width=<dttmpfile
     Set /a pointsize=%width%/50
    rem  echo ZZ >> dttempfile
     DEL dttmpfile
     ECHO Processing %_inname% ...
     convert %_inname% -gravity SouthEast -font Arial -pointsize %pointsize% -fill orange -annotate +%pointsize%+%pointsize% "%%[exif:DateTimeOriginal]" %_inname%
     EXIT /B
    :end