As Seen on Demi Moore’s Twitter Feed
What a week. First, TheBloggess is seen on the intertubez wearing one of my custom Twitter t-shirts (okay, okay, I sent it to her for free) and now Demi Moore posts a link to my web site:
Demi Moore Was Here
That TinyURL points to my Twitter RT FAQ.
I’m never washing my web site again.
More of My Twitter Articles
- Top 10 Reasons I’m Not Following You On Twitter – wondering why nobody’s following you back?
- Top 10 Reasons I *AM* Following You – how I pick who gets past my virtual bouncer.
- Twitter T-Shirts and Coffee Cups – you can get Twitter shirts with the fail whale, or get your own Twitter profile on a shirt.
- Twitter RT, OH, HT: The Slang Explained – I talk about what these cryptic terms mean and how to use them correctly.
- Twitter Client Demo Video – I show you TweetDeck, Seesmic Desktop and Orsiso, three different Twitter clients.
- How to Make a Bigger Twitter Profile – when people glance at your Twitter profile, they make a snap decision about whether to follow you. Give them the information they need to make a good decision.
- Browse All Posts Tagged “Twitter”
- How To Start a Blog – decide why you’re blogging, because that’ll affect the type of blog you start and how you build it.
- Browse All Posts Tagged “Blogging”
Want More Blogging & Twitter Tips? Follow me on Twitter. I tweet whenever I post a new blog entry, so you’ll always know when I’ve got new stuff. See you online!
SQL Server and Cloud Links for the Week
SQL Server Links
Disk Performance Recap – Kendal Van Dyke wrote a long blog series on disk performance, and he sums up his findings in this post.
Slowly Changing Dimensions – a tutorial on how to design for this common data warehouse requirement.
Expert Advice on Virtualizing SQL Server – SQL Server Magazine sent out email questionnaires about this, and my comments are in there along with Kevin Kline’s and others.
DevLink 2009 Call for Speakers – Joe Webb puts out the call for folks in the Tennessee area.
How to Add Custom Links to Your Blog’s RSS Feed – if you’re syndicating your blog at another site like SQLServerPedia, check out Rob’s tips on how to add an automatic footnote to every blog post that explains where the original source is.
Getting SQL Server Help on Twitter – Jeremiah Peschka talks about how he’s gotten help on Twitter when he’s been in a bind.
Cloud and Virtualization Links
SQL Server in the Cloud Coming with Azure – They said it first, not me.
5 years from now, we’ll all be in the clouds – Paul Nielsen believes that the cloud will be the default choice for databases five years from now. Whether you agree or disagree, it’s just wild that we’re even having that discussion.
Citrix Starts Giving Away XenServer – let’s see how long these companies (Citrix and VMware) can go giving away product while simultaneously competing with Microsoft. Reminds me of the browser wars. Here’s a hint: Microsoft has a big war chest of dough, and Citrix…doesn’t.
Google Responds to GFail Outage w/New Dashboard – you can see at a glance what’s up and what’s down, and what the recent history’s been like.
The Junk Drawer
Happy Pancake Day!
Happy Pancake Day! Jay Lee, Houston photographer and funny guy, took this photo to celebrate Shrove Tuesday. I can just imagine what breakfast is like at their place.
The Bloggess Wears My Shirt! – my t-shirt business can now fold. It doesn’t get any better than this.
SQLBatman’s Happiness Coach – no matter what business you’re in, there are some basic keys to success. Tom LaRock shares what he learned from his friend the happiness coach. (Just thinking of that job title makes me wanna hurl.)
Scrum Means Panic – ever been on one of those projects that kept getting later and later, and all of a sudden the project manager freaked out and said, “We gotta meet every morning now! All hands on deck!”
Windows 7 Changes Since the RC – I toyed with 7 briefly when my Macbook Pro went in for service, and I gotta tell ya, I really liked what I saw. It was very attractive, very straightforward, and had a lot of neat little things to offer. If you can get hardware with stable Windows 7 drivers (I didn’t), you won’t get mocked by Apple guys for running it on the day it’s released. (Can’t say the same for the guys who went with Vista on day one.) This article about the continuous improvements in 7 is fun to read.
ASUS Eee PC 901 12GB SSD now $280 after rebate – holy cow, this stuff’s getting cheap. Available in two versions, both with Atom processors, 8.9″ 1024×600 screens, and 6-cell batteries:
- Atom processor, 1gb ram, 12GB solid state drive, Windows XP Home – $280
- Atom processor, 1gb ram, 20GB solid state drive, Linux – $270
Those are going fast, though – only 4 of the Linux ones in stock as I’m publishing this.
We Agree: It’s Not My Problem
Manager: “It’s come to my attention that the application performance isn’t as good as it should be. Users are complaining that it takes too long. Is it fair to say that every query sent to the database should finish in under ten seconds?”
Developers: “Yeah.”
Database administrators: “Yeah.”
Manager: “Okay, we’re in agreement. Going forward, all queries sent to the database need to finish in under ten seconds. Meeting adjourned. Great job guys.”
…
Is everyone really in agreement?
The developers think it’s the DBAs’ fault because the server isn’t fast enough. The DBAs think it’s the developer’s fault because the queries are so poorly written. The managers think everybody’s just flat out incompetent.
Groups of people can agree on something on the surface, but ask a few questions, and all hell breaks loose.
This seems to happen a lot in database administration due to the kinds of knowledge DBAs have. People bring us lots of ideas, but they’re not really clear on what needs to be done in order to accomplish that goal – or who’s the right person to actually do the legwork. As a result, sometimes we have to break some hearts.
I struggle with this, because I’m the kind of guy who will turn right around and give a flip answer like, “John, I agree, that’s a great idea, and I can’t wait to see how it turns out for you.”
Better answers are probably along the lines of:
- “I agree – that’s a great idea. Is there anything I can do to help you get it done?” People freeze at that one because they don’t feel comfortable saying, “Yeah, you could do it for me.” It at least gets them to stop and think.
- “That sounds great! Could I help you by testing your work or finding beta testers?” Again, sets the expectation that you’ll get involved when their work is over.
- “I’ve been thinking about that same thing for quite a while, and I haven’t been able to find the time to do it. Doggone day job of mine! Who could we track down to do it?”
And still, as I write these, I know they’re all too sarcastic. <sigh> This is one professional skill I still gotta work on.
SQL Server 2010 Features Leaked! (Parody)
(If you found this page via a search, I’ve got good news – SQL Server 2010 will be called SQL Server 2008 R2, and the release date for all editions (including Express Edition) is May 2010. You can learn more about SQL 2008 R2′s new features here. I’m getting a lot of hits on this post because it was written as a joke before Microsoft announced the real version & release date. The rest of this post was a joke, like April Fool’s. I write a lot of funny stuff here.)
I’ve got great news, folks: I just got my hands on the list of new features in SQL Server 2010. What? You didn’t even know it was coming? Pfft, outsider. Listen, I’m under NDA, though, and I can’t really talk about this, so don’t say you heard these from me.
Best Practices Policies Enforced by Default
SQL Server 2008 shipped with Policy-Based Management, a way for DBAs to control large numbers of instances by setting up configuration policies. For example, we can create policies that every table must have a primary key, or that all stored procedures must begin with usp. SQL Server 2010 takes this to the next logical level by enabling these policies by default.
Think they won’t do it? Think back a few years: SQL Server 2005′s default configuration did not allow connectivity from end users. You had to manually turn on TCP/IP connectivity. SQL Server 2010 is just continuing this gradual enforcement of best practices.
I’m not 100% sure about this, but from the way I’m reading the draft of Books Online, it looks like disabling policies will be deprecated in the next version of SQL Server as well.
Database Mail Expanded with Social Networking
Web 2.0, meet SQL 2.010. Microsoft’s seen the spread of tools like Twitter and instant messaging, and they’re bundling right inside the box. Now when you set up operators and alerts, you can include the user’s Twitter name or MSN instant messaging screen name.
Clearly, this is just to be used as a fallback, since these types of communication are less reliable than conventional SMTP mail. It’s also not clear whether we’ll need to get a Twitter account for each of our SQL Servers, and whether we’ll have to set up “friend/follower” relationships with each DBA. I’d hate to miss one of my critical errors because I’d blocked “SQLBOX9357″ thinking it was just another spammer.
On the plus side, I’m looking forward to being able to set up a column in TweetDeck for “SQL Server Alerts.” Although I’m not sure I’ll get too many of them, because SQL Server 2010 claims to be…
Completely Self-Tuning
Starting with SQL Server 2005, we got Dynamic Management Views (DMVs). These windows into SQL Server’s internal engine workings have made tuning databases so easy a caveman could do it. SQL Server 2010 will therefore include Ugg the Prehistoric Performance Tuner, a friendly fella in a fur outfit who constantly reads the DMV queries for missing indexes and unused indexes, then updates your schema appropriately.
This isn’t available in every edition, unfortunately, because SQL Server 2010 will be…
Available In Seven Editions
Historically we’ve had Express, Developer, Standard and Enterprise, but the clear success in the marketplace of Windows Vista’s licensing scheme is carrying into the SQL Server world. We’ll be able to choose from:
- Starter Express Edition – only allows 3 databases.
- Developer Edition – optimized for cursors and triggers, and does not throw any error messages. Microsoft wants to make sure developers can quickly say, “It worked on my machine.”
- Standard Edition – I know what you’re thinking: it’s named after The Standard, a hip, humorous and luxurious boutique hotel chain. You’re wrong.
- Hybrid Edition – has the SQL Server GUI, but a MySQL engine. Everybody’s buying ‘em these days.
- Enterprise Edition – everything you really wanted in Standard.
- Ultimate Edition – acts as a Media Center repository, so you can save your TV shows and por – I mean, “home movies” on it. Scheduled for early release on BitTorrent.
- Cloud Edition – like Enterprise Edition, but you pay by the byte stored. Pricing and service levels haven’t been announced yet, but you can go ahead and start developing on it now and I’m sure you’ll be fine.
There’s more, too. I’m reading BOL as we speak, and it looks like there’s a new Tweet datatype. It looks like a varchar(140), but it has some kind of built-in security if the first two characters are “d “. I’ll keep reading up on that and let you guys know what I find out.
Update: CTP to Be Released on 4/1/2009
I got two emails from people asking for more information, so I figured I’d better clarify that this is an April Fool’s style parody. Sometimes my humor is a little on the subtle side. I appreciate that folks hang on my every word, but sometimes – err, a lot of the time – I’m smiling when I say ‘em. Hope you had a laugh from it anyway!
Update #2: No Joke, It’ll Be Called SQL Server 2008 R2
And here’s my posts about it:
Kevin Kline webcast tomorrow on SQL Server performance
The Houston PASS chapter hosts their meetings in LiveMeeting, which means you can watch online for free. On Wednesday the 25th at noon EST, Kevin Kline will be presenting. Here’s the abstract:
Webcast Agenda: Detect, Diagnose and Resolve Performance Problems
Learning how to detect, diagnose and resolve performance problems in SQL Server can be very difficult. It often takes years of on-the-job experience to learn how to use the tools and techniques that help you detect when a problem is occurring, diagnose the root-cause of the problem, and then resolve the problem. This hands-on session will take you from the very start of a problem situation on SQL Server, showing you how to use the native tools that help you keep SQL Server at top performance. This session will detail and demonstrate:
- Performance Monitor (PerfMon)
- SQL Profiler/Server-side Trace
- Correlating PerfMon and Profiler results
- Graphic Explain Plan and Transact-SQL Showplan
- SQL Server 2005 and 2008 DMVs
- When to use Trace Flags
These tools and techniques can make your long days shorter and make difficult troubleshooting activities much faster and easier. Every DBA needs to know how to keep their SQL Server in tip-top condition, and you’ll need these skills to do it.
About Kevin Kline
Kevin Kline is the Technical Strategy Manager for SQL Server Solutions at Quest Software. A Microsoft SQL Server MVP, Kevin is a founding board member of PASS and the author of several books including SQL in a Nutshell (O’Reilly and Associates). Kevin is a top-rated speaker at industry trade shows and has been active in the IT industry since 1986.
To Watch the Free Webcast
At around 11:50 AM EST (8:50 AM PST), click this link. Go early, because the LiveMeeting stuff takes forever to start if it’s not up to date. Then call 1-866-231-6479 and put in participant code 304968 to hear the audio portion of the presentation.
To get emailed whenever the Houston PASS Chapter schedules another webcast, sign up for their email alerts on the Houston PASS website.
Syndication Hits the Newspaper Business
I’ve blogged about how syndication is the future for news sites like SQLServerPedia and for magazines like GestaltIT, and now it’s time to talk newspapers.
The Seattle Post-Intelligencer is doing syndication and aggregation right on their home page. Blog site The Stranger reports that the PI has started running headlines that point straight out to other sites like blogs. They followed up with another story on the SeattlePI that confirms it.
We’ve seen newspapers struggling financially lately: some Michigan papers have stopped printing 7 days a week, and only produce printed versions certain days of the week. They’re having a tough time competing with the internet news sites. Now, the SeattlePI has realized that they’re having a tough time competing with blogs too, and they’ve decided to turn the site into a central gateway instead.
There’s another industry with this same business model, although we don’t usually think of it as an industry.
Cupid and Psyche - St Petersburg Russia
Bloggers and Syndication Is Like Artists and Museums
When you go to a museum, you’re seeing a limited amount of artworks. You might see one or two pieces by an artist, but not all of their work. If you find one of their pieces amazing, you might write down that artist’s name and learn more about their work. You might go to the museum bookstore and buy a book about that particular artist.
The museum’s curators spend their days viewing art and selecting pieces that will be placed in the museum. They try to put pieces together that will have a flow, that are beautifully made, and that tell a good story. This is the approach the SeattlePI is taking: their editors are curating posts from lots of blogs that when taken together, form a newspaper.
Each artist isn’t confined to using a single museum: they can produce as much work as they want, and get that art in as many museums and galleries as possible. After all, artists need to eat! Some pieces are done on commission for clients, and some are done just for the passion of it. Sounds like bloggers, doesn’t it?
Museums by themselves don’t make money – they rely on sponsors. When you enter a museum, you see some ads for the exhibit’s sponsor and the museum’s overall sponsors. Fortunately, museums keep it pretty subtle, but there’s no such guarantee in online syndication sites. I expect that some of them will look pretty ugly.
I believe that we’re handling the syndication process at SQLServerPedia the same way I’d approach museum curation. When someone wants to learn about art, they go to a good museum. When they want to learn about Microsoft SQL Server, I want them to come to SQLServerPedia, see our artists – I mean, uh, bloggers – and say, “Wow! That person’s a great writer! I want to go to their blog and learn more about them, get more of their work, and talk to them about what they’re doing.” That’s my vision of success with syndication.
Dump Your Twitter Friends with Tweet-SQL
I’ve been using Twitter for a while, and I decided to prune the number of people I’m following.
But before I start going through my list of friends by hand, why not strip out everybody who hasn’t been active in a certain amount of days?
To help me do that, I’ll be using Tweet-SQL. It’s a set of stored procedures you can use to call the Twitter API from Microsoft SQL Server using plain T-SQL code. In today’s examples, I’ve got T-SQL code that will:
- Call the Twitter API to get the list of people I’m following (my “friends” in Twitter terms)
- For each friend, fetch their last updates
- Generate a web page to help me unfollow people
Buckle your seatbelts!
Our Twitter Cache Database Schema
If you read my award-winning (not really) blog post on how to use Tweet-SQL to import a Twitter feed, this schema is going to look pretty familiar. I’ve started with the same two tables for Users and Statuses, but I’m adding a new table called UserFollows to hold the list of users that a user is following.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Statuses](
[id] [int] NOT NULL,
[created_at] [datetime] NOT NULL,
[nvarchar](200) NOT NULL,
[nvarchar](160) NOT NULL,
[truncated] [bit] NOT NULL DEFAULT ((0)),
[in_reply_to_status_id] [int] NULL,
[in_reply_to_user_id] [int] NULL,
[favorited] [bit] NOT NULL DEFAULT ((0)),
[user_id] [int] NOT NULL,
[cached_date] [datetime] NOT NULL CONSTRAINT [DF_Statuses_cached_date]
DEFAULT (getutcdate()),
CONSTRAINT [PK_Statuses] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users](
[id] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[password] [nvarchar](50) NULL,
[screen_name] [nvarchar](50) NULL,
[location] [nvarchar](200) NULL,
[description] [nvarchar](160) NULL,
[profile_image_url] [nvarchar](200) NULL,
[url] [nvarchar](200) NULL,
[protected] [bit] NULL,
[followers_count] [int] NULL,
[cached_date] [datetime] NOT NULL CONSTRAINT [DF_Users_cached_date] DEFAULT (getutcdate()),
[cached_friends_status_id] [int] NULL,
[cached_status_id] [int] NULL, --New For This Demo!
[cached_status_date] [datetime] NULL, --New For This Demo!
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UsersFriends](
[id] [int] NOT NULL,
[user_id] [int] NOT NULL,
[friend_user_id] [int] NOT NULL,
[cached_date] [datetime] NOT NULL CONSTRAINT [DF_UsersFriends_cached_date] DEFAULT (getutcdate()),
CONSTRAINT [PK_UsersFriends] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Our code is going to be driven by our Twitter ID# – a number Twitter assigns each user – and it will rely on having our information already in the Users table. The easiest way to do this is to follow the steps in my last Tweet-SQL blog post about fetching your timeline, but if you’re too lazy, here’s a quick script to do it:
EXEC dbo.tweet_cfg_resultset_send 1;
EXEC dbo.tweet_usr_show 'brento', null, null;
Those two stored procedures will configure Tweet-SQL to return recordsets, and then go fetch your user information. The first ID# in the first recordset is your Twitter ID#.
Get Our Friends from the Twitter API
Now that we’ve got our schema set up, we can call the Twitter API to get our list of friends and populate it into the UsersFriends table. Here’s the code to do it, a stored procedure I like to call usp_ImportFriends:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ImportFriends]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ImportFriends]
GO
CREATE PROCEDURE [dbo].usp_ImportFriends @import_users_id INT
AS
SET NOCOUNT ON
DECLARE @xml XML
,@handle INT
,@page INT
,@optional NVARCHAR(50)
,@RowsReturned INT
,@new_cached_friends_status_id INT
,@cached_friends_status_id INT
/* Table to join statuses and users */
DECLARE @imported_statuses TABLE(
id INT
,created_at DATETIME
,text NVARCHAR(200)
,source NVARCHAR(160)
,truncated BIT
,in_reply_to_status_id INT
,in_reply_to_user_id INT
,favorited BIT
,user_id INT
,user_name NVARCHAR(50)
,user_screen_name NVARCHAR(50)
,user_location NVARCHAR(200)
,user_description NVARCHAR(160)
,user_profile_image_url NVARCHAR(200)
,user_url NVARCHAR(200)
,user_protected BIT
,user_followers_count INT
)
SET @page = 1;
SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));
/* Turn off resultsets from Tweet-SQL because we'll be using XML data */
EXEC dbo.tweet_cfg_resultset_send
0;
/* Get the first set of statuses */
WHILE (@page <= 20)
BEGIN
EXEC dbo.tweet_usr_friends
@import_users_id ,
@optional ,
@xml OUTPUT;
EXEC sp_xml_preparedocument
@handle OUTPUT ,
@xml;
/* Copy the XML data into a single table to make handling easier */
INSERT INTO @imported_statuses
(id
,created_at
,text
,source
,truncated
,in_reply_to_status_id
,in_reply_to_user_id
,favorited
,user_id
,user_name
,user_screen_name
,user_location
,user_description
,user_profile_image_url
,user_url
,user_protected
,user_followers_count)
SELECT DISTINCT id
,dbo.tweet_fnc_dateconvert(created_at)
,text
,source
,truncated
,in_reply_to_status_id
,in_reply_to_user_id
,favorited
,user_id
,user_name
,user_screen_name
,user_location
,user_description
,user_profile_image_url
,user_url
,user_protected
,user_followers_count
FROM OPENXML (@handle, '/users/user', 2)
WITH (created_at NVARCHAR(50) 'status/created_at',
id INT 'status/id',
text NVARCHAR(200) 'status/text',
source NVARCHAR(200) 'status/source',
truncated NVARCHAR(5) 'status/truncated',
in_reply_to_status_id INT 'status/in_reply_to_status_id',
in_reply_to_user_id INT 'status/in_reply_to_user_id',
favorited NVARCHAR(5) 'status/favorited',
user_id INT 'id',
user_screen_name NVARCHAR(200) 'screen_name',
user_name NVARCHAR(200) 'name',
user_location NVARCHAR(200) 'location',
user_description NVARCHAR(200) 'description',
user_profile_image_url NVARCHAR(200) 'profile_image_url',
user_url NVARCHAR(200) 'url',
user_protected NVARCHAR(5) 'protected',
user_followers_count INT 'followers_count')
EXEC sp_xml_removedocument
@handle
/* Insert new members that haven't been cached yet.
The not-exists clause at the end is to avoid adding duplicate users. */
INSERT INTO dbo.Users
(id
,name
,screen_name
,location
,description
,profile_image_url
,url
,protected
,followers_count)
SELECT DISTINCT imp.user_id
,user_name
,user_screen_name
,user_location
,user_description
,user_profile_image_url
,user_url
,user_protected
,user_followers_count
FROM @imported_statuses imp
LEFT OUTER JOIN dbo.Users u
ON imp.user_id = u.id
WHERE u.id IS NULL
AND NOT EXISTS (SELECT *
FROM @imported_statuses impNewer
WHERE imp.user_id = impNewer.user_id
AND imp.id < impNewer.id)
/* Import statuses that haven't been cached yet */
INSERT INTO dbo.Statuses
(id
,created_at
,text
,source
,truncated
,in_reply_to_status_id
,in_reply_to_user_id
,favorited
,user_id)
SELECT DISTINCT imp.id
,imp.created_at
,imp.text
,imp.source
,imp.truncated
,imp.in_reply_to_status_id
,imp.in_reply_to_user_id
,imp.favorited
,imp.user_id
FROM @imported_statuses imp
LEFT OUTER JOIN dbo.Statuses s
ON imp.id = s.id
WHERE s.id IS NULL
AND imp.id IS NOT NULL
SET @RowsReturned = (SELECT COUNT(* )
FROM @imported_statuses)
SET @page = @page + 1
SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));
/* If we retrieved less than 100 rows, then we hit the last page. */
IF @RowsReturned < 100
BEGIN
SET @page = 999
END
/* Empty out the holding table for the next pass */
DELETE @imported_statuses
END
/* Update the Users table with the most recently cached record. */
UPDATE dbo.Users
SET cached_friends_status_id = @new_cached_friends_status_id
WHERE id = @import_users_id
SET NOCOUNT OFF
When you execute this stored procedure, call it with your ID# that we fetched earlier. Here’s how I do it, since my ID# is 495643:
EXEC dbo.usp_ImportFriends 495643;
This fetches all of your friends, but be aware that it will burn one Twitter API request for each 100 of your friends. If you’ve got 500 friends, that’s 5 API calls. There’s a limit to how many API calls you can make per hour. To get around that, you can request to be whitelisted for the Twitter API, meaning you can call it as often as you want. (My first request got denied, putting in my second one now with more info.)
Twitter has implemented a limit of 2,000 friends. Some users have been grandfathered in with higher friend limits: if you’re one of those folks, you probably don’t care how many friends you have, but be aware that this stored proc will only fetch up to 2,000 friends, and only call the API up to 20 times.
Problem: Private User Updates Aren’t Included
After running that usp_ImportFriends stored procedure, run this query to show what users haven’t been active:
SELECT u.name, u.screen_name, u.protected,
(SELECT TOP 1 created_at FROM dbo.Statuses s
WHERE u.id = s.user_id ORDER BY s.created_at DESC) AS last_status_update
FROM dbo.Users u
ORDER BY 4
You’ll find that users who have protected their Twitter stream won’t show a recent status update. Dang! Well, for the purpose of this demo, we’re going to assume that protected users are worth following, and we won’t include them in our analysis.
Finding The Dead Wood
Let’s query the database to fetch user information, and we’ll order it by the time the user sent their last update:
SELECT u.name, u.screen_name, u.location, u.description, u.profile_image_url,
u.url, u.followers_count, ('http://twitter.com/' + screen_name) AS twitter_url,
(SELECT TOP 1 created_at FROM dbo.Statuses s
WHERE u.id = s.user_id ORDER BY s.created_at DESC) AS last_status_update
FROM dbo.Users u
WHERE u.protected = 0
ORDER BY 9
Twitter People To Unfollow, SQL Style
My results are shown at right, and just by glancing at them, I can see that I could easily dump a bunch of people. For example, @HurricaneIke doesn’t really matter to me anymore, and I can see why that account hasn’t been updating lately.
I could write a T-SQL script to simply unfollow people who haven’t updated in X days, but there are some people in the list that I want to keep no matter what. If @cwgabriel of Penny Arcade starts tweeting again, I wanna know.
Instead, what I really need is a web page that I can click on when I want to unfollow them.
I know Classic ASP, and I could write a page that would query SQL Server to build the result set, but we’re knee-deep in T-SQL here and it’s time to show off a really ugly T-SQL hack. Let’s build the whole thing entirely with T-SQL.
Building the Web Page
This last query takes the results from our database and formats them HTML style. This is horrendous code, and you should never ever do anything like this in production. Don’t generate HTML inside the database. It’s a bad idea for a million reasons.
Having said that, let’s do it! Run this query:
SELECT ‘<p><img src=”‘ + u.profile_image_url + ‘” width=”40″ height=”40″ align=”left”>’
+ ‘<a href=”http://twitter.com/’ + u.screen_name + ‘” target=”_blank”>’ + u.screen_name + ‘</a><br />’
+ u.location + ‘<br />’
+ u.description + ‘<br />’
+ ‘URL: <a href=”‘ + u.url + ‘” target=”_blank”>’ + u.url + ‘</a><br />’
+ ‘Followers: ‘ + CAST(u.followers_count AS VARCHAR(20)) + ‘<br />’
+ ‘Last Update: ‘ + COALESCE((SELECT TOP 1 CAST(created_at AS VARCHAR(20)) FROM dbo.Statuses s
WHERE u.id = s.user_id ORDER BY s.created_at DESC),’Never’) + ‘</p><hr>’
FROM dbo.Users u
WHERE u.protected = 0
ORDER BY (SELECT TOP 1 created_at FROM dbo.Statuses s
WHERE u.id = s.user_id ORDER BY s.created_at DESC)
People to Unfollow, HTML Style
You’ll notice that this is not cleanly formatted like the rest of my SQL code examples. That’s because WordPress, my blog software, starts choking when I mix T-SQL code with HTML in my code examples. Heck, so does my SQL Server!
When you run that query, you’ll get a list of records that don’t mean much to you.
Open your favorite text editor and start a new file called MyFriends.html. Put <html><body> at the top on a new line, then copy/paste your results from SQL Server into the text file. Then add </body></html> at the end of the file, save it, and preview it with your web browser by double-clicking on the file in Windows Explorer. If you’re lucky, it’ll look like my example at right.
Their name links to their Twitter page, and it opens in a new window so that you can race through the list unfollowing people.
Happy pruning!
By remixing the “ORDER BY” clause of the query, you can also sort by u.followers_count descending. I find that early in my Twittering, I followed some people who seemed cool just because they were huge, like @scobleizer, but as I get more used to Twitter, I find those people less interesting.
Wondering Who’s Unfollowing You?
If you’re curious about who unfollows you, there’s a service called Qwitter that will email you whenever someone unfollows you. I’ve had mixed luck with it – it hasn’t sent me an email in months, and I refuse to believe I’m that magnetic.
When Qwitter emails you, it includes the tweet that caused them to unfollow you – the straw that broke the camel’s back, if you will. Don’t put too much faith into that, because it has to do with the frequency that Qwitter runs. They might have unfollowed you several hours prior to that particular tweet.
The dangers of building linked servers on the fly
Linked servers let users query different database servers from a central SQL Server. Some uses for this might be:
- DBA utility scripts – from a central repository server, we could query all of the servers in our environment to check for things like backup dates or configuration settings
- Consolidated reporting – we could gather results from multiple databases on different servers and join them together into a single query
- Moving data from platform to platform – if we had an Oracle, AS400, etc platform and we needed that data in SQL Server, we could fetch it periodically through a linked server query
All of these work great in theory. The danger comes in when we start scaling up with more data or more linked servers.
As we deal with more and more data, linked servers are less efficient. Say we’re joining a ten million row sales table on Server A with a ten thousand row customer table on Server B. That query might run fine if we run it from Server A, with Server B set up as a linked server. Since Server B only has to pull 10,000 rows and push them all through the network to Server A, we may not see big performance problems. However, if we try to run that query on Server B, with Server A set up as a linked server, we can have truly horrendous performance depending on how we write the query.
As we scale out to more servers, the temptation arises to build the linked server connection on the fly, like this:
- If a linked server connection does not exist to Server B, build it
- Run our query
- If we added the linked server connection in step 1, then delete it
We’ve now introduced a problem: if our query throws a ginormous error and bails out, we never delete our linked server connection. We have to be very careful about error checking in step 2, especially around deadlocks. If a deadlock arises and SQL Server kills our connection, we’re really in trouble – the linked server connection will stick around.
Worse, if the query takes any time whatsoever to run, we suddenly introduce at least two scenarios where this process will fail:
- If a second automated query fires up while the query is running, it too will check to see if the linked server exists. When it doesn’t, it will start running a linked server query. When our original query finishes, it will try to tear down the linked server connection while it’s still in use by our new query.
- If a human being (damn those meatbags!) sees a linked server connection that shouldn’t exist, they might try to delete it while our query is running.
Another potential problem is that server names change, and databases are moved. When your query is initially designed, your databases might be on several different machines and require the linked server connection. Later, when servers are consolidated, these databases may all be on the same server. If the code isn’t solid enough, you might build linked server connections to servers that no longer exist – or worse, the local server itself.
Bottom line: before building your own linked server connections programatically, whiteboard out every step in the process, and assume that it will fail sooner or later. Plan for that failure.
Discovery Wizard: Building a Free Product With Your Help
Quest Discovery Wizard for SQL Server is a free program that goes through your network and your Active Directory looking for SQL Servers. It discovers things about the server, like what databases are on it, what applications are connected, what version numbers it has, and so on. Version 2.0 just came out today.
I wish I’d have known about this product when I started working at a new job as a DBA. It searches your environment, discovers all of the SQL Server instances in your environment, and gives you information about what’s going on inside those servers.
If I’d have known about it, it would have been the first product I ran when I walked into a shop. Nobody ever has a good handle on every server in the house, and things change rapidly – SQL Server sprawls like crazy. Seems like we only find out about them when a user drops a database and they want to restore from a backup – but since we never knew about the instance, there’s no backups. Whoops. Bad first impression.
Killer Potential, Minimal Resources
When I visited Russia last year and talked to Andrey Kviatkovsky, the development manager for a lot of our stuff, I spent a lot of time jumping up and down in front of a whiteboard, drawing all kinds of crazy ideas about what I wanted Discovery Wizard to do. Being a free product, though, it didn’t have a lot of development resources allocated to it, so we couldn’t make much headway.
We didn’t have any marketing resources for it either, so as a result, most of you never heard of this product. (Heck, I didn’t hear about it before I started with Quest.) If you search the web for it right now, you might run across the Discovery Wizard user community on Quest.com. However, it’s not exactly a friendly web site, and it pales in comparison to the web presence for the similarly free Toad for MySQL.
I didn’t forget about it when I left Russia, and it’s been bubbling away at the back of my mind for months. I’ve had conversation after conversation with our development guys, product management, and marketing, talking about all the cool things we could build into this with a minimum of development effort. Database administrators have a ton of pain points that we could solve with just one good community-oriented tool, and I believe that Discovery Wizard (or DiscoWhiz, as I affectionately call it) could be that tool.
Quest Listened and Invested Resources
At the risk of sounding like somebody who’s been drinking a little too much of the Kool-Aid, I shouldn’t have been surprised that we got our wish for more resources.
Quest takes the whole community idea pretty seriously, as evidenced by some of the stuff they put money and resources into:
- Toad for MySQL Freeware – folks who like to develop against the free database can use a killer free tool to do it.
- SQLServerPedia – Quest took a formerly pay-to-play product, KnowledgeXpert, and put all of the content into an open wiki for anybody to use and improve.
- PowerGUI – long before PowerShell started catching on, Quest dedicated full-time employees to building this free tool for PowerShell developers and even built a web community around it.
- Guys like Kevin Kline and Joel Oleson – Quest hires some of the best and brightest experts in their fields and puts them to work in the community helping other people.
And now we’re going to turn Discovery Wizard into a tool that I am absolutely convinced will be a vital tool in your arsenal when the next release comes out. You’re not going to believe you ever got by without it.
If I’m wrong, I’m only going to have myself to blame, because those wackos put me in charge of working with Andrey and the developer to flesh out the requirements, do the testing, and deliver this baby.
That’s Where You Come In
That’s where you come in, dear readers: to act as my scapego – wait, I mean, sounding board. Over the coming weeks, I’m going to blog about:
- The features I want to get into Discovery Wizard 3.0
- The development and marketing process for free software
- Where we’ll need to compromise to get it out the door – as the quote goes, “Shipping is a feature too.”
- How the community will be able to take this thing and run with it
For now, you can check out Discovery Wizard 2.0. If you run into issues, post them in the Discovery Wizard community: they’ll either be answered by me, or by the support and development staff at Quest.
Twitter is What You Make It
If you follow people that don’t interest you, then you won’t be interested in Twitter.
If you follow people that interest you, then you’ll be interested in Twitter.
It’s just that simple.
Saying Twitter sucks is like saying the web sucks, or that music sucks, or that talking sucks. It just indicates that you’re unclear on the concept.
When I hear people saying they don’t like Twitter, and I start asking about why, it usually falls into one of two categories:
- They didn’t have enough spare time for it. If you don’t have enough spare time to do something, that doesn’t mean it sucks. I don’t have enough spare time to go fishing right now, but that doesn’t mean fishing sucks.
- They followed the wrong people. Just because somebody follows you doesn’t mean you have to follow them back. As of right now, I’m only following about half the number of people that are following me. I follow people ***I*** find interesting, not people who find ***ME*** interesting. To those of you who are following me and I’m not following back, don’t take it personally: it’s not you, it’s me.
If you want to enjoy Twitter, follow people who really interest you, and only follow as many as you have the spare time to interact with. That’s why I only follow a fraction of the people who follow me – it’s not that I don’t like everybody, but there’s just not enough hours in the day to hear everybody.
More of My Twitter Articles
- Top 10 Reasons I’m Not Following You On Twitter – wondering why nobody’s following you back?
- Top 10 Reasons I *AM* Following You – how I pick who gets past my virtual bouncer.
- Twitter T-Shirts and Coffee Cups – you can get Twitter shirts with the fail whale, or get your own Twitter profile on a shirt.
- Twitter RT, OH, HT: The Slang Explained – I talk about what these cryptic terms mean and how to use them correctly.
- Twitter Client Demo Video – I show you TweetDeck, Seesmic Desktop and Orsiso, three different Twitter clients.
- How to Make a Bigger Twitter Profile – when people glance at your Twitter profile, they make a snap decision about whether to follow you. Give them the information they need to make a good decision.
- Browse All Posts Tagged “Twitter”
- How To Start a Blog – decide why you’re blogging, because that’ll affect the type of blog you start and how you build it.
- Browse All Posts Tagged “Blogging”
Want More Blogging & Twitter Tips? Follow me on Twitter. I tweet whenever I post a new blog entry, so you’ll always know when I’ve got new stuff. See you online!
