Configuration of Gmail Smtp in Database mail in Sql Server 2008

Configuration of Gmail Smtp in Database mail in Sql Server 2008

Step 1:
           Connect to Sql server 2008.Expand Management in Object Explorer and right click on Database Mail and click Configure Database Mail 

Step 2:
select first option and click next.
       

Step 3:

Give any ProfileName for ex: "AlerNotify" and Click Add and then click New Account.



Step 4:



EmailAddress: vagalla.suresh@gmail.com
Display name: VAGALLA
Reply e-mail:vagalla.suresh@gmail.com
Server name: smpt.gmail.com
Port number:  587

Basic authentication

Username: vagalla.suresh@gmail.com
password: my gmail password
confirm password: my gmail password

Step 5:
Make that as default profile. and click Next and then Next and click Finish.


Step 6;
 close

Step 7:
  we can test  by right click on Database mail  and click send mail.
 
 

trigger for INSERT UPDATE DELETE in single statement

trigger for INSERT UPDATE DELETE in single statement

CREATE TRIGGER TRG ON V
FOR INSERT,UPDATE,DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'INSERTED' FROM inserted
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'DELETED' FROM deleted
END
IF EXISTS(SELECT * FROM deleted)
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT INTO V_log(ID,NAME,flag) SELECT *,'UPDATED' FROM inserted
END
END

DYNAMIC SQL

------------------------DYNAMIC SQL-------------------

CREATE PROC usp_DYNAMIC(@empno int=null,@ename varchar(20)=null,@job varchar(10)=null,@mgr int=null)

as

BEGIN

DECLARE @SQL NVARCHAR(MAX)='SELECT * FROM EMP WHERE 1=1'

IF(@empno IS NOT NULL)

SET @SQL=@SQL+' and EMPNO='+CAST(@empno AS VARCHAR)

IF(@ename IS NOT NULL)

SET @SQL=@SQL+' OR ENAME='+CHAR(39)+@ename+CHAR(39)

IF(@job IS NOT NULL)

SET @SQL=@SQL+' OR JOB='+CHAR(39)+@job+CHAR(39)

IF(@mgr IS NOT NULL)

SET @SQL=@SQL+' OR MGR='+CAST(@MGR AS VARCHAR)

--print(@SQL)

EXEC(@SQL)

END

EXEC usp_DYNAMIC @empno=7369,@ename='ALLEN',@job='CLERK'

--backup databases

alter PROC backups 
as
BEGIN
DECLARE @DBNAME NVARCHAR(MAX)=''
DECLARE @DATE DATETIME=''
SET @DATE=GETDATE()
DECLARE BACK CURSOR
FOR SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID>4
OPEN BACK
FETCH NEXT FROM BACK INTO @DBNAME 
DECLARE @FULLNAME NVARCHAR(500)=''
DECLARE @SQL NVARCHAR(MAX)=''
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @SQL=('BACKUP DATABASE '+@DBNAME+' TO DISK=''D:\BACKUP\'+CAST(@DATE AS VARCHAR(100))+'.BAK''')
PRINT(@SQL)
FETCH NEXT FROM BACK INTO @DBNAME
END
CLOSE BACK
DEALLOCATE BACK
END

EXEC backups

Recursive CTE

-------------------------Recursive CTE--------------------

SELECT * INTO #TEMP FROM EMP WHERE 1=2

DECLARE @mgr int

SELECT @mgr=mgr FROM EMP WHERE empno=7369

WHILE(@mgr IS NOT NULL)

BEGIN

INSERT INTO #TEMP SELECT * FROM EMP WHERE empno=@mgr

SELECT @mgr=mgr FROM EMP WHERE empno=@mgr

END

SELECT * FROM #TEMP

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

;WITH X AS

( 

SELECT * FROM EMP WHERE EMPNO=7839

UNION ALL

SELECT E.* FROM X INNER JOIN EMP E ON X.EMPNO=E.MGR

)

SELECT * FROM X

REPORTING SERVER Expressions

--color for alternate rows
=IIF(RowNumber(NOTHING) MOD 2=0,"Plum","Khaki")

--Row Number
 =RowNumber(nothing)

--text color
=IIF(Fields!DATETIME_PRECISION.Value>1,"Red","Black")

--Count of Rows
=COUNT(Fields!TABLE_SCHEMA.Value, "DataSet1")

--SPLIT & JOIN 
=SPLIT(JOIN(Parameters!DEPTNO.Value,","),",")

--sum of salary
=Sum(Fields!SAL.Value, "DataSet1")

--sum of each dept sal
=sum(Fields!SAL.Value)

Different types of joins in SQLSERVER

--------------------------Different types of joins-----------------------

1.FULL OUTER JOIN

SELECT * FROM [table name1] T1 FULL OUTER JOIN [table name2] T2 ON 

           T1.[column name]=T2.[column name]

DECLARE @id INT

SELECT * FROM [table name] WHERE [column name]=@id  

                 OR [column name] IS NULL