SQL Server and MS BI With a Geek Touch

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

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…

Although these functions are not commonly used, these are really powerful in searching a pattern and returning the location of text/ string/ pattern. By understanding the functionality of both the string functions, we will be able to understand the difference as well.

PATINDEX()

The function searches for a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similar to the LIKE operator in any query.

Read more…