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;