A quick note on how select @@version helps me while I’m T-SQL’ing:

Standard

As a part of developing ETL packages, sometimes, I’ve to write T-SQL queries to pull data from SQL server source systems. But before I start doing that, it’s always good to know the version/edition of the source system. Why? because it can determine whether a TSQL operators are available for me to use or not. Case in point, I had a requirements where I could have written a query that uses Pivot & UnPivot operators. So I write a query & it doesn’t work! I spent about 5 minutes trying to debug the code. The code seems OK to me. So I thought of checking the “version”. And there you go, client’s source system was running SQL Server 2000. So that meant, I couldn’t use the Pivot & UnPivot operators.

Select Version SQL serverThis was my quick note on how select @@version helps me while I’m TSQL’ing. Next time, I’ll probably check this first, before writing the code. That could save me few minutes 🙂

SQL server reporting services: Expression to show current month name & year

Standard

Here’s a SSRS expressions code that I’ve using lately to show current month name & year in SSRS reports:

[code]

="REPORT NAME"&" – "&MonthName(Month(Today()))&"’"&Year(Today())[/code]

This is a nice little code that you can add to your expressions which will show current name & year. Small little things that you can do to make your business users happy! I hope this helps someone out there.

How to solve common Data Quality Problems using Data Quality Services? (Part 1)

Standard

New Journal Article: First article of 2, where you will be able to see how you can use SQL Server 2012’s DQS to solve common data quality problems. http://bit.ly/172Kh5L

Topics covered:
– Data standardization
– Identifying and correcting unrealistic or invalid values
– Validation and correcting records using Regular Expressions

Read Here: How to solve common Data Quality Problems using Data Quality Services (Part 1)

Beginner’s Guide: Sentiment Analysis using Python on Windows

Standard

This is beginner’s guide to sentiment analysis using Python NLTK on windows. We’ll start w/ installing Python and NLTK and then see how to perform sentiment analysis.

Step 1: Install Python & NLTK

I followed the steps listed on http://nltk.org/install.html

1. Search for python 2.7.3 for windows and install it.

2. Search for Python setup Tools for Windows and install it.

3. Install PIP (for win 64 bit), NLTK and PyYAML.

4. Test installation: Start>All Programs>Python27>IDLE, then type import nltk

Now,

5. Also type:

>>> Import random

6. And also install movie_reviews corpus by typing:

>>>nltk.download()

in the new window that opens, install the movie_reviews corpus.

python nltk download data

Step 2: Sentiment Analysis

I followed the code explained in the NLTK book in the section “document classification” in ch 6 learning to classify text. Here is the section: http://nltk.org/book/ch06.html#document-classification

Using the code I was able to run the Naive Bayes Classifier to categorize text:

python sentiment analysis

Conclusion:

In this post, we learned how to perform sentiment analysis using Python on windwos platform. NLTK supports classifiers other than Naive Bayes, and also there are resources that will help  you increase the accuracy of the classifier. And I hope that this post acts as a starting guide for you!

Related articles

How to Install Microsoft .Net SDK for Hadoop?

Standard

There are two main steps:

1. Installing Nuget Package manager if you haven’t already.

2. Installing Microsoft .Net SDK for Hadoop

Installing Nuget Package manager

1) Open Visual Studio

2) Tools Menu > Extensions Manager > Search online gallery > Nuget

3) Downloaded and Installed Nuget:

Nuget Package Manager Extensions Manager

4. Restarted Visual Studio

Installing Microsoft .NET SDK for Hadoop

1. Tools menu > Library Package Manager > Package Manager console

2. Installed Map/Reduce, Linq to Hive and WebHDFS component by running following commands in the package manager prompt:

Example for:

install-package Microsoft.Hadoop.MapReduce -pre

Nuget Microsoft SDK for Hadoop install mapreduce

Conclusion:

In this post, we saw how to install Microsoft .NET SDK for Hadoop.

Resource:

Continue learning: Programming MapReduce Jobs with HDInsight Server for Windows

How to avoid Men in Middle attack when you try establishing a new connection to SQL Azure via SSMS

Standard

Fact: All connections with SQL Azure are SSL encrypted. No exception.

Then what am I talking about? Why do I need to worry about Men in Middle attack now?

Turns out there’s a way, Men in Middle attack can happen – Not on your established connection But when the client first tries to establish a new connection. And in this blog post, we are going to see how to avoid Men in Middle attack when you first try establishing a new connection via SSMS.

BTW: I have not researched on how to do it from developers perspective, but if any developer is reading this and has figured it out – it would be great if you can share it with us via any communication means of your choice. (Thanks a lot – if you do so).

[Update: See Bottom of the post]

Any-who. Back to SSMS.

All you got to do to avoid this improbable situation to happen is just check the “Encrypt connection”in the connection property when you try establishing a *new* connection to SQL Azure.

That’s it.

if the client requests encryption from the beginning then our connection is not susceptible to Men in Middle attach while the client is negotiating with the server for encryption.

Categorize under “Best practice” if  you wish to.

 

Update: Developers, When you connect to SQL Azure using ADO.Net – please do not forget to set 1. Encrypt = TRUE and 2. TrustServerCertificate = False  to avoid man in middle attack.

 

And Thanks to Herve Roggero for his response on my (poorly framed) question on StackOverflow. I am glad, he understood what I meant!  Thanks sir 🙂

My article on “Tuning SQL Azure databases – Part 2/2” got published in SolidQ Journal’s september edition

Standard

Part 2/2 of “Tuning SQL Azure Databases” got published. The aim of the series was to show you the options that are available to tune a SQL Azure database. In part two, I  discuss on how to use information made available through dynamic management views (DMV’s) available in SQL Azure; while the first part focused on examining execution plans and tuning the database based on that information. Here are the links to download the magazine:

Part 2: http://www.solidq.com/sqj/Pages/2011-September-Issue/The-SolidQ-Journal-September-2011.aspx

Part 1: http://www.solidq.com/sqj/Pages/2011-July-Issue/The-SolidQ-Journal-July-2011.aspx

Part 2 of “Getting started with SQL Azure” is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In this blog post, i have discussed:

1) How to sign up for a Free trial of windows azure (to play with SQL Azure)!

2) How to create your very first SQL Azure database (and a table too!)

3) How to connect to SQL Azure server Via SQL server Management studio.

Read more: Getting started with SQL Azure – Part 2 << Paras Doshi

my “Migrating Access Database to SQL Azure database using SSMA” on SolidQ blog is live!

Standard

I have written a blog post on my SolidQ blog that discusses in a step by step fashion on how to migrate Access database to SQL Azure using SQL server Migration assistant.

to read article, please go to: Migrating Access Database to SQL Azure database using SSMA

 

Part 1 of the “Getting started with SQL Azure” is live!

Standard

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In this blog post, we are going to begin our journey of learning SQL Azure. We will first see where SQL Azure fits in the Windows Azure platform offerings. We will then discuss about ways in which we can define SQL Azure. Then we will explore advantages of SQL Azure or in other words see why you should consider this cloud based database as a service as a part of your technology solution. So let’s get started!

read more: http://beyondrelational.com/blogs/parasdoshi/archive/2011/09/12/getting-started-with-sql-azure-part-1-lt-lt-paras-doshi.aspx