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

                                                                 Download  
  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
    Go
    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;
GO
reconfigure ;
GO
EXECUTE master.dbo.sp_configure 'ad hoc distributed queries', 1 ;
GO
reconfigure;
    7.  Run this query and change excel path  with your excel
         SELECT*FROM OPENDATASOURCE
         ('Microsoft.ACE.OLEDB.12.0','Data Source D:\sample.xls;
         Extended Properties = Excel 12.0')...Sheet1$


          SELECT*FROM OPENDATASOURCE
          ('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
Synatax
Example
TableName
"tbl<TableName>"
tblEmployees
tblOrders
tblProducts
Primary Key Constraint
"PK_<TableName>"
PK_Employees
PK_Orders
PK_Products
Foreign Key Constraint
"FK_<TargetTable>_<SourceTable>".
FK_Orders_Employees
FK_Items_Products
Unique Key Constraint
"UQ_<TableName>_<ColumnName(s)>"
UQ_Employees_EmailID
UQ_Items_Code
Default Constraint
"DF_<TableName>_<ColumnName>"
DF_Employees_IsActive
DF_Employees_CreateDate
Check Constraint
"CHK_<TableName>_<ColumnName>"
CHK_Employees_Salary
CHK_Employees_DOB
User Defined Stored Procedures
User defined: "usp_" "usp_<Action>_<Object>"  System defined: "sp_"
usp_Insert_Employees
usp_View_EmployeeOrders
User Defined Functions
fn_<Action>
fn_CalulateTax
fn_CalculateAge
Views
"vw_<Result>"
vw_EmpOrderDetails
vw_SalesProductDetails
Triggers
"trg_<Action>_<TableName>"
trg_Ins_Employee
trg_Upd_Employee
trg_Del_Employee
Indexes
"IX_<TableName>_<Column(s)>”
IX_Employee_Name
IX_Employee_NameMobileNo

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)

DECLARE myCursor CURSOR LOCAL FOR
    SELECT 'ALTER TABLE [' + sys.objects.name + ']
        ALTER COLUMN ['+ sys.columns.name + '] ' + sys.types.name +
        CASE sys.types.name
            WHEN 'text' THEN ' '
            WHEN 'ntext' THEN ' '
            ELSE '(' + RTRIM(
                CASE
                    WHEN sys.columns.max_length = -1 THEN 'MAX'
                    WHEN sys.columns.max_length > 4000 THEN 'MAX'
                    ELSE CONVERT(CHAR,sys.columns.max_length)
                END) + ')'
        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 ( sys.types.name LIKE 'sys%' )

OPEN myCursor
FETCH NEXT FROM myCursor INTO @SqlCollate

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'Executing: ' + @SqlCollate
    BEGIN TRY
        EXEC(@SqlCollate);
        PRINT 'Done!' + CHAR(10)
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE() + CHAR(10)
    END CATCH
    FETCH NEXT FROM myCursor INTO @SqlCollate
END

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

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

Ex:-

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

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]