Playing music in SQL Server

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…

Data

First up, some data. I’m not going for anything particularly clever, but just a scale.  Or two.  My initial data:

The Script

The first chunk of the script sets up the server to allow us to run xp_cmdshell.

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)

Next steps

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!

About these ads
This entry was posted in SQLServerPedia Syndication and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s