Working with BULK INSERT Task in SQL Server

Using BULK INSERT Task we can import data from text file to SQL Server .
First I have created a table emp in destination SQL server database.

CREATE TABLE [dbo].[emp](

       [empid] [int] IDENTITY(1,1) NOT NULL,

       [name] [varchar](100) NULL,

       [salary] [int] NULL,

PRIMARY KEY CLUSTERED

(

       [empid] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Input data of text file 

7,sami,20000

8,rao,30000

9,rema,24000

BULK INSERT Task is similar to BULK INSERT Command in T-SQL. 

The Following is the Example of BULK INSERT command

BULK INSERT dbo.emp

   FROM 'd:empdetails.txt'

   WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n'

      );

When loading bulk insert command ensure that number of columns is source should macth with destination table . other wise you may this type of error.

 Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (empid).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (empid).

Msg 4832, Level 16, State 1, Line 2

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 2

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  

So always source and destination columns should match when using BULK INSERT Task.

Drag and drop the bulk insert task from control flow

Double click on the bulk insert task and GOTO connection tab

We can specify the format or can use the format file to import the data. Once we have used the specify in the format options then we can have to specify the row delimiter and column delimiter for the specified input file as shown in the picture. then GOTO options tab.

KEEPIDENTITY is used for keeping the existing identity values in the input file. If we check the check box then it will use the existing values. 

The T-SQL code is similar to checking the keepidentity 

BULK INSERT dbo.emp

   FROM 'd:empdetails.txt'

   WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n',

         KEEPIDENTITY

      );

If we unchecked the check box then it will use the destination table identity seeding values. click ok and save the Task.

Policy Based Management in SQL Server

Policy management enables us to implement the policies in SQL Server. Suppose we have implement policy in a user database for each procedure should not start with sp_

Then we have to implement the policy using the following steps.

In order to create policy we have to create a condition.

Conditions will use facets to write a condition using expressions.

Facets is contains objects and their property to create a condition.

Policy is used for evaluating the condition against target. 

Target is an object in which the policy can executed.

Target can be a table, database, procedure etc.

you can see the polocies in the system from the below path

    drive\ProgramFiles\MicrosoftSQLServer\100\Tools\Policies\Database Engine\1033

A policy can be evaluated in different ways

•          On schedule

•          On Demand

•          On Change Prevent

•          On change Log Only

I will show here how to create policy using SQL Server Management Studio

First We have to create condition in order to create policy.

Right click on conditions from Management --> Policy Management --> Conditions

We are creating policy for store procedures that user cannot create procedure with name sp_

@Name means procedures and operator we are using not like sp_

Then we will create policy in order to evaluate the condition for stored procedures

Provide the policy name and select the condition from the drop down. 

We can select the evaluation mode as shown in the above picture. for executing the policy in only specified database we can create one more condition for the target. 

We can provide the filter using database facet with @name

Now the condition is applicable to DBmirror Database.

We have to select evaluation mode as on change prevent in order to avoid creating procedures with name sp_ .We enable this policy as shown below.

Then we will try to create a new procedure with the name sp_

The policy has been evaluated as shown below.

We can fetch the condition from the below query

select condition_id,name,created_by,facet,expression

from msdb.dbo.syspolicy_conditions

Similarly we can find the policies using the following query

select policy_id,name,condition_id,schedule_uid

from msdb.dbo.syspolicy_policies

select*from msdb.dbo.syspolicy_policy_execution_history

select*from msdb.dbo.syspolicy_policy_execution_history_details

select*from msdb.dbo.syspolicy_system_health_state

select*from Msdb.dbo.syspolicy_policy_category_subscriptions

For deleting the policy

EXEC msdb.dbo.sp_syspolicy_delete_policy@policy_id=1

GO

EXEC msdb.dbo.sp_syspolicy_delete_object_set@object_set_id=1

GO

For deleting the conditions we have use the below system stored procedure

EXEC msdb.dbo.sp_syspolicy_delete_condition@condition_id=1

SQL Sever Login Issues

I have faced issues with SQL server logins in my career while connecting to SQL Server.

I have created a SQL Login with the following details

CREATE LOGIN [Publicuser] WITH PASSWORD=N'!@DFSRE$$%^', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Login created successfully.  When am connecting to SQL server using the Publicuser.I am getting error.

 Login Failed for user ‘Publicuser’ (Microsoft SQL Server, Error: 18456)

I have checked the error log using xp_readerrorlog

From eventlog I got the error message

Login failed for user 'publicuser'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

Even though I have created the SQL server when installing SQL server I have given only windows authentication mode for login. So SQL server login users are forcedly disabled.

So I have enabled mixed mode. right click on server instance.

Select Security Tab.Select server authentication as SQL sever and Windows Authentication mode.

Click Ok.

I have to restarted the SQL sever Instance. then only the changes will reflect.

I have to restarted SQL Server Instance.

 Then i have Connected using "Publicuser" login.

If Login is disabled it will show this error.

you can enable the login account.

