Check lock in sql server

set transaction isolation Level Repeatable read

begin transaction

select * from  [Tran]

rollback transaction

sp_lock

select OBJECT_name(949578421)

select * from sys.objects where object_id=949578421

1. If we lock

set transaction isolation Level Repeatable read

begin transaction

select * from  [Tran]

2. Checking with

sp_lock

3. Checking with object id (ObjId)

select OBJECT_name(949578421)

4. Checking with sys.Objects

select * from sys.objects where object_id=949578421 

5. To roll back locked status

rollback transaction

Create Login in MS Sql server

  https://www.youtube.com/watch?v=qfuK0V1tlrA

Start timer in javascript

Start timer in javascript

<script>

    $(document).ready(function () {

        CountDown();

    })

    function CountDown() {

        var tt ="23-Mar-22 10:26:34 AM";

        if (tt == null || tt == "") {

            $('#demo').show();

            document.getElementById("demo").innerHTML = "YOUR ID IS NOT ACTIVATED";

            return;

        }

        var countDownDate = new Date('23-Mar-22 10:26:34 AM').getTime();

        var x = setInterval(function () {

            //debugger;

            var now = new Date().getTime();

            var distance = countDownDate - now;

            var days = Math.floor(distance / (1000 * 60 * 60 * 24));

            var hours = Math.floor((distance % (1000 * 60 * 60 * 24)) / (1000 * 60 * 60));

            var minutes = Math.floor((distance % (1000 * 60 * 60)) / (1000 * 60));

            var seconds = Math.floor((distance % (1000 * 60)) / 1000);

                $('#demo').show();

                document.getElementById("demo").innerHTML = "YOUR ID WILL BE EXPIRE IN " + days + " Day " + hours + "h "

                + minutes + "m " + seconds + "s ";

            if (distance < 0) {

                clearInterval(x);

                document.getElementById("demo").innerHTML = "YOUR ID IS EXPIRED";

            }

        }, 1000);

    }

</script>

 

Delete duplicate row in sql server

1. Delete duplicate row in sql server with CTC

With CTE as  

(  

Select Id,names,row_number() over (partition by id order by id) as rno from ##abc

)  

Select * from CTE order by id,names

With CTE as  

(  

Select Id,names,row_number() over (partition by id order by id) as rno from ##abc

)  

delete from CTE where rno>1

 

Fill data with stored procedure in data table

1. Fill data into data table with stored procedure

_param = new SqlParameter[]

{  new SqlParameter("@SearchTerm",search),

    new SqlParameter("@search",""),

    new SqlParameter("@mobile",""),

   new SqlParameter("@topup",objmodel.user_type),

   new SqlParameter("@fromdate",objmodel.date),

   new SqlParameter("@todate",objmodel.todate),

   new SqlParameter("@PageIndex",1),

   //new SqlParameter("@PageSize",objmodel.pageSize),

   new SqlParameter("@RecordCount", SqlDbType.Int, 4) { Direction=ParameterDirection.Output}

};

_dt = _sf.returnDtBy_proc("GetCustomers_Pager", _param);

m1.totalCount = _param[7].Value.ToString();

if (_dt.Rows.Count > 0){

   

}

OPTION(MAXRECURSION 0)

 

--DECLARE @Min int;  

--DECLARE @Max int;  

--SET @Max = 10;  

--SET @Min = 1;  

--WITH Sequence_ AS(SELECT @Min AS num UNION ALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max)  

--SELECT num FROM Sequence_  

--DECLARE @Min int;  

--DECLARE @Max int;  

--SET @Max = 110;  

--SET @Min = 1;  

--WITH Sequence_ AS(SELECT @Min AS num UNION ALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max)  

--SELECT num FROM Sequence_  

DECLARE @Min int;  

DECLARE @Max int;  

SET @Max = 150;  

SET @Min = 1;  

WITH Sequence_ AS(SELECT @Min AS num UNION ALL SELECT num + 1 FROM Sequence_ WHERE num + 1 <= @Max)  

SELECT num FROM Sequence_  

OPTION(MAXRECURSION 32767)

--OPTION(MAXRECURSION 32768)

--OPTION(MAXRECURSION 0)

Store Procedure with Output Parameter

 Store Procedure  with Output Parameter

Stored procedure with Encryption

Table Valued Function

--Create

create function funTableValued()

returns table

as

return(

select * from test1.dbo.Employee

)

--Execute

select * from dbo.funTableValued()

Scaler Valued Functions

--Create

create function funScalerValued()

returns numeric(18,2)

as

begin

declare @num numeric(18,2)

select @num=count(*) from test1.dbo.Employee

return @num

end

--Execute

select dbo.funScalerValued()