If exist......... else if NOT Exist

Create procedure [dbo].[PMP_CML_AddAttendeeInformationAndAddress]
 @PolarisCustomerID AS VARCHAR(50)='',
 @Customer_Id AS VARCHAR(50)='',
 @FullName AS VARCHAR(100)='',
 @Customer_Type AS VARCHAR(50)='',
 @NPINumber AS INT=0,
 @PolarisAddressID AS VARCHAR(50)='',
 @Address_ID AS VARCHAR(100)='',
 @AddressLine1 AS VARCHAR(100)='',
 @City AS VARCHAR(50)='',
 @State AS VARCHAR(50)='',
 @PostalCode AS NVARCHAR(50)='',
 @createDate AS datetime,
 @SourceSystem AS VARCHAR(100),
 @Country AS varchar(50)
AS
  BEGIN TRAN
--------RecipientType_id,State_Id,Country
DECLARE
@RecipientType_id INT,
@State_Id INT,
@COUNT INT,
@intErrorCode INT,
@AttendeeInfoAttendee_id INT

select @RecipientType_id = RecipientType_id from PMP_RecipientType where RecipientType_Name = @Customer_Type
select @State_Id = State_Id from PMP_StateMaster where State_initials = @State
 
     IF(EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     WHERE --AI.PolarisCustomerID = @PolarisCustomerID
         AI.CustomerID = @Customer_Id
     AND AI.Fullname_organizationName = @FullName
     AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
     AND A.Location_id =  @Address_ID
     AND A.AddressLine1 = @AddressLine1
     AND A.City = @City
     AND A.State_Id = @State_Id
     AND A.PostalCode = @PostalCode
     AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
     AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
         ))
     BEGIN
     -------No Changes in Existing records of Core Tables
     SET @COUNT =1
     END
     ELSE IF(NOT EXISTS(SELECT A.*,AI.PolarisCustomerID,AI.CustomerID,AI.Fullname_organizationName,AI.RecipientType_id,AI.NPINumber
     FROM PMP_AttendeeInformation AS AI INNER JOIN
     PMP_Address AS A ON AI.Attendee_Id = A.Attendee_Id
     where    AI.CustomerID = @Customer_Id
     --AND AI.Fullname_organizationName = @FullName
     --AND AI.RecipientType_id = @RecipientType_id
     --AND AI.NPINumber = @NPINumber
     --AND A.PolarisAddressID = @PolarisAddressID
     --AND A.Attendee_Id = @Customer_Id
       AND A.Location_id =  @Address_ID
     --AND A.AddressLine1 = @AddressLine1
     --AND A.City = @City
     --AND A.State_Id = @State_Id
     --AND A.PostalCode = @PostalCode
  AND Convert(VARCHAR(10),AI.CreatedDate,121) = Convert(VARCHAR(10),@createDate,121)
  AND  Convert(VARCHAR(10),A.CreateDate,121) = Convert(VARCHAR(10),@createDate,121)
     ))
    
     BEGIN
    
    
     INSERT INTO PMP_AttendeeInformation(PolarisCustomerID,CustomerID,Fullname_organizationName,RecipientType_id,NPINumber,Isorg,CreatedDate,Creator)
     VALUES(@PolarisCustomerID,@Customer_Id,@FullName,@RecipientType_id,@NPINumber,1,@createDate,@SourceSystem)
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation --where  CustomerID = @Customer_Id and  Fullname_organizationName = @FullName
     INSERT INTO PMP_Address(PolarisAddressID,Attendee_Id,Location_id,AddressLine1,City,State_Id,PostalCode,Country,CreateDate,Creator)
     VALUES(@PolarisAddressID,@AttendeeInfoAttendee_id,@Address_ID,@AddressLine1,@City,@State_Id,@PostalCode,@Country,@createDate,@SourceSystem)
    
     END
    
     ELSE
    
     BEGIN
   ------------------------------ Update Record ---------------------------------------
    
     select  @AttendeeInfoAttendee_id = max(Attendee_Id) from PMP_AttendeeInformation
    
 UPDATE PMP_AttendeeInformation SET
     PolarisCustomerID = @PolarisCustomerID
     ,Fullname_organizationName = @FullName
     ,RecipientType_id = @RecipientType_id
     ,NPINumber = @NPINumber
  ,Creator = @SourceSystem
     WHERE Attendee_Id  = @AttendeeInfoAttendee_id
   
    UPDATE PMP_Address SET
     PolarisAddressID = @PolarisAddressID
     ,Location_id = @Address_ID
     ,AddressLine1 = @AddressLine1
     ,City = @City
     ,State_Id = @State_Id
     ,PostalCode = @PostalCode
     ,Country = @Country
  ,Creator = @SourceSystem
     WHERE Attendee_Id = @AttendeeInfoAttendee_id
   
     END
    
     SELECT @intErrorCode = @@ERROR
 IF (@intErrorCode <> 0) GOTO PROBLEM
 COMMIT TRAN
    ----------------------------------------
   PROBLEM:
 IF (@intErrorCode <> 0)
 BEGIN
     ---PRINT 'Unexpected error occurred!'
   ROLLBACK TRAN
    END

SSIS Tutorials

truncate table ee;
DBCC CHECKIDENT('ee',reseed,100);


File System task use to create rename folder


FTP Task
Send Or Receive files on Remote Servers
delete local or remote files
create local or remote directories
delete local or remote directories
right click connection manager section ---> new connection --> set properties
Add Ftp task on control flow window --- double click---select ftpConnection
go to second tab fileTransfer---where u set remote path

Send Mail Task:-
go to connection manager section right click ---> select New Connection --- > then select SMTP
set SMTP server name = localhost
now drag send email tak on control flow window -->double click
under mail tab select smtpConnection --- and set other properties
Precedence Constraint

Derived Column
Upper([FirstName]+ ""+[LastName])

DataViewer
right click Prcedence select Data view select Grid OK..

Flate File Source--- take data on file and insert into destination

Union All --- Union two flat to one

Sort ----- sorting column

Audit ---- contain information of the package

SMTP server response: 550 5.7.1 Unable to relay or cannot pick up iis directory

1) Go to: start > settings > control panel > Administrative Tools > Internet Information Services
2) Expand the " (local computer)" node
3) Right click on your SMTP server > go to "Properties"
4) Click "Access" tab
5) Under Relay Restrictions, click the "Relay" button
6) Click "Add"
7) Select "Single Computer" and enter IP address 127.0.0.1
8) Hit OK, OK, OK (until the properties dialog is closed)

and also web.config set smtp server = '127.0.0.1'

mobile application connect

http://www.youtube.com/watch?v=j9XlVUif2G8&NR=1

how to Remove log file on database

http://4rapiddev.com/sql-server/delete-shrink-eliminate-transaction-log-ldf-file/

how to pass particular column rows rows vise values to a procedure like EventId

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                 ds = obj.GetPartEventReportnew(Convert.ToInt32(dlstEventUsercount.Tables[0].Rows[i]["Event_Id"]), attendeeId, fromdate, todate);
            }

how to remove dropdownlist all rcods through java script

 document.getElementById("ctl00$ContentPlaceHolder1$G_drpGD_Type").length = 0;