SQL Server Transaction Isolation Level - READ UNCOMMITTED

BEGIN TRAN
UPDATE tableA SET
    HourlyRate = 100
WHERE ConsultantName = 'Raj'

Note that the transaction is not committed or rolled back. Since the transaction is open, the lock applied on the row being updated is not released yet. Now, open a second window (Query Window 2) and run the following.

SELECT * FROM tableA

As expected, this will block. The query will try to read a row locked exclusively by another transaction and cannot proceed until the lock is released. Now, let us see what happens under READ UNCOMMITTED isolation level.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tableA 

OR

SELECT * FROM table A WITH(READUNCOMMITTED) 

Basic SSIS Package

 

1.Open 'Business Intelligence Development Studio' comes with SQL 2005 or VS 2008 and create Integration Services project

2.Design .dtsx ( pakage file)

3.Our goal is to import .xlsx file data to SQL DB, log package status and if error occurs log error & send Email.

4.

Create connection managers like ADO.NET connection, OLEDB connection, and SMTP connection etc

OLEDB Connection



xlsx file-OLEDB connection

1.  
    



4. Drag & drop control flow items as shown here depends on the design. Create Variables as shown in the figure.

The SSIS package



 5. In the 'Data Flow' connect the source and destination connections through a data convertor.

6. Create an onError Event Handler, and create a 'Script Task'. Set the values like 'System::ErrorDescription' as   read only variable and 'User::errorMessages' as readwrite variable.

7. Click on Design Script & create code to capture the all errors into a Colloection.

8. Create 'Sequence Containers' as shown in the SSIS package and create 'Script Task' to capture all errors into a single string and insert to DB and send Email.

9. Click on Design Script
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
'Developed By : Rajesh Subbiah
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Net
Imports System.Net.Mail
Public Class ScriptMain

Public Sub Main()
Dim errorDesc As String
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)
Catch ex As Exception
'If there is an exception – the object was never initialized, so there were no errors
Return
End Try
For Each errorDesc In messages
Dts.Variables("emailText").Value = Dts.Variables("emailText").Value.ToString + errorDesc + vbCrLf
Next

Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP_CONN_MAIL_SERVER").AcquireConnection(Dts.Transaction), String)
Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)
Dim htmlMessageFrom As String = Dts.Variables("emailFrom").Value.ToString()
Dim htmlMessageTo As String = Dts.Variables("emailTo").Value.ToString()
Dim msgErrors As String = Dts.Variables("emailText").Value.ToString()
Dim htmlMessageSubject As String = Dts.Variables("emailSubject").Value.ToString()
Dim htmlMessageBody As String = "
Warning ! : The following error occured while running the SSIS package
"
htmlMessageBody = htmlMessageBody + " " + msgErrors + "."
LogError(msgErrors) ' Log the error into DB
SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub

Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String,ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
If (SendTo.Trim() = "") Then SendTo = From
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim maSendTo As New MailAddress(SendTo)
Dim maFrom As New MailAddress(From)
htmlMessage = New MailMessage(maFrom, maSendTo)
htmlMessage.Subject = Subject
htmlMessage.Body = Body
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
htmlMessage.Dispose()
End Sub

Private Sub LogError(ByVal msgErrors As String)
Dim cm1 As ConnectionManager = Dts.Connections("ISTools_ADO.NET")
'Dim cm2 As ConnectionManager = Dts.Connections("ISTOOLS_OLEDB")

' For an ADO.Net ConnectionManager using a SqlClient providerDim sqlConn As System.Data.SqlClient.SqlConnection = DirectCast(cm1.AcquireConnection(Dts.Transaction), SqlConnection)
Dim oCmd As SqlCommand = New SqlCommand
oCmd.CommandType = Data.CommandType.Text
oCmd.CommandText = "INSERT INTO Log_SSIS_Status(PkgName,PkgStatus,IsSuccessful,Notes) VALUES('HScan-ImportFile','SSIS-Failure.Logged,Sent Email',0,'" + msgErrors + "')"
oCmd.Connection = sqlConn
'sqlConn.Open()
oCmd.ExecuteNonQuery()
'release the object finally
cm1.ReleaseConnection(sqlConn)
End Sub
End Class

10. The variables can be configured via a config file.

11. run in development environment.Set Break points to debug.

12.Deploy the package to the Server and view the job through 'Server Agent' in the management studio.







Distinct of Data based on a particular Column-TSQL

When we want a distinct of data from a huge DB table (In my case I have 2 lakhs records (0.2 million))

Here ColA has duplicate records based on the ColB data.
We want distinct of ColA and one ColB data. We cannot use the following query.
SELECT DISTINCT ColA, ColB FROM tableA.
Else we have to separately store the distinct values in a DS and then loop through the table again for ColB.
Instead we can use the simple GROUP BY clause
"SELECT tableA.ColA,min([tableA.ColB])FROM tableA GROUP BY [tableA.ColA]"