Review: Twitter Search functionality for SQL Server (by SQL# – SQLsharp)

sql-sharp-logo
SQL# (SQLsharp)
Expanding the capabilities of T-SQL


SQL# is a small but extensive library of useful functions to help you code more powerful SQL Stored Procedures and User-Defined Functions.

From a Social BI’s perspective, I wanted to review the new Twitter Search functionality of SQL# (SQLsharp), which is available in the Full Version of SQL#.

System specifications:
This review was performed on a laptop with Windows 7 and SQL Server 2008R2 installed on it. Since SQL# uses a lot of the .NET framework and extra coding in DLLs, this should also work on SQL Server 2012 (and SSDT).


Solomon Rutzky did a great job by adding the Twitterizer .Net library (discontinued) and Twitter Search functionality to SQL# in this new version. For documentation about SQL#, click here.


Twitter – SearchTweets
The Full Version of SQL# provides 65 more functions than the Free Version, and it includes the Twitter: SearchTweets. An important note here, if you would only like to send tweets (twitter updates) and use other ‘basic’ Twitter functionalities, the Free Version would suffice:

SQL# – Free Version

Twitter: BlockUser, CreateFavorite, DestroyDirectMessage, DestroyFavorite, DestroyStatus, FollowUser, GetBlocks, GetFavorites, GetFollowers, GetFriends, GetHomeTimeline, GetMentions, GetMessages, GetRetweetedBy, GetRetweets, GetRetweetsOfMe, GetSentMessages, GetStatus, GetUser, GetUserTimeline, Retweet, SendDirectMessage, UnBlockUser, UnFollowUser, Update, xAuth

SQL# – Full Version

Twitter: SearchTweets

For more info about SQL# and perhaps to download the free version of SQL#, go to the website SQLSharp.com


Setup your Twitter Application:
A prerequisite is to have a Twitter Application already setup and connected to your Twitter-account. You’ll need to have the ‘Consumer key‘, ‘Consumer secret‘, ‘Access token‘ and ‘Access token secret‘.

Here’s HowTo to setup Twitter, the Twitter application and fetch the required keys:
Documentation: Twitter Setup
How to setup a Twitter Application (API)

So I’ve setup my Twitter Application called ‘PowerPivotBook‘ after the self-study course I wrote earlier:
dev-twitter-com-application-settings


Install SQL# (by executing the provided *.sql file):
To install SQL#, just open the *.sql-file in SQL Server Management Studio (SSMS, or SSDT) and execute it:

Install-SQLSharp-Succes


Set Security to External Access first
After installing SQL#, you’ll first need to set the security of Twitterizer to have ‘external access’. Otherwise you’ll be getting this error:
SetSecurity-first

Execute this SQL-statement to set the security to ‘external access’:
EXEC SQL#.SQLsharp_SetSecurity 2, 'SQL#.Twitterizer'

The result:
SetSecurity-done


Twitter Update functionality (send a Tweet via SSMS):
It’s time to send out a test Tweet and let the world know we’re alive πŸ™‚


DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT SQL#.Twitter_Update(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'TweetTest - '+CAST(GETDATE() AS VARCHAR(100)), null, null, null)

Send-TestTweet

The result:
PowerPivotBook

TestTweet-successfull


SQL#.Twitter_SearchTweets() (TVF)
Now it’s time to use the Twitter Search functionality!

DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'PowerPivot', NULL)

The results:
tweet-search-results

Optional parameters
You’re able to minimize your search area by supplying optional parameters, like for example geo-location (geocode), language (lang) or max number of tweets (count):


DECLARE @Params SQL#.Type_HashTable
SET @Params = ''
SET @Params = @Params.AddItem('count', '100') --Max = 100
SET @Params = @Params.AddItem('lang', 'NL') --LanguageCode (2 letters, ISO2)
SET @Params = @Params.AddItem('geocode','52.3708305359,4.9040098190,50km') --Geocode for 'Amsterdam, NL'

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'weer', @Params)

Twitter-search-optional-parameters


SQL#.Twitter_SearchTweets() (TVF) Return Table specs:

