Compare SQL code files with Notepad++ (Compare plugin – free)

I know that VS2010 has a very nice compare function, but for anyone who doesn’t have VS2010 installed:

Notepad++ (the famous Notepad 2.0 application and free to download) has a free plugin called ‘Compare’ which enables you to easily compare all sorts of text-based files. For example SQL, XML, anything you’re able to open with NotePad++!

How does is look like?

Notepad-plus-compare-plugin

How to get it and install it?

#1 Download NotePad++:
http://notepad-plus-plus.org/download/v6.3.3.html

#2 Download the NotePad++ Compare plugin:
http://sourceforge.net/projects/npp-compare/

#3 Unzip the plugin’s DLL-file (ComparePlugin.dll) and copy it to:
[NotePad++ install folder]\plugins

#4 Start NotePad++ and verify that the Compare plugin is enabled under:
Plugins -> Compare

#5 Open two files, like shown here:

Notepad-plus-compare-plugin-how-to

#6 Start comparing!:
Go to Plugins -> Compare -> Compare (or use Alt+D)

Notepad-plus-compare-plugin


Advertisements

How to setup a SharePoint 2013 – BI Environment

Thanks to Rob Kerr and Kasper de Jonge:

SP2013 Deployment Crash Course
Rob Kerr just posted a fabulous 23-lesson video series deploying a full-up SharePoint 2013 BI environment from soup-to-nuts (SSRS-Integration, PowerPivot, PowerView, Kerberos Delegation, Excel Services, PerformancePoint, etc.).
Go watch it here: http://msbiacademy.com/?cat=451

SharePoint-2013-BI-Environment


Microsoft Business Intelligence at a Glance Poster

Decorating the office? You might want to download this Microsoft BI poster and send it to your local copy shop.

Provides an overview of Microsoft’s Business Intelligence technologies in Office, SQL Server, and BI services in Windows Azure.

The poster summarizes the benefits of Microsoft’s BI technologies and depicts them by layer: client (such as BI in Excel), the presentation subsystem (such as SharePoint 2013), information sources (such as Reporting Services), and data sources (such as relational databases). It focuses on the enterprise architect and IT implementer audience. It includes Office 2013 client and server, SQL Server 2012 with SP1, and BI services in Windows Azure.

More info.

MSBI-poster

Download the poster: click here

SSMS 2012 – Log File Viewer enhancements

Today is another SQL adventure 🙂
Just found out that they added a few functionalities/enhancements to SQL Server Management Studio 2012’s ‘Log File Viewer‘.

In 2008 R2, you were not able to directly open the ‘Job Properties‘ or even dive directly into the ‘Job Step Properties‘ of a SQL Job. Guess what…in 2012 this is indeed possible!

SSMS-2012-Log-File-Viewer-Enhancements

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.






Why not to use a FLOAT to represent a currency or other financial value

Many thanks to Berrie Roelofs

Query:
Why-not-to-use-floats-query

Results:
Why-not-to-use-floats-results

Just a small difference between the totals, but it’s there 🙂

More info on MSDN:

The decimal data type stores an exact representation of the number; there is no approximation of the stored value.

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

Download the T-SQL query:
Click here

Another example by Joseph Fluckiger can be found here.


The famous NULL comparisons (Three-valued logic – SQL Server)

Wim Kok send me these funny comparisons that involve a NULL-value:

Queries:
NULL-comparisons-query

Results:
NULL-comparisons-results

Code:

PRINT 'Comparison: NULL = NULL'
IF NULL = NULL
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

PRINT ''

PRINT 'Comparison: NULL != NULL'
IF NULL != NULL
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

PRINT ''

PRINT 'Comparison: NULL != 4'
IF NULL != 4
PRINT 'Answer: I agree :)'
ELSE
PRINT 'Answer: I disagree :|';

About
NULL as a value equals UNKNOWN in three-valued logic (3VL), more info about that:
http://en.wikipedia.org/wiki/Three-valued_logic

It appears that the earlier version of SQL Server had it wrong 🙂

SET ANSI_NULLS { ON | OFF }

Some of you may have seen existing code where a predicate such as Column = NULL is used, and does actually return rows. This is probably legacy code that has not been maintained in a long time (and if it isn’t, it has to be written by a legacy developer who has not maintained his knowledge in a long time).

Very early versions of SQL Server were released before agreement was reached in the ANSI committee on all features. As a result, some features were implemented in a different way than what the ANSI committee turned out to describe, facing Microsoft developers with the challenge to upgrade to ANSI compliant behaviour without breaking existing code.

Behaviour of NULL comparisons in predicates is one such example where the original version of SQL Server “got it wrong”. So when SQL Server was changed to return Unknown on a comparison with NULL, the SET ANSI_NULL OFF option was introduced to force the “old” behaviour that would return False on NULL vs. non-NULL comparison, and True on NULL vs. NULL comparison.

The SET ANSI_NULL option has been marked as deprecated in SQL Server 2005, so if you find code that still relies on this setting, you’d better change it ASAP. This would of course be very easy if you only had to look for “= NULL” and “ NULL”, and change them to “IS NULL” and “IS NOT NULL” – but unfortunately, expressions of the form “= expression” where expression can be NULL are also affected by the ANSI_NULL setting; these are much harder to identify or fix!

Click here for Hugo Kornelis‘s full blogpost and more info about NULL comparisons and their behaviors.

Want to know more about NULL? Click here.