looping with Pipe

WHILE LEN(@CandidateIds) > 0
BEGIN
IF PATINDEX('%|%',@CandidateIds) > 0
BEGIN
SET @CandidateId = CONVERT(BIGINT,(SUBSTRING(@CandidateIds, 0, PATINDEX('%|%',@CandidateIds))))
-- print @CandidateId
EXEC Proc @CandidateId,@FK_CorporateId,@FK_ProgramId,@Interview_DateTime,@Interview_Venue,@Interview_Remarks,@intInvitationStatus,@ReturnValue OUTPUT
SET @CandidateIds = SUBSTRING(@CandidateIds, LEN(CONVERT(VARCHAR(20),@CandidateId) + '|') + 1, LEN(@CandidateIds))

IF(@ReturnValue != 0 AND @ReturnValue != -1)
BEGIN
SET @DublicateCandidateIds = @DublicateCandidateIds + ',' + CONVERT(VARCHAR(20),@ReturnValue)
--print 'IF:' + @DublicateCandidateIds
END
END
ELSE
BEGIN
SET @CandidateId = CONVERT(BIGINT, @CandidateIds)
SET @CandidateIds = NULL
-- print @CandidateId
EXEC Proc_ @CandidateId,@FK_CorporateId,@FK_ProgramId,@Interview_DateTime,@Interview_Venue,@Interview_Remarks,@intInvitationStatus,@ReturnValue OUTPUT

IF(@ReturnValue != 0 AND @ReturnValue != -1)
BEGIN
SET @DublicateCandidateIds = @DublicateCandidateIds + ',' + CONVERT(VARCHAR(20),@ReturnValue)
--print 'ELSE:' + @DublicateCandidateIds
END
END
END

Procedure Parameter in where condition

WHERE (IsInterviewed = 0 OR IsInterviewed is null) 
AND ISNULL([Corporate-ShortListStatusForInterview],0) IN (2,3) -- Consolidated Candidate AND ((@strDistrictIds IS NOT NULL AND FK_DistrictId   IN (SELECT [ID] FROM  dbo.SplitIDs(@strDistrictIds))) OR (@strDistrictIds IS NULL))

AND(
((LEN(@Keywords) > 0 AND CP.FirstName  LIKE '%' + @Keywords + '%') OR (@Keywords IS NULL OR LEN(@Keywords) = 0))
OR ((LEN(@Keywords) > 0 AND CP.MiddleName LIKE '%' + @Keywords + '%') )
)
AND ((@ProgramId    IS NOT NULL AND P.ProgramId = @ProgramId)  OR (@ProgramId   IS NULL))

Getting error 405 (Method Not Allowed) while calling POST method of WEBAPI from Angular

Issue is due CORS, it should be enabled in WEBAPI project. The CORS enabling not works through WEB.config code, you need to enable CORS through backend.

Note - remove all web.config CORS enabling code if its there in web.config i.e the below code

    
       name="Access-Control-Allow-Origin" value="*" />
       name="Access-Control-Allow-Headers" value="Content-Type" />
       name="Access-Control-Allow-Methods" value="GET, POST, PUT, DELETE, OPTIONS" />
    

Steps 1. Install CORS package Install-Package Microsoft.AspNet.WebApi.Cors for WEBAPI project 2. In WebApiConfig.cs add this code
var corsAttribute = new EnableCorsAttribute("*","Origin, Content-Type, Accept",
                                               "GET, PUT, POST, DELETE, OPTIONS");
            config.EnableCors(corsAttribute);

Done.

JSON Maximum length problem with ASP.NET WebForm/MVC

http://forums.asp.net/t/1962449.aspx?JSON+Maximum+length+problem+with+ASP+NET

ASP.NET State Service for Windows 8

ASP.NET State Service is not installed by default in Windows 8 and should not be installed on a “home” system.
To enabled this navigate to Control Panel -> Programs -> Programs and Features ->  Turn Windows Features on or off. Select “ASP.NET 4.5″

Open Run (Win + R) and write “services.msc”

Here right click on “ASP.NET State Service” and press start.

sqlserver :delete duplicate records

WITH CTE (COl1,Col2, DuplicateCount) AS ( SELECT COl1,Col2, ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount FROM DuplicateRcordTable ) DELETE FROM CTE WHERE DuplicateCount > 1 GO

Foreigh key dependency

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME