Load data to the parallel datawarehouse (PDW) using the PDW adapter from Microsoft. in this post I have mentioned the steps involved and the common issue that is faced by developers using PDW destination.
The problems an _ (underscore) created in a SSIS Script component
it is common to create variable / field name with an _ (underscore) like my_first_name. I had neverseen this causing Programmatic errors till I used it in Script component within SSIS package. watch out for the pitfall, if you ever create an output column with an underscore using script component in SSIS. you can fix this code by
Working with XML files without a defined Schema or Multiple namespace using SSIS
The XSDs generated was unable to read the data correctly, as the XML had multiple namespaces ( with same name to make it worse). However, as usual, C# came to the rescue. used Script component (as source) to read the data from XML.
SHA 256 cryptography Algorithm to encrypt the data using C# and SQL server
SHA 256 cryptography Algorithm to encrypt the data using C# and SQL server. Hashbytes in SQL server and ComputeHash in C#.
Creating .csv or .txt file using batch (.bat) commands
command prompt or .bat code to create a .csv or text file from SQL query. You can either use the query directly in the batch file or use another file with SQL query as the source
Transact SQL code to identify queries with highest I/O in SQL server
SQL code to identify the queries and procedures consuming most I/O and with bad performance
Transact SQL code to identify longest running queries in SQL server
A short and quick SQL script to get the longest running queries and the query plan from SQL server for any specific database
Transact SQL ( T-SQL) to get all indexes in a database of SQL Server
Below query can be used to get all indexes including heaps from an SQL server database. “IndexKeyOrder” specifies the order…
What are SQL Query Hints?
SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where…
SSIS : Using and Configuring For Loop Container
What is For Loop Container? For loop defines a repeating control flow. It has similar behavior as the “FOR” loop…
Advantages and differences between CHAR and VARCHAR datatypes in SQL Server
Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages…
Compound Operators in SQL Server
There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t…
Understanding MERGE Transact SQL Statement
By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps…
SSIS : Sequence Container
Sequence containers is used to group the package into multiple separate control flows, each containing one or more tasks and…
Creating Excel Files (.xls) dynamically from SSIS
In this blog, I will walk you through the creation of excel files ( xls / Excel 2003) dynamically through…
The string function SOUNDEX() and its purpose in SQL.
The Soundex code was developed to help negate the effects of all the spelling variations that can occur for similar…
Executing the script in batch file using “sqlcmd” command
often create batch files to execute sql scripts ( specially SPs) and then schedule it using SQL agent job/Task Scheduler.…
Changing SQL database to single user mode and back to Multi User mode
It is often necessary to change the database to single user mode, especially if you are a DBA. A simple…
Remove Not Null Constraint on a column using Alter Table statement
Many times , it may be necessary to remove Not Null constraint used for a column in a table. It…
SQL Interview Questions – Basics 2
What are the advantages and disadvantages of Surrogate Key ? Pros: Business Logic is not in the keys. Small 4-byte…
Search for a pattern in sql server using patindex() and Charindex()
Although these functions are not commonly used, these are really powerful in searching a pattern and returning the location of…
Reindex the database with high fragmentation using “DBCC DBREINDEX”
DBCC is one of the most commonly used commands by DBAs across the globe. Today I will introduce a simple…
SSIS Error : Resolve transformation errors in 64-bit version of SSIS in debug mode
In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will…
SQL Interview Questions – Basics 1
What is RDBMS (Relational database management system)? RDBMS is a database management system (DBMS) that is based on the relational…
what does NULLIF() do ? how is it different from ISNULL()?
NULLIF( ) : Returns a null value if the two expressions specified in the function are equal. The syntax used…
Set Nocount On – Hide the number of records affected by a query
Often it might be necessary to hide the number of records affected by a query. It can be a simple…
SSIS – Skipping rows and columns in Excel
Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B.…
Recompile all Stored Procedures in a database
Recompiling an object is advantageous when ‘indexes or other changes that affect statistics are made to the database, compiled stored…
Script to List Linked Servers
In a large server , it is very common to create high number of linked server connections. it might be…
GUID (Globally Unique Identifier) in SQL Server
The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a…
Differnce between inner join, left / right outer join and full outer join in sql server
Many have asked me this in the past. so what exactly is the difference between different type of joins? lets…
So what do we call SQL Server 2011 ?
We had been sticking onto the mountains names from a very long time for SQL server. The proposed release of…
Excel and SSIS 64-bit Connections
I stumbled upon a new error yesterday. I developed a simple and straight forward SSIS package in my machine. It…
TeamViewer – Remote Access and Support over the Internet
TeamViewer connects to any PC or server around the world within a few seconds. You can remote control your partner’s…
Missing Folder Option
There are plenty of viruses which enable the NoFolderOptions Policy in the registry, and in addition, hides file extensions for…
Custom Icon for Your Pendrives
The default look of Removable drives like USB pen drives, Memory cards for phones and cameras can be changed using…
Get WeekNumber in SQL server using datepart() function
Many applications and organizations use the week number of a date for some sort of identification, classification, or formatting purpose.…
Show Hidden Files and Folders Missing
If your System is infected with this worm then it will not allow you to view the Hidden files. To…
FAQ: what is the purpose of turning on the service”SQL Server Integration Services”?
This is a very common question asked to me by many developers. As a fresh developer few years ago, even I…
Compound Operators in SQL Server 2008
The compound operators are supported by most of the programming languages available today in the market. However this feature was…
No Harddisk found error
To bypass this error follow the steps below 1. Disconnect the ribbon cable to harddisk. 2. Insert WinXP CD and…
Remove Autorun worm
This is one common problem in most of the Systems. Double click any drive will not open it if your…
Convert Fat To Ntfs Without Data Loss
You can convert FAT or FAT32 volumes to NTFS with this command. To convert a volume to NTFS from the…
Add wallpaper to a folder in XP
To add wallpaper to a folder in XP, follow the steps 1. Open the folder. 2. Choose Tools, Folder Options,…
Renaming MS SQL Server database
Renaming a database is not done very often. However, I have done it myself many times. this can be done…
Script to drop all connections to a Database
There are instances when we may have to drop all the connections to a database for example to rename a…
SQL Error: “Cannot execute as the database principal because the principal “sec_user” does not exist, this type of principal cannot be impersonated, or you do not have permission.”
You might have come across the error as highlighted below. Cannot execute as the database principal because the principal “sec_user”…
Stop Checkdisk on Startup
Tired of seeing this screen on every Startup , Well this method allows you to get rid of this problem.…
Laptop as wifi HotSpot
This method will help you to quickly turn your laptop into a wireless HotSpot without any extra software.This allows other…
Use single “INSERT” statement for multiple value inserts in SQL server 2008
In Sql Server 2005 or earlier , to insert each record into a table , you had to isuue one…
Concatenate multiple .sql files into a single file.
Often we receive the scripts from our customers in multiple files. Sometimes as many as 70 to 80 files. Although I…
Functions to get the current system datetime in SQL Server
There are different ways of fetching the current time from the sql server. Sql server 2005 provides 3 different functions…
What is @@IDENTITY ?
Very often, it is important to check the last identity value inserted into a table. This can be easily done…
Get the consolidated size of all tables in a database.
The first thing that goes through your mind might be ” why cant we SP_SPACEUSED instead ? “. You are…
Check for the existence of a string in all procs and functions within a database.
Many developers have asked me, if there is any way to check the existence of a string or text with…
Using string function DIFFERENCE()
The function “Difference” is used to check the similarity between two string based on its sound. This function internally uses the…
Change the system error messages with custom messages
This post discusses about ways to change the system messages with custom messages . This is not widely used, as it…
An easier way to translate query stats and plan/SQL handle information
Usually we capture the SQl/plan handles for the query stats or from cached plan DMVs and supply these as the…
Calculating the value of proposed indexes and analysing the impact.
Take this as a continuation of the post for identifying columns on which indexes are missing The most difficult decision…
Some SQL best practices
I had been seeing lot of SQL best practices posts on the web. I found some points missing in many of these…
How to decide between “Rebuild vs Re-organize” for an index on a table.
How do you know whether to rebuild or to reorganize index by using ALTER INDEX…REORGANIZE or ALTER INDEX…REBUILD against user…
Deleting the file in use / locked files using “Unlocker”
Ever had such an annoying message given by Windows? It has many other flavors: Cannot delete file: Access is denied…
Difference between Datalength() and Len() functions in SQL server
Often the functions Datalength() and Len() lead to confusion, Both looks the same and returns length. But “Length of what…
Changing Identity Seed value of a SQL server table using DBCC
Whew !! this is my third post in two days on identity fields of SQL server. The more I investigate,…
Get the latest identity value inserted into a table / Identity value inserted in the current scope .
It is often a requirement to fetch the latest/ current identity value used in a specific table . There are…
Insert values into IDENTITY column of SQL server table / Disable IDENTITY Column in SQL server 2005
Identity columns automatically inserts value for each record inserted into the table . These columns are commonly used as primary…
Get the owner of the database in SQL server
Sometimes it is important to get the owner of each database in a server. You can do this by checking…
Executing dynamic queries with more than 4000 characters using variables
Usually problem occurs when we are trying to build a query dynamically and if the length exceeds 4000 characters (…
Connect to SQL Server 2008 from SSMS for 2005
Many people have asked me this in the past. I used the SP3 released for SQL Server 2005. Install this…
Get the list of dependent objects for any given object using query.
At times , we may have to find the objects on which a particular object is dependent on. The views…
“ALL” : the transact SQL statement
The T-SQL statement “All” is used very rarely by developers. Usually, we don’t get requirements where “All” can be used.…
Get the last database backup time for any database.
As a DBA it is very important to know, when the database was last backed up. It is also important…
Identify the columns on which the indexes needs to be created / missing indexes in a table.
Most often identifying the columns for which the indexes needs to be created is a difficult job. Often we land…
Maximum Length of login/User name and password in SQL Server 2005
One of my friend asked me this question. although I spontaneously replied as “128 characters”, I wasn’t sure of it. …
Restoring differential backup in SQL server
I have seen people struggling to restore differential back up. People tend to treat differential backup same as full backup.…
Save storage disk space by replacing “space” with “horizontal tabs”
This was a simple experiment that I performed to find, how much difference it makes by replacing some of the…
Tracking the modifications of objects in a database
As a DBA or a Database developer, it is important to track the modifications that happens on objects in a…
Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”
sometimes, you may need to copy some/ all objects from one database to another. well this can easily done in…
Adding Audit information to you dataset using SSIS.
Adding audit information to your dataset such as user/ machine that modified the data, package that inserted data into your…
Database Compatibility Errors : ” If it is intended as a parameter to a table-valued function,ensure that your database compatibility mode is set to 90.”
You might have encounterd the errors related to compatibility while running DMVs and functions on sql server databases. The error…
Insert “Space”,”new line”,”tab” characters into a field as value
There are times when you need to insert some special characters such as “newline”,”tab” etc as values into a field.…
ASCII character set table
ASCII character set and values in decimal,hexadecimal,oct Special Characters Char Oct Dec Hex Remarks NUL 0 0 0 Null character…
Perform an action on all tables in a database using ‘sp_msforeachtable’
You might have faced a challenge to perform some operation on all the tables in a database. For example truncate…
Collation — Another update for Case Insensitive collations.
Usually simple queries on a database using Case insensitive collation runs without any issues. In case insensitive collation select *…
Changing the collation of a database
You can change the collation by using SSMS. Right click on database, click on properties. click on options on the…
Logging using SQL provider in SSIS 2008 and SSIS 2005
I am starting this post with an assumption that you know to configure logging in SSIS 2005/2008 for SQL Server…
resolving collation issues in sql server
I had to write a query to fetch data from tables located in two different databases using a join. The…
Resolving Datetime datatype Issues during insert/select
The developers who work on sql servers, located across the globe, face various issues related to datetime formats used. Some…
Difference between IN and EXISTS operators in SQL
This is a commaon question that comes to each developers mind. Most of them considers IN and EXISTS as same…
Excluding MS shipped objects in a database.
Very often we might require to get the list of tables available in a database. Imagine I have a database…
Case insensitive comparison in SSIS 2008 LookUp transformation
Developers using SSIS 2005, were never satisfied with the lookup component . The lookup component in ssis 2005 didn’t support…
Converting multiple record into a single comma seperated value
My friend happend to ask me a question on “How to convert multiple records into a single delimited separated value?”.…
Configure DTC on Windows Server 2003/ Windows XP
For the transactions to run successfully across servers , we need to enable DTC ( distributed transaction coordinator). For example,…
Return a custom delimited result set
Developers very often require special character delimited query results, so that it can be used in other softwares and spread…
Executing SSIS Packages from command prompt / SSMS
“Dtexec” utility can be used to run the SSIS packages from the command prompt. Using dtexec utility you can load…
Awesome SQL 