If Login user created and deny the user permissions.It will generates the below error.

Login failed for user 'Publicuser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]

Table Partition in SQL Server

Table Partition will be used to split the table into multiple file groups so that we can reduce the lock escalation and we can improve query performance.

Suppose we have table like this

create table emp (id int identity ,            

                          name varchar(200),

                          Datejoined date default getdate(),

                          email varchar(100),

                          phonenumber bigint,

                          gender char(1),

                          alternatecontactnumber bigint )

In order to create partition table the database must be divided into
different file groups. We can check the current file groups in the database using the below query

       select * from sys.filegroups

In order to create partition the table first we have to add filegroups to the database using the below query.

ALTER DATABASE  PartitionDemo ADD FILEGROUP [Secondary]

GO

ALTER DATABASE PartitionDemo ADD FILEGROUP [Tretiry]       

GO 

We have created the file groups successfully.the second step in creating table partition is to create partition scheme.

CREATE PARTITION FUNCTION [partfunction](int) AS RANGE LEFT FOR VALUES (N'1000','2000')  

you can find the partition functions from the below query

select * from sys.partition_functions 


Similarly you can view the partition function ranges using the below query

select * from sys.partition_range_values

The partition function will use the boundaries with LEFT or RIGHT.

LEFT function will use the LEFT boundary.

1st partition -- < =1000 
2nd partition -- > 1000 and <=2000 

RIGHT function will use the RIGHT boundary

1st partition -- <1000 font="">
2nd partition -- > = 1000 and < 2000


The next step in creating the table partition is to create partition scheme using the partition function.

we can use the partition scheme using the following command

CREATE PARTITION SCHEME [PartScheme] AS PARTITION [partfunction] TO ([PRIMARY], [Secondary],[Tretiry]) 

using the below query we can find the partition schemes using the below query

select * from sys.partition_schemes

Now you can see the we have created partition schema as shown in the above picture.

Next we have to create a clustered index on the emp table using the partition scheme.

CREATE UNIQUE CLUSTERED INDEX [IXUC_PartScheme] ON [dbo].[emp]

(

      [id]

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartScheme]([id])

you can able see this partitions using the below query

select * from sys.partitions where OBJECT_NAME(object_id)='emp'

For new table you can able to assign the existing partition scheme using 

CREATE TABLE newEmp (ID int IDENTITY PRIMARY KEY, name varchar(100))

    ON [PartScheme] (ID)

 Based on the above picture you will know that you can use the partition scheme for as many tables as possible if the data type on partition scheme will match.

So we have successfully created table partition.


Now  we will load the data into partitioned table emp.

Execute the below query for inserting test data into partition table.

insert into dbo.emp (name) values ('testdata')

GO 1000 

We have inserted the 1000 rows using loop (GO statement).Now we will verify the number of rows in partition table emp.

We have inserted 1000 rows successfully. LEFT partition uses ranges <= 1000. so next row should be inserted into secondary filegroup (second partition). we will execute the statement.

insert into dbo.emp (name) values ('testdata')

Exception has been raised Msg 622, Level 16, State 3, Line 1
The filegroup "Secondary" has no files assigned to it. Tables, indexes, text columns, ntext columns, 
and image columns cannot be populated on this filegroup until a file is added.

we are getting the above error. because we did not add file to the secondary filegroup. we will the files to files group using the below query.

-- Add data file to [[Secondary]]

ALTER DATABASE PartitionDemo

ADD FILE (NAME = PartitionDemo1,FILENAME = 'D:\TestDB1.ndf')

TO FILEGROUP [Secondary]

GO

-- Add data file to [Tretiry]

ALTER DATABASE PartitionDemo

ADD FILE (NAME = PartitionDemo,FILENAME = 'D:\TestDB2.ndf')

TO FILEGROUP [Tretiry]

Once we have add files to filegroup we can able to insert the rows into secondary partition.

insert into emp (name) values ('1001row')

Data has been inserted successfully . new row has been inserted into second partition.

SELECT $PARTITION.[partfunction](1000) 'Partion number',

$PARTITION.[partfunction](2000) 'Partion number',

$PARTITION.[partfunction](1500) 'Partion number'

Using the  $PARTITION function we can verify the range belongs to which partition.

We can Merge the partition using the below query.

ALTER PARTITION FUNCTION [partfunction] ()

MERGE RANGE (1000); 

partition Now you can verify that there is only two partitions

We can split partition using the split function

ALTER PARTITION FUNCTION [partfunction] ()

SPLIT RANGE (1000);

Exception has been thrown "Msg 7707, Level 16, State 1, Line 1
The associated partition function 'partfunction' generates more partitions 
than there are file groups mentioned in the scheme 'PartScheme'.

The above error shows that two partitions are associated with two filegroups but the statement given in query is trying to split into one more partition but there is no filegroup associated with third partition. In order to split first we will associate the filegroup for third partition.

ALTER PARTITION SCHEME [PartScheme]

NEXT USED [Tretiry]; 

Now we can able to split the partition as shown below