Monday, November 2, 2015

TRY...CATCH usage in (T-SQL)

Try catch got same functionality as other programming languages like c++,c#,c..etc. Microsoft introduced try catch mechanism from SQL Server 2005. This functionality used for error handling in any programming language and same concept used in T-SQL scripts.Work flow of try catch can be explained through syntax


T-SQL Statements
Block of  T-SQL Statements
T-SQL Statements
Block of  T-SQL Statements

The above syntax shows that at  BEGIN TRY part will start execution of T-SQL statements and if there are no exceptions found in TRY block control directly passed to the END CATCH which is the end part of stored procedure or trigger. Check the below stored procedure for try catch example.

    SELECT 1/0
      ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;

In above stored procedure begin catch statement block contain error functions they will indicate which type of error we are facing in the code block after execution of stored procedure or trigger.

ERROR_NUMBER()  returns the error number.
ERROR_SEVERITY()   returns the error severity.
ERROR_STATE()  returns the error state number.
ERROR_LINE()  returns the error line number.
ERROR_PROCEDURE() returns the procedure or the trigger which causes error.
ERROR_MESSAGE()   returns the error message.

Result of above stored procedure

Few points to remember about try catch statements.

  1. TRY CATCH Statements must start with  BEGIN TRY and ends with END TRY 
  2. One or more T-SQL blocks can be specified into individual BEGIN TRY,BEGIN TRY blocks
  3. A try block must be followed immediately CATCH  block.
  4. Try block of one catch should not be in another try another statement block.
  5. Error severity more than 20 can causes connection closure to the database.Below 20 error functions can be handled by the try catch block
  6. Two types errors that are not handled by the try catch  1)syntax errors 2) Errors that occurs at statement level recompilation.

Friday, October 30, 2015

How to Import Excel data by using T-SQL(OPENROWSET and OPENDATASOURCE)

Importing data from ms-office excel is very common task in it industries. Here I will elaborate methods to import data from excel to the sql server database by using openrowset and opendatasource.Tool needed before you need to start is  Microsoft Access Connectivity Engine (ACE) 32Bit (or) 62Bit Based on your machine configuration. download it from below link

  1. Download and install above tool for reading excel file from sql server. And check whether installation was success or not, to check go to sql server management studio>expand server objects expand linked servers and go to providers. you will find "Microsoft.ace.oledb.12.0" if the installation was successfull.
  2. Double click on the provider and check the box allow in process and dynamic parameter 
  3. Run below Script to skip step 2 process. open your sql server in administrator mode
    Execute master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'dynamicparameters' , 1;
    GoExecute master.dbo.sp_msset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'allowinprocess' , 1;
     4.  Now pre-process is completed.
     5.  Go to sql server management studio
     6. Run Below Script enable remote queries
