Archiving another Twitter user's timeline with Tweet-SQL

Here's a modified version of the script I created to archive your twitter timeline with Tweet-SQL. This script will allow you to archive another users timeline provided they haven't protected their updates. This script will be good for archiving 2000 statuses before you hit your api limit. In a future script I'll show how you can archive even larger timeline using the since_id and count in the @optional parameter.

First create a table to hold the users timeline...

CREATE TABLE TwitterArchive

(

      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),

      status_Id INT,

      statuses_Id INT                                                              

);

Just modify the value for @user_id_or_nick and you're good to go...

DECLARE @xml XML, 
            @handle INT,
            @page INT,
            @optional NVARCHAR(10),
            @rowcount TINYINT, 
            @user_id_or_nick NVARCHAR(30);


-- Set the users timeline to archive
 
SET @user_id_or_nick = 'lost_in_bangkok';
 
-- Starting page

SET @page = 1;
 
-- Turn off resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;
-- Set the optional parameter to request page 1
SET @optional = '?page=' + CAST(@page AS NVARCHAR(4));
  
-- Get page 1 of your timeline
EXEC dbo.tweet_sts_user_timeline @user_id_or_nick, @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.TwitterArchive
      (
            created_at,
            id,
            [text],
            source,
            truncated,
            in_reply_to_status_id,
            in_reply_to_user_id,
            favorited,
            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,
                  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),
            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));
  
      -- 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_user_timeline @user_id_or_nick, @optional, @xml OUTPUT;
 
END
  
-- Turn resultsets from Tweet-SQL back on as appropriate

EXEC dbo.tweet_cfg_resultset_send 1;
  

Using Tweet-SQL to post multiple updates

Many Twitter users seem to post large numbers of updates to promote news, events, or their websites. Doing this manually would be rather time consuming. So here's how easy it is to do with Tweet-SQL...

First lets create a database specifically for this purpose.

-- Create a db called twitter

CREATE DATABASE twitter;

Now create a table and insert some Tweets

USE twitter;

 

-- Create a table to hold our tweets

CREATE TABLE TwitterPosts

(

      id INT PRIMARY KEY IDENTITY(1, 1),

      tweet VARCHAR(140) NOT NULL

);

 

 

-- Post some links to YouTube videos of

-- Peter Sellers in the Pink Panther films

INSERT INTO TwitterPosts

(

      tweet

)

SELECT 'Does your dog bite? http://uk.youtube.com/watch?v=SXn2QVipK2o'

UNION ALL

SELECT 'Not now Kato! http://uk.youtube.com/watch?v=c_Jb2Vd4BJY'

UNION ALL

SELECT 'The lift fart... http://uk.youtube.com/watch?v=ViYclyAkkDg'

UNION ALL

SELECT 'The bath scene... http://uk.youtube.com/watch?v=AtLbsyx-1f4'

UNION ALL

SELECT 'The opening to "The Pink Panther"... http://uk.youtube.com/watch?v=HhHwnrlZRus'

UNION ALL

SELECT 'The famous Theme music performed by a live band... http://uk.youtube.com/watch?v=jBupII3LH_Q'

UNION ALL

SELECT 'Outakes... http://uk.youtube.com/watch?v=h0vW5O42cFA'

UNION ALL

SELECT 'The Revenge of the Pink Panther... http://uk.youtube.com/watch?v=r1cbl0zwFws'

UNION ALL

SELECT 'The Pink Panther Strikes Again... http://uk.youtube.com/watch?v=1SG4xb_o8BM'

UNION ALL

SELECT 'Can Steve Martin compare? http://uk.youtube.com/watch?v=iUCDhvbQFmU';

Here's the TSQL script that makes posting your table of Tweets easy...

-- Setup variables for tweeting

DECLARE @tweet VARCHAR(140),

            @id INT;

 

-- Turn off resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

 

-- Setup a cursor

DECLARE tweetCursor CURSOR LOCAL FAST_FORWARD FOR SELECT id

                                                  FROM dbo.TwitterPosts;

 

-- Open the cursor and get the first row

OPEN tweetCursor;

FETCH NEXT FROM tweetCursor INTO @id;

 

WHILE (@@FETCH_STATUS = 0)

BEGIN

 

      -- Get the Tweet to post

      SET @tweet = (SELECT tweet FROM dbo.TwitterPosts WHERE id = @id);

     

      -- Tweet!

      EXEC dbo.tweet_sts_update @tweet, null;

      PRINT 'Posted tweet with id = ' + CAST(@id AS VARCHAR(4)) + ', "' + @tweet + '".';

 

      -- Wait for a while to avoid hitting the API limit

      -- One minute delay here

      WAITFOR DELAY '00:01';

 

      -- Get the next row

      FETCH NEXT FROM tweetCursor INTO @id;

 

END

 

-- Clean up

CLOSE tweetCursor;

DEALLOCATE tweetCursor;

 

-- Turn on resultsets from Tweet-SQL as appropriate

EXEC dbo.tweet_cfg_resultset_send 1;

That's it! If all goes well SQL Server Management Studio will return the following...

200 OK: everything went awesome.
Posted tweet with id = 1, "Does your dog bite? http://uk.youtube.com/watch?v=SXn2QVipK2o".
200 OK: everything went awesome.
Posted tweet with id = 2, "Not now Kato! http://uk.youtube.com/watch?v=c_Jb2Vd4BJY".
200 OK: everything went awesome.
Posted tweet with id = 3, "The lift fart... http://uk.youtube.com/watch?v=ViYclyAkkDg".
200 OK: everything went awesome.
Posted tweet with id = 4, "The bath scene... http://uk.youtube.com/watch?v=AtLbsyx-1f4".
200 OK: everything went awesome.
Posted tweet with id = 5, "The opening to "The Pink Panther"... http://uk.youtube.com/watch?v=HhHwnrlZRus".
200 OK: everything went awesome.
Posted tweet with id = 6, "The famous Theme music performed by a live band... http://uk.youtube.com/watch?v=jBupII3LH_Q".
200 OK: everything went awesome.
Posted tweet with id = 7, "Outakes... http://uk.youtube.com/watch?v=h0vW5O42cFA".
200 OK: everything went awesome.
Posted tweet with id = 8, "The Revenge of the Pink Panther... http://uk.youtube.com/watch?v=r1cbl0zwFws".
200 OK: everything went awesome.
Posted tweet with id = 9, "The Pink Panther Strikes Again... http://uk.youtube.com/watch?v=1SG4xb_o8BM".
200 OK: everything went awesome.
Posted tweet with id = 10, "Can Steve Martin compare? http://uk.youtube.com/watch?v=iUCDhvbQFmU".