To Delete the duplicate rows in the table by Ranking

   DELETE A  FROM (  
  SELECT ROW_NUMBER()OVER(PARTITION BY CenterRefID,CorporateRefID,CenterID,obsolute ORDER BY CenterRefID,CorporateRefID,CenterID DESC)   RANK1,CenterRefID,CorporateRefID,CenterID,obsolute
  FROM TblCorpRefIDMapping  ) A
   WHERE RANK1 > 1

To create Trigger for the Table Column

Create
TRIGGER [dbo].[Trg_TblVisits]
ON [dbo].[TblVisits]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE
A SET LastModifiedDate = GETDATE()
FROM
TblVisits A INNER JOIN inserted B
ON
A.VISITID = B.VISITID

END

To bring the row data in one column

SELECT top 1  
       STUFF((SELECT ',' + b.Coldisp from tempd b Where a.CenterName=B.CenterName FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName  
FROM tempd a  
    GROUP BY  CenterName,Coldata,Coldisp

How to resolve Error 22051?

Jobs failed with below error message.

Error Message:

Executed as user: XXXXXX-svc. Mail queued. [SQLSTATE 01000] (Message 0)  File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22051).  The step failed.

Resolution Steps:

Go to Database Mail under Management in Sql Server Management Studio -->Select Configuration Task as View or Change system parameters -->Give Maximum File Size (Bytes) as to 1.5 million

That's it

Now mail sending commands in the job will execute successfully.

To Split the String Value

SET NOCOUNT ON;
GO

Declare @data varchar(100) = 'MDEB KLP 657 678989'
Declare @date varchar(100) = '2012-5-06'

DECLARE @T TABLE ([description] varchar(MAX));

INSERT INTO @T ([description]) VALUES (@data)

Insert into [TblEBMeterReading]

SELECT
 @date as Date,
 --Q.x.value('(/e/text())[1]', 'varchar(MAX)') [Category],
 Q.x.value('(/e/text())[2]', 'varchar(25)') CenterCode,
 Q.x.value('(/e/text())[3]', 'int') MeterNo,
 Q.x.value('(/e/text())[4]', 'int') MeterReading
FROM
 @T AS T
 CROSS APPLY
 (SELECT T.[description] AS [text()] FOR XML PATH('')) AS R(s)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(R.s, ' : ', '</e><e>') + '</e>' AS xml)) AS S(x)
 CROSS APPLY
 S.x.nodes('/e') AS N1(e)
 CROSS APPLY
 (SELECT CAST('<e>' + REPLACE(REPLACE(N1.e.value('text()[1]', 'varchar(MAX)'), ' ', '</e><e>'), ' ', '</e><e>') + '</e>' AS xml)) AS Q(x)
GO

StoredProcedure used in Jobs

select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%storeproce%'

To Split the column values

Table1 
Value   String 
1          Cleo, Smith 

 

Select

 Value  ,

 Substring(FullName, 1,Charindex(',', FullName)-1) as Name, 
 Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as  Surname 
from Table1

 

 

Output 
Value  Name   Surname 
1        Cleo     Smith