SQL Server and MS BI With a Geek Touch

Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages between these two?

The obvious difference that comes to our mind is related to the length. Yes! You are right. The varchar supports variable length and Char supports fixed length. This is the main advantage varchar datatype. for example,  let us consider varchar(10) and char(10) . To store “GEEKEPISODE”, both the datatypes will use the complete length allocated and to store “GEEK”, CHAR datatype will use the complete 10 characters  allocated, but VARCHAR will use only 4.

From the above examples it might seem that VARCHAR is better than CHAR all the time. But remember, CHAR was included as a separate datatype because it has certain advantages over VARCHAR.

VARCHAR uses additional space to store the length of the string stored in it and to mark the end of the string. Whereas, CHAR datatype doesn’t use this additional space.  This certainly is the advantage of CHAR datatype.

Summary:

Read more…

I often create batch files to execute sql scripts ( specially SPs) and then schedule it using SQL agent job/Task Scheduler. In this post , I will show you how to create a batch file with sql commands.

First of all, The command that is used to execute SQL queries from commnd prompt is “SQLCMD“. The general syntax of this command is available in the link. I will go with a simple one as below

sqlcmd –E –S Servername –Q Query -o “Output Path”

C:\Users\geekEpisodes>  sqlcmd -E -S “Local-SRV01″ -Q “Select top 10 * from MyTestDatabase..[ForecastHistory]” -o “C:\New Folder\Output.txt”

All the commands that you execute in the command prompt can be executed from batch/command files. to create a batch file with SQL commands follow the steps below

Read more…

The Soundex code was developed to help negate the effects of all the spelling variations that can occur for similar sounding names ( Smith, Smithe, Smythe, etc.).  This way, users can index records based on a Soundex code based on the sound  and “not” by how it is spelled.

The Soundex system is not infallible – the surnames Gough and Goff sound the same (Goff), but a different code is formed for these two names.  Soundex can however be used to a great extent to get a desired result ( atleast closer to a desired result) This is a commonly used algorithm by many researchers.

In SQL a string function is available for this and is usually comes handy.  The function is SOUNDEX().

It accepts a string (name ) as parameter and returns a 4 character code. This code is known as the soundex code of the name/string passed as parameter.

example:

Select Soundex(‘Smith’)   O/P: S580

Select Soundex(‘Smithe’)   O/P: S580

SELECT  SOUNDEX(‘GREEN’)   O/P: G652

Another use of this is against an user entered field where you need to look for a specific word, but the words might be mispelled( although it may not yield 100% result).

 Hello everyone. First of all, thanks for all your mails and comments. I was out of town and was very busy last couple of months . I could hardly get access machines last few months and could not post any contents.

 I am now back, and you can look forward for more posts and contents from me. Lately, I have also started on little bit of VBA for excel as part of my daily routine and work. I will endeavor to post some contents on that as well. And also watch out for more videos . Hope you will enjoy it.

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14^th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14^th Jan. To do this,

1. go to properties window of the excel connection manager
2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

“C:\\Taxonomy_”+ (MONTH( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) MONTH( GETUTCDATE()  ) DT_WSTR,2)MONTH( GETUTCDATE()  ))

+(DAY( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) DAY( GETUTCDATE()  ) DT_WSTR,2)DAY( GETUTCDATE()  ))

+(DT_WSTR,4)YEAR( GETUTCDATE()  ) +”.xls”

Also you need to use the settings as below for connection manager ( for .xls files)

Read more…

The video shows the dynamic functionality of SSIS packages. I have used the ETL to read through multiple excel files dynamicaaly using a single connection manager. a For each loop container is used to loop through each of these files and the data loaded into SQL database by dynamically changing the connection string for each excel file. You can also download high quality video by clicking here.

Part 1:

Part 2:

Please leave a comment with your suggestions and feedbacks.

This video takes you through the creation of SQL Server Integration Services (SSIS) project creation. The video will also demonstrate the usages execute sql tasks and will take you through various components and windows in SSIS UI and the purpose of each. you can also download high quality video from by clicking here

Let us know if you would like to watch some specific videos.

RAISERROR statement is used to return error messages to the business applications that executes SQL statements. The usual errors returned by the SQL server may not make much sense to the business applications users hence we overwrite it by using RAISERROR to display meaningful messages. The statement uses same format as a system error or warning messages generated by SQL server.

You can return a user-defined error message by using RAISERROR.  The general syntax is as below

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

ExamplesRAISERROR (‘Error raised in TRY block.’, — Message text.

Read more…

A TRY ….CATCH construct includes a TRY block followed by a CATCH block. The TRY block contains a set of transact SQL statements. If an error occurs in any statements of the TRY block, the control is passed to the CATCH block, which contains another set of SQL statements.

The CATCH block contains SQL statements that executes only during errors in TRY block. The general syntax of the TRY…CATCH block is as shown below

BEGIN TRY

                <SQL Statements…..>

                <SQL Statements…..>

END TRY

BEGIN CATCH

                <SQL Statements…..>

                <SQL Statements…..>

END CATCH

  Read more…

You can use the string functions to manipulate the string values in the result set. String functions are used with both char and nchar data types. The list below contains the set of string functions available in SQL Server 2005.

Read more…