New home for Tweet-SQL Examples

New examples for Tweet-SQL will be posted at my new blog http://www.youdidwhatwithtsql.com This will also be the home for the forthcoming Ping-SQL product examples as well as other SQL Server and Technical articles.

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!

10 minute TweetWasters Clone with Tweet-SQL

Today I came across Tweetwasters which i basically a little bit of fun showing you approximately how much time you've spent on Twitter.

Here's my profile...



Here's a quick 10 minute script on how to clone this using Tweet-SQL. Just modify the value set in @username and you're good to go

DECLARE @username VARCHAR(20),
  @xml XML,
  @handle INT,
  @statuses_count INT,
  @seconds INT,
  @minutes INT,
  @hours FLOAT,
  @days FLOAT;

-- Set username here!
SET @username = 'rhyscampbell';

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

-- Get the Twitter users' info
EXEC dbo.tweet_usr_show @username, null, @xml OUTPUT;

-- Create an xml document
EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

-- Get the statuses count
SELECT @statuses_count = statuses_count
FROM OPENXML (@handle, '/user', 2)
WITH
(
 statuses_count INT
);

-- Remove the xml doc from memory
EXEC sp_xml_removedocument @handle;

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

-- Do our calculations
SET @seconds = (@statuses_count * 30);
SET @minutes = ROUND((@seconds / 60), 0);
SET @hours = ROUND((@minutes / 60.00), 2);
SET @days = ROUND((@hours / 24.00), 2);

-- Show the results
SELECT @username + ' has ' + CONVERT(VARCHAR, @statuses_count) + ' total tweets ' AS TweetWaster UNION ALL
SELECT 'and assuming they spent an average of 30 seconds per tweet they''ve spent ' UNION ALL
SELECT CONVERT(VARCHAR, @seconds) + ' seconds or ' UNION ALL
SELECT CONVERT(VARCHAR, @minutes) + ' minutes or ' UNION ALL
SELECT CONVERT(VARCHAR, @hours) + ' hours or ' UNION ALL
SELECT CONVERT(VARCHAR, @days) + ' days using Twitter!';

and SSMS will return something like the following...



Get your own Twitter app up and running fast by downloading Tweet-SQL now!

Twitter Sources: What Apps are being used?

Some of you may have seen this neat twitter mashup over on funkatron which provides some nice stats of what Twitter Apps are being used by sampling the public timeline.

Funkatron says...

"This data is derived from 20-message slices taken every minute from the Twitter public timeline. It currently reports the overall usage since the data collection began (2008-07-31), as well as a few common time periods."

I thought it would be fun to replicate this with Tweet-SQL. The Funkatron page does allow you to drill down through various date ranges but I've just done a total count for the sake of simplicity. A few modifications to the script and you could also break by date if needed. Please be aware I've used a couple of features new to SQL Server 2008 so if you're using 2005 you need to modify it to work (script is commented in the appropriate places).

This script will call the public timeline every one minute for the number of iterations specified. In the example below @iterations is set to 100 so this script will take over 100 minutes to complete!

-- Create the TwitterSources table if it doesn't already exist
IF (OBJECT_ID('TwitterSources', 'U')) IS NULL
BEGIN
 CREATE TABLE TwitterSources
 (
  [source] NVARCHAR(100) PRIMARY KEY NOT NULL,
  [count] INT NOT NULL DEFAULT 0
 );
END

-- Using 2008 variable initialization here, change if using 2005
DECLARE @xml XML,
  @handle INT,
  @iterations INT = 100, 
  @count INT = 0;

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

-- Loops reading the public timeline for the specified number of iterations
WHILE (@count < @iterations)
BEGIN

 -- Get the pubic timeline
 EXEC dbo.tweet_sts_public_timeline @xml OUTPUT;

 -- Prepare an xml document
 EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

 SELECT source, COUNT(*) AS [count]
 INTO #PublicTimelineSource
 FROM OPENXML (@handle, '/statuses/status', 2)
 WITH 
 (
  source NVARCHAR(100)
 )
 GROUP BY source;

 -- Add this timelines source count to the TwitterSources 
 -- This is also a 2008 feature, rewrite if using 2005!
 MERGE dbo.TwitterSources AS [target]
 USING #PublicTimelineSource AS temp
 ON [target].source = temp.source
 WHEN MATCHED THEN
  UPDATE SET [target].[count] = [target].[count] + temp.[count]
 WHEN NOT MATCHED THEN
  INSERT ([source], [count])
  VALUES (temp.source, temp.[count]);

 -- destroy the xml document
 EXEC sp_xml_removedocument @handle;

 -- Drop the tempory table
 DROP TABLE #PublicTimelineSource;

 -- Wait for a one minute
 -- The public_timeline is cached for 1 minute so no pointed less than this
 WAITFOR DELAY '00:01:00';

 -- Increment count
 SET @count = @count + 1;
 PRINT 'Completed iteration ' + CAST(@count AS VARCHAR(5));