[StatusID] [bigint] NULL,
[Created] [datetime] NULL,
[InReplyToStatusID] [bigint] NULL,
[InReplyToUserID] [int] NULL,
[IsFavorited] [bit] NULL,
[IsTruncated] [bit] NULL,
[Source] [nvarchar](200) NULL,
[StatusText] [nvarchar](200) NULL,
[RecipientID] [int] NULL,
[TimeZone] [nvarchar](100) NULL,
[ScreenName] [nvarchar](100) NULL,
[UserName] [nvarchar](100) NULL,
[UserID] [int] NULL,
[Location] [nvarchar](100) NULL,
[PlaceID] [nvarchar](50) NULL,
[PlaceName] [nvarchar](500) NULL,
[PlaceFullName] [nvarchar](500) NULL,
[PlaceType] [nvarchar](500) NULL,
[PlaceCountry] [nvarchar](500) NULL,
[PlaceLatitude] [float] NULL,
[PlaceLongitude] [float] NULL,
[RateLimit] [int] NULL,
[RateLimitRemaining] [int] NULL,
[RateLimitReset] [datetime] NULL

You’ll be able to fetch the results and feed it into your ETL-process. Social BI is just a few steps away πŸ™‚


Conclusion:
SQL# is a great addition to your cookbook and/or standard functionalities of SQL Server.

Free Version
If you would like to build a TweetBot that tweets automated updates, like for example job vacancies or weather updates, the Free Version of SQL# is a great toolkit. It’s free and easy to install, but as a best practice for security or practical reasons, create a dedicated (new) database for it or even a different SQL Server instance.

Full Version
Today’s BI is all about gathering social data, an easy way to add twitter search data to your BI-stack is by using the SQL# Full Version. Of course an experienced .NET developer can easily set things up with the Twitterizer-kit, but keep in mind that Twitter makes changes to its API and that requires maintenance. Nevertheless, for US$ 200,- (about 2~3 times an average hourly rate) you’ll be getting a lot more functionalities.






How to log all SQL Server and SQL Agent starts

SQL Server

To log all SQL Server starts, simply execute the T-SQL code provided below to create a new table and Stored Procedure (SP) to log this event:

USE LoggingDB
GO
CREATE TABLE dbo.SQLServerStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_ServerStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLServerStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLServerStartTimes DEFAULT VALUES
END
GO

To trigger/execute this SP once SQL Server starts, use this statement (more on MSDN about this built-in Stored Procedure)
EXEC SP_PROCOPTION sp_LogSQLServerStart, 'STARTUP', 'ON'
GO

Result:

SQL Agent

For SQL Agent starts, simply execute the T-SQL code provided below to create a new table and SP to log this event:


USE LoggingDB
GO
CREATE TABLE dbo.SQLAgentStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_SQLAgentStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLAgentStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLAgentStartTimes DEFAULT VALUES
END

Now, since all coding is done…we still need to setup a new SQL Agent job to execute this SP once SQL Agent starts.
Create a new job in SQL Agent and click on Schedules, select

Result:

Special thanks to Armando Prato for the inspiration!

How To: Generate a script to drop all Indexes

Well…here it is!
I know some of you are thinking, why would you ever do that?
In my case, I had to upload the entire database (about 40GB) to an external FTP-server.
It saved me a few GB’s to drop the indexes first and send them a SQL-script to (re-)create them πŸ˜‰

SELECT
'DROP INDEX ' + SS.name + '.' + SO.name + '.' + '[' + SI.name + ']'
FROM sys.objects as SO
INNER JOIN sys.schemas SS
ON SO.schema_id = SS.schema_id
INNER JOIN sys.indexes as SI
ON SO.Object_Id = SI.Object_Id
WHERE LEFT(si.Name,2) IN ('IX') --'IX' is the used naming prefix.
ORDER BY SO.Name , SI.name

SSIS / SQLCMD: Delete old TXT files

An easy way to delete old TXT files on your server is to use a SQL Agent Job and can be used with my previous GenerateScripts SP’s.

For this, I wrote a SP that uses the SQLCMD utility of SQL Server instead of SSIS because it’s lean and easy to implement.

A simple folder lookup by using SQLCMD:

I wrote to different Stored Procedures:
– Delete TXT files based on the file’s filename (for example:’_yyyymmdd‘)
– Delete TXT files based on the file’s creation/modification date

Download both SP’s here:
sp_DeleteOldTXTFiles