EXECUTE master.dbo.sp_configure 'show advanced options', 1;
reconfigure ;
EXECUTE master.dbo.sp_configure 'ad hoc distributed queries', 1 ;
    7.  Run this query and change excel path  with your excel
         ('Microsoft.ACE.OLEDB.12.0','Data Source D:\sample.xls;
         Extended Properties = Excel 12.0')...Sheet1$

          ('Microsoft.ACE.OLEDB.12.0','Data Source = <source file path(excel)>
         ;Extended Properties = Excel 12.0')...Sheet1$

    8. Now create one table to insert excel data into it. that's it done

create table sample  (name  varchar(50), id int)
insert into sample 
SELECT*FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source                    D:\sample.xls;Extended Properties = Excel 12.0')...Sheet1$

Wednesday, October 28, 2015

Naming conventions for SQL Server

Naming Conventions

Object Type
Primary Key Constraint
Foreign Key Constraint
Unique Key Constraint
Default Constraint
Check Constraint
User Defined Stored Procedures
User defined: "usp_" "usp_<Action>_<Object>"  System defined: "sp_"
User Defined Functions

Wednesday, October 21, 2015

How to change database/server collation

Collation option will create troubles when you are migrating database. To change collation run below script.

-- **************** BEGIN INPUT **********************
USE [YourDBName]

DECLARE @collation NVARCHAR(128)
-- enter you collation name below
SET @collation = N'SQL_Latin1_General_CP1_CI_AS'--Type of Collation you want change--
-- **************** END INPUT ************************

-- **************** BEGIN LOGIC **********************
DECLARE @SqlCollate VARCHAR(2048)

    SELECT 'ALTER TABLE [' + + ']
        ALTER COLUMN ['+ + '] ' + +
            WHEN 'text' THEN ' '
            WHEN 'ntext' THEN ' '
            ELSE '(' + RTRIM(
                    WHEN sys.columns.max_length = -1 THEN 'MAX'
                    WHEN sys.columns.max_length > 4000 THEN 'MAX'
                    ELSE CONVERT(CHAR,sys.columns.max_length)
                END) + ')'
        + ' COLLATE ' + @collation + CASE sys.columns.is_nullable WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END
        FROM sys.columns , sys.objects , sys.types
        WHERE sys.columns.object_id = sys.objects.object_id
            AND sys.objects.TYPE = 'U'
            AND sys.types.system_type_id = sys.columns.system_type_id
            AND sys.columns.collation_name IS NOT NULL
            AND sys.columns.collation_name <> @collation
            AND NOT ( sys.objects.NAME LIKE 'sys%' )
            AND NOT ( LIKE 'sys%' )

OPEN myCursor
FETCH NEXT FROM myCursor INTO @SqlCollate

    PRINT 'Executing: ' + @SqlCollate
        PRINT 'Done!' + CHAR(10)
        PRINT 'Error: ' + ERROR_MESSAGE() + CHAR(10)
    FETCH NEXT FROM myCursor INTO @SqlCollate

PRINT 'Finished!'
-- **************** END LOGIC **********************

After running above procedure, rebuild the index's on the tables 


USE AdventureWorks2012;
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee

Thursday, October 8, 2015

How to get Month/Day name in SSIS derived column Expression

Simply paste below expression 

For month name use this expression

simply we are extracting month from the getdate() sql function later we are assigning month name to it

(MONTH(getdate()) == 1 ? "Jan" : MONTH(getdate()) == 2 ? "Feb" : MONTH(getdate()) == 3 ? "Mar" : MONTH(getdate()) == 4 ? "Apr" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "Jun" : MONTH(getdate()) == 7 ? "Jul" : MONTH(getdate()) == 8 ? "Aug" : MONTH(getdate()) == 9 ? "Sep" : MONTH(getdate()) == 10 ? "Oct" : MONTH(getdate()) == 11 ? "Nov" : MONTH(getdate()) == 12? "Dec":"")

For day name use this expression

simply we are extracting date from the getdate() sql function later we are assigning day name to it
(DATEPART("dw", GETDATE())==1 ? "Sunday" :
DATEPART("dw", GETDATE())==2 ? "Monday" :
DATEPART("dw", GETDATE())==3 ? "Tuesday" :
DATEPART("dw", GETDATE())==4 ? "Wednesday" :
DATEPART("dw", GETDATE())==5 ? "Thursday" :
DATEPART("dw", GETDATE())==6 ? "Friday" :
DATEPART("dw", GETDATE())==7 ? "Saturday" : "") 

In sql server:-

select left(DATENAME(month,getdate()),3) as [month_name]

Tuesday, September 15, 2015

How to create and use temp tables in SSIS

Creating temp table by using SQL is very easy process. Some times we require to create temp tables in SSIS packages for this we need to configure some properties while creating the ssis package.

Creating a test SSIS  package for  temp table creation:-

 1) Drag execute sql task from SSIS Toolbox and rename it to temp table

 2)Double click on Execute sql task and select your connection string and in your sql statement paste your   SQL statement i pasted below sample

IF OBJECT_ID('tempdb..##temptest') IS NOT NULL
    DROP TABLE ##temptest
    CREATE TABLE ##temptest
        id VARCHAR(255),
        name VARCHAR(255),
        city  VARCHAR (2)
    INSERT INTO ##temptest VALUES
       ('hyderabad', 'cycle', 'ap'),
       ('delhi', 'bike', 'DL')

click ok.

3) Drag a data flow task from SSIS Tool box

4) Double click on the data flow task  after entering into it drag one OLEDB source. now we will pull data from temp table and insert into database by using oledb destination. To avoid error create global temp table in SSMS 

5) Again go to SSIS package and in data flow task go to oledb  select data access mode as SQL command
select the temp table. Click on oledb destination select data access mode as table or view fast load and create table and click ok

6) Once finished above task start debugging. Now you will see below error in execution results.

7)Now we will fix the issue go oledb connection and right click on it. Put the property  retain same connection as true check below image and the delay validation properties as true

8)Now execute the package  successfully executed.

9)Now check in SSMS data loaded into destination.

10) Why global temp table is used here ?

Ans:- We can create it in SSMS and use it in SSIS package because of  global access(##testtemp)(create in any session access in any session). if we use local temp(#testtemp) we face below issue and session specific 


Monday, September 14, 2015

Running SSIS Package From Command Line

 Methods used to execute the ssis package:-

  1. SQL Server data tools(SSDT)/Business Intelligence development studio(BIDS)
  2. Command Line utility 
  3. SQL Server Job Agent

1) SQL Server data tools(SSDT)/Business Intelligence development studio(BIDS)

