It had all the hallmarks of a drunken conversation of the sort held at a conference… except it was early morning.
The perpetrators: Mladen Prajdic (blog | twitter), John Sansom (blog | twitter), Jonathan Allen (blog | twitter) and me. Accesories during the fact: James Boother (blog | twitter), Annette Allen (no blog | twitter). Accessory after the fact: Matt Whitfield (blog | twitter). And a hat-tip to Mr Tugs (blog | twitter) for filling in the blank in my memory of musical terms, seeing as my brother‘s not yet on twitter… Tchah.
Mladen probably started it all – he was talking about a SQL Full Monty, which was disturbing enough. And then I must take some of the blame because I wondered out loud about getting SQL Server to play tunes. Oh dear. Somebody really should have stopped me at that point, because it was bound to get ugly… I wondered about VBScript, only to find out that vbs doesn’t have a beep command – there’s any number of posts out there suggesting playing one of the default windows noises, or a console-esque “beep”, but I’m a musician, and I want notes, dammit!
James woke up at this point, and suggested the Powershell
[System.Console]::Beep command. Someone else suggested the PowerShell
"write-host `a" option, but both of those just do a default console “beep”. However, PowerShell’s “beep” does have a couple of parameters – frequency (Hz), and duration (milliseconds).
After that, it’s all down to the coding…
First up, some data. I’m not going for anything particularly clever, but just a scale. Or two. My initial data:
The first chunk of the script sets up the server to allow us to run
exec sp_configure 'show advanced options', 1 reconfigure with override exec sp_configure 'xp_cmdshell', 1 reconfigure with override
Next job: declare our temporary table:
declare @Scale table ( seq int identity, pitch numeric(25,13), dur int default 100, -- change this value to make the scale faster or slower major bit default 1, minor bit default 1, wholetone bit default 1, majarp bit default 1, minarp bit default 1, dim7th bit default 1 )
And now we’ll go and populate that table.
declare @StartPitch numeric(25,13) select @StartPitch = 440.0 insert into @scale (pitch) SELECT @StartPitch -- Starting pitch Concert 'A'. /* Now build the rest of the scale from that... */ while (select count(*) from @scale) <= 12 INSERT INTO @Scale (pitch) select @StartPitch * (power(2.000000, (select count(*) from @scale) * 1.0/12.0))
And now we’ll set up the filters for the different types of scales:
update @scale set dur = dur*2 where seq in (1,13) -- make the first & last notes longer update @scale set major = 0 where seq in (2,4,7,9,11) -- major scale, miss out a few update @scale set minor = 0 where seq in (2,5,7,10,11) -- minor scale, miss out a (slightly different) few update @scale set wholetone=0 where seq in (2,4,6,8,10,12) -- whole tone scale, miss out the odd numbers update @scale set majarp = 0 where seq not in (1,5,8,13) -- major arpeggio update @scale set minarp = 0 where seq not in (1,4,8,13) -- minor arpeggio update @scale set dim7th = 0 where seq not in (1,4,7,10,13) -- Diminished 7th. Thanks, MrTugs!
And now for the good bit. Danger: requires PowerShell.
declare @PS varchar(max) select @PS='' select @PS = @PS + '[console]::beep(' + cast(pitch as varchar(25))+', ' + convert(varchar(5), dur) + ');' from @scale where major = 1 -- use the other columns to filter for different types of scale order by seq declare @SQL varchar(max) select @SQL = 'master..xp_cmdshell ''powershell -Command "' + @PS + '"''' exec (@sql)
Now all I have to do is get the infrastructure boys to fit the server racks with microphones so I can hear the reactions of those people peering at my database servers – we already have video.
Oh, and if you want to play real tunes, you’ll have to figure that out yourself. Let me know how you get on!
PS: Forgot to mention that James Boother suggested putting a “
thread.sleep” command between the individual notes. Might help with articulation when I start putting in some more “serious” tunes… Will have to be careful with the timings, though!