Tuesday, November 22, 2011

Error Handling in SP

BEGIN CATCH
   DECLARE @ErrorSeverity INT
   DECLARE @ErrorState INT
   SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorCode = ERROR_NUMBER()
 
   RAISERROR (@ErrorCode,@ErrorSeverity,@ErrorState,@ErrorMessage)--(50327,14,1)
   RETURN 1
END CATCH

Partition Logic in ETL Design


Partition Logic

/****** Object:  PartitionFunction [partfunc_TinyInt_MOD10]    Script Date: 11/22/2011 15:33:47 ******/

CREATE PARTITION FUNCTION [partfunc_TinyInt_MOD10](tinyint) AS

RANGE RIGHT FOR VALUES (0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09)

GO

/****** Object:  PartitionScheme [partscheme_DATA_TinyInt_MOD10]    Script Date: 11/22/2011 15:34:30 ******/

CREATE PARTITION SCHEME [partscheme_DATA_TinyInt_MOD10]

AS PARTITION [partfunc_TinyInt_MOD10] TO ([DATA], [DATA], [DATA],

[DATA], [DATA], [DATA], [DATA], [DATA], [DATA], [DATA])

GO

1.       SQL Task for Tracking data load partition wise

EXECUTE [dbo].[sp_syslargevolumelog_get_isloadedsuccessful_value]

   @PackageName = 'GMODW_Datasources_GMED_IndividualNonPII.dtsx'

  ,@TempSchemaName = 'tmp'

  ,@TempTableName = 'GMED_IndividualNonPII_P01'

  ,@IsLoadedSuccessful = ? OUTPUT



2.       If loading not at completed it will go down

@IsLoadSuccessful==FALSE



3.       SQL Task for Create partition table

/****** Drop   Table [tmp].[MED_IndividualNon_P01]    ******/



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[MED_IndividualNon_P01]') AND type in (N'U'))

BEGIN

DROP TABLE [tmp].[MED_IndividualNon_P01]

END

GO



CREATE TABLE [tmp].[MED_IndividualNon_P01](

                [MedIndID] [bigint] NOT NULL,

                [MedOrgID] [bigint] NULL,

                [PIIFileID] [int] NOT NULL,

                [MOD10] [tinyint] NOT NULL,

                [DWRowInsertedDate] [smalldatetime] NOT NULL DEFAULT (getdate())

) ON [partscheme_DATA_TinyInt_MOD10]([MOD10])

WITH

(

DATA_COMPRESSION = PAGE

)

GO



4.       Data flow task for Data Load



5. SQL Task for tracking data load partition wise

EXECUTE [dbo].[sp_syslargevolumelog_set_isloadedsuccessful_value]

   @PackageName = 'MODW_Datasources_MED_IndividualNon.dtsx'

  ,@TempSchemaName = 'tmp'

  ,@TempTableName = 'MED_IndividualNon_P01'

  ,@IsLoadedSuccessful = 1



6. SQL Task for Create temporary table same as original table

/****** Drop   Table [tmp].[MED_IndividualNon]    ******/



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[MED_IndividualNonPII]') AND type in (N'U'))

BEGIN

DROP TABLE [tmp].[MED_IndividualNon]

END

GO





CREATE TABLE [tmp].[MED_IndividualNon](

                [MedIndID] [bigint] NOT NULL,

                [MedOrgID] [bigint] NULL,

                [PIIFileID] [int] NOT NULL,

                [MOD10] [tinyint] NOT NULL,

                [DWRowInsertedDate] [smalldatetime] NOT NULL DEFAULT (getdate())

) ON

 [partscheme_DATA_TinyInt_MOD10]([MOD10])

WITH

(

DATA_COMPRESSION = PAGE

)

;



7. Switch individual partition to temporary table

ALTER TABLE tmp.MED_IndividualNon_P01

SWITCH PARTITION 1 TO tmp.MED_IndividualNon PARTITION 1



8. Create unique clustered indexes

CREATE UNIQUE CLUSTERED INDEX [IXCU_MedIndID_MOD10] ON [tmp].[MED_IndividualNon]

(

 [MedIndID] ASC,

 [MOD10] ASC

)WITH (DATA_COMPRESSION = PAGE) ON [partscheme_DATA_TinyInt_MOD10]([MOD10]);



9. Rename the temporary table as original table

-- Here is the most important part, we will drop the gmed.IndividualNon table, and replace it with the tmp.MED_IndividualNonPII table.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[gmed].[IndividualNon]') AND type in (N'U'))

BEGIN

                DROP TABLE [gmed].[IndividualNon]

END



ALTER SCHEMA [gmed] TRANSFER [tmp].[MED_IndividualNon]

EXEC SP_RENAME 'med.MED_IndividualNon','IndividualNon'

Thursday, October 13, 2011

Finding Dependent Tables from Stored Procedures

select distinct object_name(referencing_id) SpName,isnull(sd.referenced_schema_name,'dbo') referenced_schema_name,sd.referenced_entity_name from sys.sql_expression_dependencies sd

LEFT JOIN sys.objects obj ON obj.object_id=sd.referenced_id

 where referencing_id in (select object_id from sys.objects

            where name like '%Agree%' and type='P'

Thursday, September 15, 2011

Split functions

1.
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
    select @idx = 1       
        if len(@String)<1 or @String is null  return       
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       
        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       
        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
   end   
return       
end 
GO
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

2.
CREATE FUNCTION [dbo].[fn_Split_Up_Ids](   @Param_Ids varchar(500))RETURNS @Id_Table TABLE(IDField int)AS
BEGIN     IF (LEN(@Param_Ids) <= 0)       RETURN   DECLARE @CommaPos smallint   SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))           IF @CommaPos = 0       INSERT INTO @Id_Table               VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))   ELSE        BEGIN           WHILE LEN(@Param_Ids) > 1        BEGIN         SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))             INSERT INTO @Id_Table                       VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))         SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))           SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))          IF @CommaPos = 0        BEGIN                 INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))                 BREAK           END         END       END       RETURN   END
GO
select * from fn_Split_Up_Ids('1,2,3')

Error Handling in SP

BEGIN CATCH
   DECLARE @ErrorSeverity INT
   DECLARE @ErrorState INT
   SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorCode = ERROR_NUMBER()
  
   RAISERROR (@ErrorCode,@ErrorSeverity,@ErrorState,@ErrorMessage)--(50327,14,1)
   RETURN 1
END CATCH

Finding Dependent Tables

DECLARE @TempDepends Table(Name varchar(200),typeName varchar(20),updated varchar(10),selected varchar(10),columnName varchar(50))
INSERT INTO @TempDepends EXEC sp_depends @objname = N'vwAccount' ;
SELECT distinct Name FROM @TempDepends
DECLARE @TempDepend Table(oTypeName varchar(20),oObjName varchar(200),oOwner varchar(10),oSequence varchar(10))
INSERT INTO @TempDepend EXEC sp_MSdependencies N'Account', null, 1315327
SELECT distinct oObjName FROM @TempDepend
SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name) 
GROUP BY
    so.name
    having MAX(si.rows)=0
ORDER BY
    2 DESC

DB space Findings

1.
xp_fixeddrives

2.
SELECT
    a.FILEID,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
    a.name as [DATABASENAME],
    a.FILENAME as [FILENAME]
FROM
   dbo.sysfiles a