Step 1:- Go to bids or ssdt whatever you have i have ssdt here, go to solution explorer right click on project name under solution file click on build.
Step 2:- After build successfull click on execute package. if you want make it as startup object click on startup object.  

2) Command Line Utility 

Step 1:- Open your command prompt as administrator (Run as administrator)

Step 2:-  type below text to call package

dtexec /f  <Filepath>
/set \package.variables[Variable]; value

Example:-   dtexec /f E:\test_package\test_package\Package.dtsx
/set \package.variables[id];1


3)Execution using SQL Server job agent

Step 1:- right click on SQL server job agent in your  SSMS and go to job. in general tab give job name, and go to steps and click on new

Give step name, select type as SQL server integration service package and Run as sql server

select your file system in package source and browse and select package path, and click ok

click on start job. execution will start you will see below window

Thursday, September 10, 2015

How to rename column name in a table

Column name can be changed in two ways 

A) SQL server management studio-->right click on the database-->select table and expand-->select column name which you want to rename--> change the name

B)By Using Query:-

SQL server allows you to rename column name by using sp_rename procedure which is already written in the procedure library.
Syntax:- sp_rename 'tablename.old column name','new column name','column'
         Ex:-sp_rename '','Fullname','column'

name column changed to Full name

Renaming Table name 

In same way we can re-name our table also  and below query used to rename table name

Here department is my old table 

Syntax:- sp_RENAME 'OldTableName' , 'Newtablename' 
       Ex:-sp_rename 'department','mydepartment'

Now table name is changed

Renaming Database:-
 for explanation purpose a test database has been created here

create database testdata

Syntax:- sp_renamedb 'old database name','new database name'
           Ex:-sp_renamedb 'testdata','nedb'

Tuesday, September 8, 2015

How to delete duplicate row from table

SQL server tables generally doesn't contains duplicates if design done with proper addition of constraints (Primary, Unique etc...). For data integrity we should not allow duplicates to be insert into table. Sometimes duplicates inserted into table because of duplicate primary keys exist stance. If duplicate pks are present in relational database tables primary key uniqueness is not worked (or) enforced this causes duplicates insertion

Here i will show how to delete duplicates from table:-

CREATE TABLE duplicates_test(id int,name varchar(50),salary int)

insert into duplicates_test values(1,'ram',5000)
insert into duplicates_test values(2,'priya',5000)
insert into duplicates_test values(3,'amitha',4000)
insert into duplicates_test values(4,'patokar',3000)
insert into duplicates_test values(5,'jaya',3000)
insert into duplicates_test values(5,'jaya',3000)

select * from duplicates_test

now  jaya related rows existed two times we want remove it. here am using with CTE(temp table)

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)select * from x where rn>1


 we got duplicates rows information. now use delete instead of select see below query

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)
delete  from x where rn>1

duplicates are deleted after running above cte query

Sunday, September 6, 2015

How to Fix Error Can not connect WMI Provider you do not have permission to Server

Problem Cause : WMI Provider Removed when you uninstalled instance of  32bit/64bit versions of sql using same wmi provider, location of your wmi  provider "C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof" file name,

To quick fix this error:-
go to command prompt type as below :-

C:windows\system32\mofcomp  "C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof" and press enter.

Then you will see below window appeared. Now you can access sql server configuration manager.

for deeper info: refer  MSDN

Wednesday, September 2, 2015

How to schedule a SQL server job In SQL Server 2012

     SQL Server agent helps you to automate (or) schedule a job T-SQL or SSIS Packages Jobs contains one or more steps to execute step by step manner. You can manually run a job or  you can schedule the job as per you convenience. In express edition it is not possible to create a sql server job.

This post helps you to create a job in SQL server agent step by step:-

step:-1) open SSMS  connect to database engine with sql authentication.

step:-2) go to sql server agent right click on it and click on start
             select new job and name it here i named as test-job

step:-3)  go to steps click on new and select type as Transact-Sql Script if  you want execute stored procedure here i considered stored procedure mention the step name,select database where the stored procedure is present. write command "exec load_table" in your case write your stored procedure name 

step:-4) go to schedules click new and give schedule name  and select schedule type based on your requirement 

step:-5)Mentioned the frequency as per your requirement here i choosed daily  frequency for every 15 minutes,duration how many days you want to run the job mention the dates ,i selected no end date, click ok, again click ok 

step:-6)it will execute for every 15 minutes. 

step:-7)if you want to run the job manually you can run by right clicking on the job and click on start job