Archive for July, 2012


I have used this technique over and over, and although many other options exist, this is simple and effective so worth mentioning.

There is 1 prerequisite: database mail must be configured on the sql server.

The first step is to build your query- let’s say:

select blah from some_table where exception_condition = 'yes'

Once we’re happy with our query we can build it into the sp_send_dbmail command:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test',
@recipients = 'blah@example.com',
@query = 'select blah from some_table where exception_codition = ''yes''',
@subject = 'EBMS Exception Report',
@attach_query_result_as_file = 1;

Notice- you will have to replace any single quotes in your query with 2 single quotes. You will likely notice a the output is a bit mis-aligned:

This is because sql server automatically extends the field to the length in the table definition. I normally truncate each field to no more than about 30 characters- and we’re in business. If you don’t want to keep running the sp_send_dbmail procedure, opening the e-mail, tweaking the query and re-running- you can run the sql query in management studio and select the "output as text" option as shown below:

After a bit of tweaking you should have it looking how you want:

Once you have tested this and are happy you can now created a sql job with a single t-sql step containing the above code. Attach a schedule and you’re good to go!

If you receive an error when trying to execute the sql job, you may need to double check the sql server agent account has adequate permission on the msdb database and the databases involved in your sql query.

L

*EDIT* This image has now been succeeded by a Raspbian / Wheezy based image: My raspbian SD image is now available: https://tickett.wordpress.com/2012/08/16/raspbian-wheezy-gambas2-domotiga/

After an earlier post https://tickett.wordpress.com/2012/06/05/domotiga-running-on-raspberry-pi/ I received a few requests for the sd card image. So here it is.

The following are all the login/passwords you may need:

login: pi/raspberry
msql: root/raspberry
vnc: raspberry

This is based on the official Raspberry Pi debian squeeze image, with the following additional items installed:

ssh enabled
mysql-server
tightvncserver
subversion
gambas2.24

It just fits on a 2 GB card:

pi@raspberrypi:~/domotiga$ df -H
Filesystem Size Used Avail Use% Mounted on
tmpfs 98M 0 98M 0% /lib/init/rw
udev 11M 148k 11M 2% /dev
tmpfs 98M 0 98M 0% /dev/shm
rootfs 1.7G 1.5G 60M 97% /
/dev/mmcblk0p1 79M 29M 50M 37% /boot
pi@raspberrypi:~/domotiga$

All packages have been upgraded to their latest stable version from the squeeze repository.

If you don’t know how to get into the guy, you can either login to the console then run:

startx

Or via ssh run:

vncserver :1 –geometry 1024x728 –depth 16

Then use any vnc client to connect to the desktop. Now execute gambas2 from the run menu:

I have already opened the DomotiGa project so just click on it:

Click "Run" from the toolbar, Debug menu or press F5:

Download links:

http://tickett.net/downloads/raspi_squeeze_gambas2_domotiga.img.7z

http://tickett.net/downloads/raspi_squeeze_gambas2_domotiga.img.7z.md5sum

Mirror:

http://speedy.sh/nAMsr/raspi-squeeze-gambas2-domotiga.img.7z
http://speedy.sh/v9ETB/raspi-squeeze-gambas2-domotiga.img.7z.md5sum

Let me know how you get on!

L

I powered up a new ESX host and enabled passthrough. This got me a bit further:

I thought it had hung here but patience paid off and the installer booted:

Damn- the keyboard/mouse doesn’t work (the pointer was moving around but I couldn’t click anything). Again- patience paid off and eventually it automatically moved on to the next step:

No target disks were listed when I chose to "Reinstall OS X". I went in to Disk Utility to investigate:

Damn. I tried using both IDE and SCSI disks in ESX but neither appear.

No doubt I’ll try some other ideas at a later date. I have a feeling I need to replace a .kext file on the install disc.

L

No success unfortunately- although I’m not overly fussed about getting it to work- just fancied a try.

ESX 5:

VirtulBox:

Again, getting stuck on "Still waiting for root device".

