What is NOLOCK Hint in SQL server ?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is used in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data.  SELECT statements take Shared locks, means multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

The slight disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table ie Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.


How to use:
SELECT * FROM TableName (NOLOCK)

Steps to Deploy Report Model Into Production Server.

 Note :

1.First Deploy the .smdl File into Local Server(http://localhost/reportserver) and then download the .smdl file in to folder(send downloaded files as a deliverable).

2.All tables related with report model should be present  in the data base.

1.Create the DataSource inside the "data Source" folder

                1.1 Click On "New data Source" tab.

                1.2 Give the New data Source Name(.dsv)

                1.3 Data source type: microsoft sql server

                1.4 Connection String:Data Source=<machine name>;Initial Catalog=<db name>;

                Example:Data Source=A648;Initial Catalog=database_Name;

                1.5 check Windows integrated security.

                1.6 Test connection.

                1.7 Apply

2.GO to "Models" Folder

               2.1 Upload the .smdl file

               2.2 Right click-->Manage-->Data Source-->browse the data source.

              2.3 select the data source inside the "data source"Folder.

3.Upload the RDL File.

               3.1 Right click-->Manage-->Data Source-->browse the shared data source.

               3.2 select the data source inside the "Model"Folder.

Error,while restoring the database in MS SQLserver 2008R2

Error: “The file “C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf” is compressed but does not reside in a read-only database or file group. The file must be decompressed.”

solution1:Opened my mdf file properties dialog box, and in the advance tab the problem was revealed: the “compress content” checkbox was checked.

After unchecking and saving changes, SQL Server accepted the attach with no problem whatsoever.

solution2:Check the drive where data base is going to restore.Right click the Drive(C: Drive)--> Property-->General-->Compress this drive to save the disk space(it should be unchecked )

Create a Dyanamic Excel destination file with a filename base on the date

Problem Statement:
Create a Dynamic Excel destination file with a file name base on the date 


Things which we have to remember:
1.Delay validation of DFT(data flow task) should be True.
2.Should not run at 64 Bit run time in short (Run64Bit Run time should be False)
3.Check weather your drive have permission to create the excel file in the folder othere wise it will throw you below error:
 [Error: [Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection. ]

Solution steps:
1.Create a package level variable "XLFileRootDir" as string and set it to the root directory where you want to create excel file.
 Example: D:\
2.Create a package level variable "Client_detail" as string and set it to the file name,which we will use to create the dynamic file name.

 Example: "Client_Detail"
3. Create an Excel connection in the connection manager. Browse to the target directory and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.Later we will replace the file name with our dynamic generated file name.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@[User::XLFileRootDir] + @[User::Client_Details]+(DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an exl file like Client_detail_05072011.xls.

5. Add a SQL task to package and double click to edit.

In the general tab, set 'ConnectionType' to 'Excel'.

For 'SQLStatement', enter the create table SQL to create destination table.

For example:

CREATE TABLE Employee_Detail (

     FirstName NVARCHAR(50)

    ,MiddleName NVARCHAR(50)

    ,LastName NVARCHAR(50)

    ,BirthDate NVARCHAR(50)

    ,Gender NVARCHAR(50)

    ,EmailAddress NVARCHAR(50)

)

Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.

7. Connect this to Excel destination. 

In the destination editor, select the Excel connection in the manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on new button and paste the create table command from Step 5.

Map the columns appropriately in the mappings tab and you are done.

Set the Connection Manager Values using the Script Task

We can set the Connection manager Settings while running the Package.We can save the Connection information into the config file ,and from the config file we can read the values and use in the package.

For Example :check the below sample config File.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

            <section name="TS_Contract_Interface.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

    </sectionGroup>

    </configSections>

How to Set FTP Connection Manager Password Using Script Task(VB)

1.Create the FTP Connection Manager and Enter the "Server name"  and "UserName".Here the Connection manager name is CNRL_FTP Connection Manager

 

2.Public Sub Main()

        Dim FTPConnectionManager As ConnectionManager

        'Set variable to an existing connection manager

        FTPConnectionManager = Dts.Connections("CNRL_FTP Connection Manager")

        'Set connection manager property "ServerPassword"

        FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, "cnrlPUftp")

            Dts.TaskResult = ScriptResults.Success

      End Sub

 

Insert Row Records into Columns.

The Source data is Like:store in Retention_Import_Staging Table

Now We have to convert above data into below format and load data into Retention_Dates table

By Using the Store Procedure we implement the above task:

IF EXISTS (SELECT       *

                              FROM dbo.sysobjects

                              WHERE       id = object_id(N'[dbo].[TS_pCN_Dates_Load]')

                              AND   OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

      DROP PROCEDURE [dbo].[TS_pCN_Dates_Load]]

      PRINT 'Deleted script ''dbo.TS_pCN_Dates_Load'''

END

GO

SET   QUOTED_IDENTIFIER ON

GO

SET   ANSI_NULLS ON

GO

CREATE PROCEDURE [dbo].[TS_pCN_Dates_Load]

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

      DECLARE @RowData varchar(4000)

      DECLARE @Col1 varchar(50)

      DECLARE @Col2 varchar(50)

      DECLARE @Col3 varchar(50)

      DECLARE @Col4 varchar(50)

      DECLARE @SplitOn varchar(3)

      DECLARE @Row_id int    

      DECLARE @array_text varchar(4000)

      DECLARE @Error_fg varchar(2)

SET @Error_fg = (select top 1 error_fg from SFI_Retention_Import_Staging where error_fg ='Y')

IF ISNULL(@Error_fg,'')=''

BEGIN

      ----------------fetch records from SFI_Retention_Import_Staging table using cursor------------------

      DECLARE Retention_Cursor CURSOR FOR

      SELECT Row_id,array_text from Retention_Import_Staging

      OPEN Retention_Cursor

      FETCH NEXT FROM Retention_Cursor INTO @Row_id,@array_text

      WHILE @@FETCH_STATUS = 0

      BEGIN

           

      SET @SplitOn = ','

      SET @RowData = @array_text + ','

      SET @Col1 = NULL

      SET @Col2 = NULL

      SET @Col3 = NULL

      SET @Col4 = NULL 

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col1 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE 

      BEGIN

            SELECT @Col1 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

     

      IF (Charindex(@SplitOn,@RowData)>0)

      BEGIN

            SELECT @Col2 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

      END

      ELSE

      BEGIN

            SELECT @Col2 = ltrim(rtrim(@RowData))

            Set @RowData = ''

      END

      IF (@RowData = '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

      While (Charindex(@SplitOn,@RowData)>0)

      Begin

            SET @Col3 = NULL

            SET @Col4 = NULL

            SELECT @Col3 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            if @col3 = ''

            set @col3 = NULL

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

                 

            IF (Charindex(@SplitOn,@RowData)>0)

            BEGIN

                  SELECT @Col4 = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

                  if @col4 = ''

                  set @col4 = NULL

                  Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

            END

            INSERT INTO dbo.Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            VALUES(@Col1,@Col2,@Col3,@Col4)

      End

      IF (@RowData <> '')

      BEGIN

            INSERT INTO Retention_Dates

            ([ACTION_CD],[RCRD_SERS_CD],[ST_ABBR],[TRIGGER_DT])

            values(@Col1,@Col2,@Col3,@Col4)

      END

      ------------------------------------------------------------------------------------

FETCH NEXT FROM Retention_Cursor INTO @ROW_ID,@array_text

      END

      CLOSE Retention_Cursor

      DEALLOCATE Retention_Cursor

      ---update trigger_date field------------------

      UPDATE Retention_Dates

      SET TRIGGER_DT= (SELECT convert(VARCHAR(10),TRIGGER_DT,101))

END  

      DELETE FROM Retention_Dates

      WHERE ROW_ID IN(

            SELECT row_id

            from

                  (

                  Select row_id,ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT,rank() over (partition by ACTION_CD,RCRD_SERS_CD,ST_ABBR,TRIGGER_DT order by row_id) as rnk

                  from Retention_Dates

                  )aa

            WHERE aa.rnk > 1)

END