Stored Procedure: Automatically ‘Generate Scripts’ for local or linked server

Normally you would have done this manually:

Time for a change!
Schedule it in SQL Agent as a Stored Procedure and export it to a TXT-file.

SP GenerateScripts on LocalServer

On execution:

To export it to a TXT-file:
SQL Agent Job – ExportDatasetToTXT

Make sure that xp_cmdshell is enabled:

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Automated query performance test

Just got back from San JosΓ© and San Fransisco!
Went there for two weeks to work on my project for LumiLEDs (Philips Lightning).

A great view on the Golden Gate Bridge!

 
I did something new: needed to test my new Stored Procedures (SP’s) on performance and make an comparison between the ‘new’ and the ‘old’ queries.
For this blog post I rewrote my original code to use AdventureWorksLT2008R2, but it’s easy to rewrite it for AdventureWorksLT.

Like a lot of things you start with an idea:

The basics:
Test two scenarios (S1 – old vs. S2 – new) on performance with a common set of ID’s (TestIDs table) and log the results (TestResults table). Each scenario has three queries/SP’s (Q1 to Q3) with the same resultset, but in this example they also share a common query.

The objects:

 
For this test I made two tables:
TestIDs, which contains a common set of IDs to test:

TestResults, well….for the results πŸ™‚

 
Each scenario has three queries (Q1 to Q3) to test, so in total I’ve made six SP’s.
For each scenario I made a SP to run the individual SP’s with a cursor called ‘sp_TestScript_S#‘.

 
How to start:
Execute the generated script to create all the objects:
Test_scripts

Don’t forget to install/attach AdventureWorksLT(2008R2).

Execute the SP called: dbo.sp_Populate_tbl_TestIDs
The table TestIDs should be filled with ID’s by now.

Now you need to execute both SP’s (‘sp_TestScript_S#‘) at the same time.
You may want to use SQL Agent Jobs and schedule them.

 
The results:
As soon as the SQL Agent Jobs are finished, have a look at the results.
Execute the SP called dbo.sp_TestResults and analyse the results:

For a more detailed overview execute the SP called dbo.sp_TestResult_Details.
In the preview below, I zoomed in on ‘sp_S2_Q3_ProductModelID’:

 
Conclusion:
In my development environment I first tested my new queries and took the ID’s with the longest processing time or the highest result count.
 
You may want to combine:
1000 x Max(ProcessTime)
+
1000 x Max(ResultCount)

Next: use these ID’s to fill up the TestIDs table (sort of a worst case scenario) and run both SQL Agent Jobs (S1 and S2) simultaneously.
 
Have fun with testing! πŸ˜€

Going independent as a freelancer/contractor!

Last month I made the decision to start working as a freelancer/contractor starting from the 1th of April 2011!
I’m very excited!

Launched my own company called BumbleBI IT (Dutch).

Drop all table constraints per (x)type

I found an inspiring stored procedure and rewrote it to drop all (kind of) contraints.

sp_drop_constraints will drop all constraints on the specified table, including CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE, and DEFAULT constraints. Compile it in your master database and use it from any user database.

The code:

Download it here: here.

This SP uses my MultiValue function (included in the code above).

 
Here’s a list of all the possible values for the xtype column in the sysobjects table of a SQL Server database:

AF = Aggregate function (CLR)
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = In-lined table-function
IT = Internal table
P = Stored procedure
PC = Assembly (CLR) stored-procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = Table function
TR = SQL DML Trigger
TT = Table type
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

 
How to use:

NOTE: Don’t forget to generate a script to recreate them πŸ˜‰

Recursive Stored Procedure query

I stumbled upon a great stored procedure for Recursive Queries (here), but I wanted to recreate it for AdventureWorks.

Note: This one only works on the previous AdventureWorks database (not 2008R2, the Employee table has changed). Soon I’ll be posting a modified version of this based upon the AdventureWorks2008R2 database.

Basicly the Employee-table is self referencing table with levels (also known as ‘Tree Walking‘) has an EmployeeId and ManagerId I’m going to use.

The code:

How to execute:

And ofcourse, the results are shown here:

A really nice thing about this SP is that it uses a parameter Root, which can be used to ‘climb’ the tree. For example, if I set @Root to be 12:

Want the SQL sourcecode? Click here