SQL CONVERT Function

Syntax 
CONVERT(data_type(length),expression,style)

SQL Query
UPDATE HS_HR_EMPLOYEE
SET EMP_EPF_NUMBER = CONVERT (VARCHAR (20),CONVERT(NUMERIC,NSSF_NUMBER))
FROM AAA_NSSF_NO_UPLOAD
WHERE HS_HR_EMPLOYEE.EMP_NUMBER = AAA_NSSF_NO_UPLOAD.EMP_NUMBER

SQL Server 2008 Process Information

By using the SQL Server Activity Monitor you can know the SQL Server processes and how these processes affect the current instance. 

Shrink MS SQL Data Base

Shrink SQL Data Base for reduce the Log File size.

Log to SQL server with Sa account. Run below query with relevant values.

USE <write the database name>;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE <write the database name>
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 10 MB.
DBCC SHRINKFILE (<Database Logical Name>, 10);
GO

-- Reset the database recovery model.
ALTER DATABASE <write the database name>
SET RECOVERY FULL;
GO

LEFT OUTER JOIN

My Requirement is :
All employees have some value for TRN_DTL_CODE = '000003'
Only few employees have  value for TRN_DTL_CODE = '000124'
I want the data view for the format of TRN_STARTDATE,TRN_ENDDATE,EMP_NUMBER, NSSF, VOL_NSSF
NSSF = Value of TRN_DTL_CODE = '000003'
VOL_NSSF =   Value of TRN_DTL_CODE = '000124'

I used LEFT OUTER JOIN for create this.

CREATE VIEW VW_HS_PR_VOLUN_NSSF AS
SELECT A.TRN_STARTDATE AS TRN_STARTDATE, A.TRN_ENDDATE AS TRN_ENDDATE , A.EMP_NUMBER AS EMP_NUMBER,
A.TRN_PROC_EMP_AMT AS NSSF, ISNULL (B.TRN_PROC_EMP_AMT,0)  AS VOL_NSSF   FROM
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000003' ) A LEFT OUTER JOIN 
(SELECT TRN_STARTDATE, TRN_ENDDATE , EMP_NUMBER, TRN_PROC_EMP_AMT
FROM HS_PR_PROCESSEDTXN WHERE TRN_DTL_CODE = '000124' ) B
ON
A.EMP_NUMBER = B.EMP_NUMBER AND
A.TRN_STARTDATE = B.TRN_STARTDATE AND
A.TRN_ENDDATE = B.TRN_ENDDATE

Format Field Option in Crystal Reports

My requirement is to format all fields to a one font type, color and size.

Right click the field which you want to apply the format of it for others. Then click the Paint brush icon in tool bar.

Finally click the field which you want format.

Make Insert Queries for the Existing Data in MS SQL

Step 1 : Right Click the MS SQL Data Base. Go --> Tasks --> Generate Scripts 

Step 2 : Click the Next button.

Step 3 : Select the table/s data which you want to take as insert query. Then Click Next

Step 4 : Click the advance button and select the Types of data to Scripts option and set it to the Schema and data. Then give a path for save your query. 

Step 5 : Wait till the process compete and collect you query . 

How to View Logical File Names in MS SQL DB

Log to the MS SQL Server with Sa login and run the below query after selecting the Data base.


SELECT FILE_NAME(1) AS 'File Name 1', FILE_NAME(2) AS 'File Name 2';