END

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

The TwitterSources table will be populated with a count of all the Twitter apps that were encountered from the public timeline sampling. Here are the top ten rows from one of my test runs...

web - 959

txt - 174

twitterfeed - 156

twitterrific - 142

TweetDeck - 137

TwitterFox - 84

twhirl - 81

TwitterBerry - 54

Twinkle - 30

Tweetie - 30

Archiving your @replies with Tweet-SQL

Tweet-SQL is a Twitter Client for Microsoft SQL Server 2005
and above. Over 30 procedures allow you to leverage the Twitter API
with standard TSQL. This site contains lots of examples of the stuff you can do with the Tweet-SQL procedures. Get stuck in by downloading Tweet-SQL today!

Want to archive your @replies? Here's how to do it with Tweet-SQL

First create a table to hold your @replies...

-- Create a Table to hold the @Replies

CREATE TABLE TwitterReplies

(

      created_at DATETIME,

      id INT PRIMARY KEY,

      [text] NVARCHAR(140),

      source NVARCHAR(100),

      truncated NVARCHAR(5),

      in_reply_to_status_id INT,

      in_reply_to_user_id INT,

      favorited NVARCHAR(5),

      in_reply_to_screen_name NVARCHAR(50),

      status_Id INT,

      statuses_Id INT                                                              

);

Running the below script will archive your @replies into the table created above. Watch out for the API limit if you have a large number of @replies. Consider changing the WAITFOR DELAY value for best results.

DECLARE @xml XML,

            @handle INT,

            @page INT,

            @optional NVARCHAR(10),

            @rowcount TINYINT;

 

SET @page = 1;

 

-- Turn of resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

 

-- Set the optional parameter to request page 1 of the @replies

SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));

-- Get page 1 of your @replies

EXEC dbo.tweet_sts_replies @optional, @xml OUTPUT;

SET @rowcount = 20;

 

WHILE (@rowcount = 20) -- While we still have results to deal with

BEGIN

      -- Prepare an xml document

      EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

 

      -- Insert the page into a table

      INSERT INTO dbo.TwitterReplies

      (

            created_at,

            id,

            [text],

            source,

            truncated,

            in_reply_to_status_id,

            in_reply_to_user_id,

            favorited,

            in_reply_to_screen_name,

            status_Id,

            statuses_Id

      )

      SELECT  dbo.tweet_fnc_dateconvert(created_at),

                  id,

                  [text],

                  source,

                  truncated,

                  in_reply_to_status_id,

                  in_reply_to_user_id,

                  favorited,

                  in_reply_to_screen_name,

                  status_Id,

                  statuses_Id

      FROM OPENXML (@handle, '/statuses/status', 2)

      WITH

      (

            created_at NVARCHAR(30),

            id INT,

            [text] NVARCHAR(140),

            source NVARCHAR(100),

            truncated NVARCHAR(5),

            in_reply_to_status_id INT,

            in_reply_to_user_id INT,

            favorited NVARCHAR(5),

            in_reply_to_screen_name NVARCHAR(50),

            status_Id INT,

            statuses_Id INT

      );

 

      -- Get the rowcount, when < 20 then we have the last page

      SET @rowcount = @@ROWCOUNT;

      -- Which page have we done?

      PRINT 'Archived page ' + CAST(@page AS NVARCHAR(4)) + ' of your @replies';

      -- destroy the xml document

      EXEC sp_xml_removedocument @handle;

      -- Increment the page count

      SET @page = @page + 1;

      -- Setup the optional parameter

      SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));

      -- Wait for a bit...

      WAITFOR DELAY '00:00:05';

      -- Get the next page

      EXEC dbo.tweet_sts_replies @optional, @xml OUTPUT;

END

 

-- Turn resultsets from Tweet-SQL back on as appropriate

EXEC dbo.tweet_cfg_resultset_send 1;

Tweet-SQL Released!

The first version of Tweet-SQL has been released just before 2009! Have a look at the features Tweet-SQL boasts, check out some screenshots, and download Tweet-SQL!

I look forward to improving Tweet-SQL even more over 2009.

Happy New Year!

Blogged with the Flock Browser

twtki: a wiki built by tweets

One interesting thing about the Twitter API is the vast number of applications where it has been put to use. Yesterday I came across twtki which is literally a wiki built by the tweets sent to @twtki. Nice!

