SQLCLR NEWS

New home for Tweet-SQL Examples

2009-03-14T05:42:00.000-07:00

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

2009-01-14T04:39:00.000-08:00

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

2009-01-09T13:05:00.000-08:00

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?

2009-01-06T12:29:00.000-08:00

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

2009-01-03T11:52:00.000-08:00

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!

2008-12-31T16:09:00.001-08:00

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

2008-12-19T05:34:00.000-08:00

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;

Archiving another Twitter user's timeline with Tweet-SQL

2008-12-18T06:03:00.000-08:00

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

2008-12-02T13:27:00.000-08:00

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

(

)

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".

Dealing with HTTP formatted dates with Tweet-SQL

2008-11-10T12:10:00.000-08:00

Since my post about using Tweet-SQL to archive your Twitter timeline I noticed what a pain dealing with HTTP formatted dates is and how useful it would be to turn them into something a little more SQL Server friendly. So, I came up with a function that will allow this nasty date format to be handled a little more easily.

SELECT dbo.tweet_fnc_dateconvert('Tue Nov 04 22:33:39 +0000 2008');

Returns an ODBC canonical (with milliseconds) formatted datetime value, i.e. 2008-11-04 22:33:39.000

This function will be included in the upcoming release of Tweet-SQL.

Making friends with Tweet-SQL

2008-11-01T10:25:00.001-07:00

A friend of mine helping out with the testing of Tweet-SQL asked how he could add new friends using the supplied procs. He pointed me at a couple of products, one free and the other a whopping US$50, that allow you to add new friends to your twitter account.

http://www.download.com/Twitter-FriendAdder/3000-2650_4-10878011.html

http://www.twitteradder.com/index.php

Obviously this is something that spammers will love and can be easily abused. This article on mashtable illustrates this…

http://mashable.com/2008/04/14/twitter-spam-out-of-control/

I’m sure we’ve all had this happen on our twitter accounts. I for one was thrilled when “Leather Sofa World” decided to follow me… but I can see the attraction that marketeers may have to this. So here’s how you can do it with Tweet-SQL….

DECLARE @xml XML,

@handle INT,

@twitter_user_id INT,

@twitter_username NVARCHAR(100);

-- Turn of resultsets from Tweet-SQL

EXEC dbo.tweet_cfg_resultset_send 0;

-- Get the public timeline, we’re making friends with these guys

EXEC dbo.tweet_sts_public_timeline @xml OUTPUT;

-- Create an xml document

EXEC sp_xml_preparedocument @handle OUTPUT, @xml;

DECLARE userCursor CURSOR LOCAL FAST_FORWARD FOR SELECT id,

[name]

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

WITH

(

id INT,

[name] NVARCHAR(100)

);

-- Open the cursor and get the first result

OPEN userCursor;

FETCH NEXT FROM userCursor INTO @twitter_user_id,

@twitter_username;

-- Loop

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Making friends has never been so easy (or impersonal)

EXEC dbo.tweet_frd_create @twitter_user_id, null;

PRINT 'Friends with ' + @twitter_username;

-- Get the next row

FETCH NEXT FROM userCursor INTO @twitter_user_id,

@twitter_username;

END

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

EXEC dbo.tweet_cfg_resultset_send 1;

-- Clean up

CLOSE userCursor;

DEALLOCATE userCursor;

EXEC sp_xml_removedocument @handle;

Tweet-SQL will return something like the below once complete