Befriend Twitter users on the sqlserverpedia.com list

If you want a bit of background on this then read this post by statisticsio then this which will in turn lead you to sqlserverpedia. If you're still reading then you may be interested in following all these users. Here's how we can do it nice and easily with a bit of magic in Excel and then with Tweet-SQL.

I’m using Excel to first process some data copied off this page to get it into a format we can work with.

Copy the list of people on http://sqlserverpedia.com/wiki/Twitter  starting at the very first one in the section titled “SQLServerPedia Editors on Twitter”



and finishing at the very last user specified in the section called “SQL Server Vendors, Analysts…”. Once copied,  paste this list into Excel…

Remove the section titles and empty rows so we are just left with a list of users…



Next remove any text that has been placed after the twitter url.

Next this formula should be placed into cell B1 and applied to all other cells below it

=SUBSTITUTE(MID(A1, SEARCH("http://twitter.com/", SUBSTITUTE(A1, "www.", ""), 1)  + 19, 20), "/", "")

If all goes well this should produce something like this…




Check the column to see if the data is good (at the time of writing results look perfect) and modify any if need be.
This will give us a list of users that we will follow.  You may well be following some of these users so you may wish to remove them and any duplicates. If you leave them in the TSQL script you’ll waste a valuable API call and Tweet-SQL will report the following error...

Error: The remote server returned an error: (403) Forbidden.

Next import this into a SQL server database that has the Tweet-SQL procedures.  Call the table sqlserverpedia and the username column twitter_name.
Run the following TSQL...

-- Create an index on twitter username
CREATE INDEX idx_twitter_name ON sqlserverpedia (twitter_name);
-- Add a column called done
ALTER TABLE sqlserverpedia ADD done BIT NOT NULL DEFAULT 0;

The table should now look something like this...



Here is the script that will work with our table of Twitter users and attempt to befriend each of them.  The script below works with batches of 10 users at a time waiting for 40 seconds between befriending each one. You can control this behavior by modifying the values set for @batch_size and @waitfor but be aware of your API limit.

DECLARE @twitter_name NVARCHAR(50),
  @batch_size INT,
  @waitfor VARCHAR(8),
  @status INT;

-- Set the number of users for each batch
-- We don't want to hurt the Twitter servers
-- or hit your API limit! 
SET @batch_size = 10;
SET @waitfor = '00:00:040' -- 40 second wait time

-- Create a cursor to select batches of twitter users
DECLARE twitterCursor CURSOR LOCAL STATIC FOR SELECT TOP(@batch_size) twitter_name
             FROM dbo.sqlserverpedia
             WHERE done = 0;

-- Turn off resultsets from Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 0;

-- Open the cursor and get the first row
OPEN twitterCursor;
FETCH NEXT FROM twitterCursor INTO @twitter_name;

-- Loop through the cursor
WHILE(@@FETCH_STATUS = 0)
BEGIN

 -- Befriend the user
 EXEC @status = dbo.tweet_frd_create @twitter_name, null;

 IF(@status = 0)
 BEGIN

  PRINT 'Befriended user ' + @twitter_name;
  -- Update the record to indicate it has been processed
  UPDATE dbo.sqlserverpedia
  SET done = 1
  WHERE twitter_name = @twitter_name;

 END
 ELSE
 BEGIN
  PRINT 'Problem with ' + @twitter_name + ' already following?';
  UPDATE dbo.sqlserverpedia
  SET done = 1
  WHERE twitter_name = @twitter_name;
 END

 -- Wait for a bit
 WAITFOR DELAY @waitfor;

 -- Get the next row 
 FETCH NEXT FROM twitterCursor INTO @twitter_name;

END

-- Clean up 
CLOSE twitterCursor;
DEALLOCATE twitterCursor;

-- Turn on resultsets from Tweet-SQL as appropriate
EXEC dbo.tweet_cfg_resultset_send 1;

On completion SSMS will spit out something like below. The 403 errors result from the script attempt to befriend a user you are already friends with.

Error: The remote server returned an error: (403) Forbidden.
Problem with brento already following?

(2 row(s) affected)
Error: The remote server returned an error: (403) Forbidden.
Problem with statisticsio already following?

(2 row(s) affected)
200 OK: everything went awesome.
Befriended user aspiringgeek

(1 row(s) affected)
200 OK: everything went awesome.
Befriended user MicrosoftBI

(1 row(s) affected)
200 OK: everything went awesome.
Befriended user CindyGross

(1 row(s) affected)
200 OK: everything went awesome.
Befriended user aaronbertrand

(2 row(s) affected)
200 OK: everything went awesome.
Befriended user andyleonard

(2 row(s) affected)
200 OK: everything went awesome.
Befriended user brianknight

(2 row(s) affected)
200 OK: everything went awesome.
Befriended user cwoodruff

(1 row(s) affected)
200 OK: everything went awesome.
Befriended user hallidayd

(1 row(s) affected)

Try this out yourself, download a copy of Tweet-SQL now!