This was mounting the iso or trying client dvd drive. I can’t take ESX offline at the moment to reboot and enable USB passthrough.

If I get an opportunity to bring my other ESX host up sometime I’ll give it another go.

L

Mountain Lion Install

Everything went pretty smoothly- although it took a little longer than expected (probably due to the external dvd drive I was installing from).

I referred back to an old post for a list of applications I needed to install https://tickett.wordpress.com/2011/09/01/fresh-osx-lion-installation/ – fortunately with my recently upgraded FTTC 80/20 broadband I was able to re-download all the free stuff in no time at all (ensuring that I had the latest versions).

A few little things I’ve noticed so far. File copy progress indicator in finder:

By default only packages from "identified developers" can be installed:

This is easily fixed in System Preferences, Security and privacy- unlock to allow changes then change "Allow applications downloaded from:" to "Anywhere":

One of the features I’d been waiting for- Airplay Mirroring:

That’s my 50" TV (top) about 10ft away then my 15" MacBook Pro (bottom) on my lap. It seems to work pretty well even playing 1080p video- unfortunately there doesn’t appear to be any audio at the moment? Though a bit of googling highlighted a product called AirParrot which I might have to investigate.

The dictation feature seems to work pretty well too:

Although at the moment I think it relies on your caret being in the right place- I was quote hoping to be able to give it commands (Siri style)- a bit of a novelty maybe.

That’s about it for now. I might try installing on ESX later.

*EDIT* Now it would be nice to get some actual notifications in notification centre (gmail? outlook? facebook? imessage?)

Oh yeah- and software update is now part of the App Store.

L

Lots of wasted time today trying to get into X in Debian Wheezy- only to find out there’s a bug with the installer which ignores the fact that I have ticked the "desktop" option. Doh!

Adding the wheezy repository:

deb http://ftp.uk.debian.org/debian wheezy main contrib non-free

To:

nano /etc/apt/sources.list

Updating:

apt-get update

Then installing task-gnome-desktop:

apt-get install task-gnome-desktop

Fixes this!

As I am running in VirtualBox I then had to install virtualbox-guest-additions, but before doing so we need to know which headers to download:

uname –r

We can then install the virtualbox-guest-additions dependencies (the 3.2.0-2-amd64 is the output from the uname command above):

apt-get install make gcc linux-headers-3.2.0-2-amd64

Now you can mount the guest additions installation disk (devices, install guest additions) then:

mount –t iso9660 /dev/cdrom /media/cdrom

Now run /media/cdrom/VBoxLinuxAdditions.run and reboot :)

Getting gambas3 installed was a piece of cake- I chose to use the synaptic package manager and didn’t need to do anything special. I checked out DomotiGa and DomitiGaServer3 compiled/executed first time:

L

With some difficulty I have managed to get Gambas 3.1.1 running in Debian Wheezy Arm on Raspberry Pi:

I think DomotiGa has only just started to be ported to Gambas3- but it looks good:

Let’s get coding!

L

It’s not really wide enough for a busy club but when I do snap a few shots with it early in the night it reminds me what a great lens it is!

L

Thanks to ADH Heating Services I now have jeenodes counting pulses from my mains cold water supply:


Boiler hot water outlet:


And gas meter:


The pulses are being pulled into DomotiGa:

Which allows me to graph with RRDTool:

I also created my own module for DomotiGa to hook up to my custom 433Mhz RF receiving jeenode- I now have my window/door sensors (along with a bunch of rogue devices?) appearing:


I’m not best sure how to tackle the fact that the devices don’t issue an idle command when the alert has finished (i.e. when the door is closed or the fire is put out?). I’m considering trying out the built in DomotiGa feature to revert the values after 1minute or so (the smoke alarm should continue to send data whilst the alarm is being sounded so this shouldn’t be a problem).

I’m still troubleshooting a few problems- for example USB serial ports failing to open with error: "Cannot open serial port (#5)":

And some jeenodes going offline. Hopefully I will have more time to look into this later in the week.

L

%d bloggers like this: