This Update Came from SQL Server

Disclaimer:

Just because you can does not mean you should and even if you should, use with care. For example, you will have no friends after a day or so of updating your facebook status every time a tlog backup runs on your farm. There are also many security concerns to consider. That said, I can think of a few uses for SQL Server Web 2.0 mashups and I am not all that creative.

How it works

Sign up at http://ping.fm and then sign up at whatever services you want to update from SQL. Here is a list of what they currently support. From SQL, we could send a message to twitter or even better, yammer via database mail. Here is the obligatory, “Don’t send anything confidential over email”

Possible Uses

Augmenting your monitoring – The keyword is word is “augmenting” except for the smallest installations. For the crown jewel of the company, you might get a call from the 24/7 staff and an email. Why not a tweet too especially if it can add SMS functionality that you may not currently have?

Turn X into an RSS feed – Where X could be, all job executions, any SEV error messages, any trace event or DDL operations, or [your idea here]. You can funnel the RSS into the intranet, your reader or [insert creative idea here].

An insert trigger on your press release table – Blast it to the world. Some companies are already doing similar things http://dell.com/twitter.

Personal updates from the SQL Editor – I don’t know about you but I have spent weeks on end in tsql. Some deadlines don’t allow for time to come up for air. It would pretty cool in a geeky way to tell the world that I just replaced 500 lines of cursor code with a 10 liner or whatever without leaving SSMS.

Example Code:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Ping.fm demo',

    @description = 'Mail account',

    @email_address = 'jason@statisticsio.com',

    @replyto_address = 'jason@statisticsio.com',

    @display_name = 'Jason Massie',

    @mailserver_name = 'mail.statisticsio.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MailAccount',

    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MailAccount',

    @account_name = 'Ping.fm demo',

    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MailAccount',

    @principal_name = 'public',

    @is_default = 1 ;


-- here is the magic blasphemy.
EXEC sp_send_dbmail @profile_name ='MailAccount', @recipients = 'YourUniqueEmail@ping.fm’, @subject = 'Blogged:', @body = 'This update came from SQL Server http://ping.fm/XiEkj'

I just updated twitter, linkedin, facebook, plurk, rejaw from SQL!

In closing

One more time, pay attention to security. There are a few commercial products on the horizon. http://ping.fm and most of these service also have API’s that may be a better alternative than database mail. Finally, database mail was meant for administrative alerts and I suggest you stick with that mindset..

email it! |   |   |   |  | 

Permalink     3 Comments  

Rate this Post: 12345
COMMENTS:

Whoa - I never thought about using the email mechanism.

I always wanted to build a simple end-of-day sales alerting mechanism from the data warehouse so that sales execs could see how the company did for the day, the instant the numbers were done crunching for the day. This would be the easy way to do it.

posted @ Wednesday, October 29, 2008 9:47 PM by Brent Ozar

---

Awesome that SQL Server could do this; but would you?

posted @ Thursday, October 30, 2008 3:31 AM by MarlonRibunal

---

Comments from the following blog entry: http://209.251.180.21/site/archive/2008/11/sql-twitter-n00bs-sqlpass/

posted @ Sunday, June 07, 2009 2:14 PM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]