Here's how Tweet-SQL could be used to pull down the @replies from @twtki and fed into a database.

First lets create a table for holding these...

CREATE TABLE twtki

(

      created_at DATETIME,

      id INT PRIMARY KEY,

      [text] NVARCHAR(240),

      source NVARCHAR(100),

      truncated NVARCHAR(5),

      in_reply_to_status_id INT,

      in_reply_to_user_id INT,

      favorited NVARCHAR(5),

      status_Id INT,

      statuses_Id INT,

      definer NVARCHAR(240),

      topic NVARCHAR(240),

      definition NVARCHAR(240)     

);

Sadly there seems to be various issues with the since_id parameter. The since_id parameter does not function the way you would expect, i.e. if since_id = 100 then you would expect it to return 101-120 but it actually returns the 20 most recently sent @replies, i.e. 500-519. Combining this with the count parameter as mentioned in the google group doesn't seem to help but then this isn't listed in the API docs for the replies method.

This only seems to be an issue if you're bothered about archiving all of your @replies. This script picks up new @replies without any evident issue but should a large number of @replies be received between checks some may be missed. It would be relatvely easy to switch to using the page parameter and then cross reference status_ids if you think this will be an issue for you.

Problems aside here a script that pull down @replies, stores them in a table and attempts to format the status text into definer, topic and definition as stated on twtki.

-- Setup variables

DECLARE @since_id INT,

            @xml XML,

            @handle INT,

            @optional NVARCHAR(50),

            @rowcount TINYINT;

 

-- Get the last id to run from

SET @since_id = (SELECT ISNULL(MAX(id), 1) FROM twtki);

-- Setup the @optional paramter with since_id

SET @optional = '?since_id=' + CAST(@since_id AS NVARCHAR(10));

-- Set @rowcount so we pass hrough the loop at least once

SET @rowcount = 20;

 

-- Turn of resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

-- Get 20 @replies

EXEC dbo.tweet_sts_replies @optional, @xml OUTPUT;

 

WHILE (@rowcount = 20) -- While we still have results to deal with

BEGIN

      -- Prepare an xml document

      EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

 

      -- Insert the @replies into our twtki table

      INSERT INTO dbo.twtki

      (

            created_at,

            id,

            [text],

            source,

            truncated,

            in_reply_to_status_id,

            in_reply_to_user_id,

            favorited,

            status_Id,

            statuses_Id,

            definer,

            topic,

            definition

      )

      SELECT  dbo.tweet_fnc_dateconvert(created_at),

                  id,

                  [text],

                  source,

                  truncated,

                  in_reply_to_status_id,

                  in_reply_to_user_id,

                  favorited,

                  status_Id,

                  statuses_Id,

                  SUBSTRING([text], 2, CHARINDEX(' ', [text]) -1) AS Definer,

                  CASE

                        WHEN [text] LIKE '%: %' THEN

                              SUBSTRING([text], CHARINDEX(' ', [text]), CHARINDEX(': ', [text]) - CHARINDEX(' ', [text]))

                        ELSE

                              'Malformed'

                  END AS Topic,

                  CASE

                        WHEN [text] LIKE '%: %' THEN

                              SUBSTRING([text], CHARINDEX(': ', [text]) + 1, 240)

                        ELSE

                              'Malformed'

                  END AS Definition

      FROM OPENXML (@handle, '/statuses/status', 2)

      WITH

      (

            created_at NVARCHAR(30),

            id INT,

            [text] NVARCHAR(140),

            source NVARCHAR(100),

            truncated NVARCHAR(5),

            in_reply_to_status_id INT,

            in_reply_to_user_id INT,

            favorited NVARCHAR(5),

            status_Id INT,

            statuses_Id INT

      );

 

      -- Get the rowcount, when < 20 then we have the last page of @replies for now

      SET @rowcount = @@ROWCOUNT;

      -- Which page have we done?

      PRINT 'Retrieved ' + CAST(@rowcount AS NVARCHAR(2)) + ' @replies.';

      -- destroy the xml document

      EXEC sp_xml_removedocument @handle;

      -- Update the since_id value.

      SET @since_id = (SELECT MAX(id) FROM twtki);

      -- Setup the @optional paramter with since_id

      SET @optional = '?since_id=' + CAST(@since_id AS NVARCHAR(10));

 

      -- Wait for a bit adjust as appropriate

      WAITFOR DELAY '00:00:05';

      -- Get the next batch of @replies

      EXEC dbo.tweet_sts_replies @optional, @xml OUTPUT;

 

END

 

-- Turn resultsets from Tweet-SQL back on as appropriate

EXEC dbo.tweet_cfg_resultset_send 1;