Search Country & Location Information based on IP Address

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

Once I ran into the situation where I have to find out the CityCountry name & location (Longitude, Longitude) based on the IP address. This was not difficult but rather not straight-forward as IP addresses are stored into database table as strings which is very obvious because there are no standard data type which supports IP address as value.

Anyways, so in my database table there are 2 important columns regarding IP address details i.e. Start_IP_Address and End_IP_Address. And mind-well, a range of IP addresses belongs to a country. Currently my database table looks like following: (Below screenshot shows only 5 records)

Continue reading →

GodMode in Windows 7

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

If you have a Windows 7, then you can enable so-called “GodMode” to get access to most of the controls of operating system & other components.

You simply have to create a folder name with following words.
“GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}”
(Please exclude ” – double quotes while naming it). Ta-Da.

The folder icon will change to control panel icon and you will see the big list of items where you can easily search many controlling options just grouping by “Application” in details view. You can also collapse or expand all groups by right-click & selecting appropriate options.

Though it seems like a one-place-control-center for operating system components, but there are few options which you may not find in the list. Please check the control panel for more details.

Continue reading →

“Specify Values for Template Parameters” in SQL Server

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

SQL Server Management Studio provides a dialog box named “Specify Values for Template Parameters” for filling values for the template you have used. This is very handy if you always like interface based interactions with components.

E.g. When you choose any template from Template Explorer, you will see template with lots of place-holders where you can specify your values. In following example I am going to show you the template of basic stored procedure.

Continue reading →

Useful information about available Database Snapshots in SQL Server

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

Once I had to find out few details of available database snapshots in SQL Server. I wrote following script which helped me to find out following:

  • how many database-snapshots are available & live
  • whats the size1 of database snapshot as well as the allotted size2 for database snapshot
  • What collation is assigned to it
  • where it is physically located in my hard-drive

1. Size of database snapshot = Actual Size in query results
2. Size allotted for database snapshot = File Size in query results

Continue reading →

FileTables in SQL Server 2012

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

FileTables feature introduced in SQL Server 2012. This feature is built on the top of FileStream feature of SQL Server 2012. I published the introduction of FileStream in my previous post.

FileTables allows us to store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

A FileTable is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes.

Continue reading →

FileStrems in SQL Server 2012 – Introduction

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,

I would like to start this post with a small story. I believe (at least) at one point of time, we might have faced problem searching information from our CD/DVD collection. We used to write information on CDs but that was not enough to cover all the contents of that CD. And when we need something particular we used to get confused, whether that content is in CD 1 or CD 2. I faced this many a times during my college-days. At that time one of my friends wrote a software that allows us to create a catalog for CD/DVD and store into database table. And that table name used to be written on that CD/DVD. So whenever we need to search something, we use that software and it will return us the CD name. So by that we minimized the efforts of searching and leverage the search functionality of software. I know there are lots of constraints and dependencies involved for this system to work beautifully, but that was a good concept.

In SQL Server Denali now SQL Server 2012, you might have heard the feature of FileStream which it pretty much like my story. FileStream allows us to manage unstructured data like documents, images etc. on file system. So applications which needs to have access to these resources (files, images etc.) can leverage facility of rich streaming APIs and performance of file system but at the same time maintain transactional consistencies between unstructured data and their management systems which is SQL Server.

Continue reading →

Enhanced “Generate Scripts” dialog box in SQL Server 2012 RC0

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,
I have been monitoring the “Generate Scripts…” functionality of SQL Server Database.

One thing bothers me for last few years or since SQL Server 2005 is that once I right-click on any database -> Tasks -> Generate Scripts, I would expect to generate scripts only for that database which I have selected. But rather Generate Scripts dialog box list down all the available databases using SMO queries and allows me to choose any other databases at that time.

I understand their concerned about the facility to choose other database and go ahead with the operation. But the problem is when you have lots of database on SQL Server, it takes time for this listing. And as a developer it’s a pain in head 🙂

Time by time Microsoft enhanced this feature. E.g. in SQL Server 2005 edition, you cannot script the data, but in SQL Server 2008 onwards, you can scripts data as well so it will create insert scripts for you which is very handy for any fresh machine deployment. Thanks for that.

Continue reading →

The task “SCR Get Task Start time” cannot run on this edition of Integration Services. It requires a higher level edition. – SSIS 2005 – DTEXEC Execution Result

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,
I was testing an SSIS package (Developed using SSIS 2005 version) using DTEXEC command line tool. But it got crashed and showed me long list of exceptions/errors occurred during the “TRY” of execution. (I used the word TRY here because I have logging enabled in my SSIS package, but it did not log anything, that means it did not even start.) And at the end I see this message – “DTExec: The package execution returned DTSER_FAILURE (1)”. That means package was failed.

The main error description was “The task “SCR Get Task Start time” cannot run on this edition of Integration Services. It requires a higher level edition.”

Continue reading →

The package failed to load due to error 0xC0010014 – SSIS 2005 Package – Corrupted

Khilitchandra's Business Intelligence Blog Posted on by

Hi All,
I had to work on my colleague’s SSIS solution which was developed using SQL Server Integration Services 2005 SP2 version. I could successfully open the SSIS solution but I could not load the package and I got following message.

Continue reading →