Database is restoring for a long time no idea what is going on

 Database is restoring for a long time no idea what is going on

Try this

use master

RESTORE DATABASE [you database name] WITH RECOVERY;

Cannot drop database because it is currently in use

 Try find the lock or SPId using the database  using the following command

SP_WHO 

Then run

 kill <SPID>;

example kill 60; to kill the process.

Or the folling script also generate the kill script for the database 

DECLARE @DatabaseName nvarchar(50)

SET @DatabaseName = N'Your database name'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL ;

The database could not be exclusively locked to perform the operation. While renaming database

 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

While renaming database :( 

/*You might have seen "The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)" error when you try to rename SQL server database.  This error normally occurs when your database is in Multi User mode where users are accessing your database or some objects are referring to your database.

 Nothing much to do to resolve the issue. First set the database to single user mode and then try to rename the database and then set it back to Multi user mode./*

 --We will go through step by step.

--First we will see how to set the database to single user mode,

ALTER DATABASE OlddbName 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Now we will try to rename the database

ALTER DATABASE OlddbName MODIFY NAME = NewdbName


--Finally we will set the database to Multiuser mode


ALTER DATABASE NewdbName
SET MULTI_USER WITH ROLLBACK IMMEDIATE
 

--Hope you are able to rename your database without any issues now!!!

 

Kendo Serverside filters with ajax and mvc

Kendo Serverside filters with ajax and mvc 

why? when we don't want to load millions on records on in grid at once, so we need paging , 

but for filters/sorting we need to filter among the million records and show only pageSize records.

Kendo says to use the DataSourceRequest as below

public ActionResult Paging_Orders([DataSourceRequest] DataSourceRequest request)
{
    return Json(GetOrders().ToDataSourceResult(request));
}

but for multiple filters you may get the following errors

Solution

create a class dataSourceHelper.cs

  public class DataSourceHelper
    {
        public static DataSourceRequest GetDataSourceRequest(string filter, string sort,  string page, string pageSize)
        {
            DataSourceRequest request = new DataSourceRequest()
            {
                Page = Convert.ToInt32(page),
                PageSize = Convert.ToInt32(pageSize)
            };
            if (page == null)
                page = "1";
            if (pageSize == null)
                pageSize = "25";
            if (request.Filters == null && !string.IsNullOrEmpty(filter))
            {
                request.Filters = new List<Kendo.Mvc.IFilterDescriptor>();
                request.Filters.AddRange(getFilter(filter));
            }
            if (request.Sorts == null && !string.IsNullOrEmpty(sort))
            {
                request.Sorts = new List<Kendo.Mvc.SortDescriptor>();
                request.Sorts.AddRange(getSorts(sort));
            }
            return request;
        }
        private static IEnumerable<SortDescriptor> getSorts(string sort)
        {
            List<SortDescriptor> list = new List<SortDescriptor>();
            string[] sorts = sort.Split(new[] { "-and-" }, StringSplitOptions.None);
            foreach (string s in sorts)
            {
                string[] x = s.Split('-');
                if (x.Length == 2)
                    list.Add(new SortDescriptor() { Member = x[0], SortDirection = getDirection(x[1]) });
            }
            return list;
        }
        private static ListSortDirection getDirection(string dir)
        {
            switch (dir)
            {
                case "asc":
                    return ListSortDirection.Ascending;
                default:
                    return ListSortDirection.Descending;
            }
        }
        private static IEnumerable<IFilterDescriptor> getFilter(string filter)
        {
            List<IFilterDescriptor> list = new List<IFilterDescriptor>();
            string[] filters = filter.Split(new[] { "~and~" }, StringSplitOptions.None);
            foreach (string f in filters)
            {
                string[] x = f.Split('~');
                list.Add(new FilterDescriptor() { Member = x[0], MemberType = typeof(string), Operator = getFilterOperator(x[1]), Value = x[2].Trim(new char[] { (char)39 }) });
            }
            return list;
        }
        private static FilterOperator getFilterOperator(string str)
        {
            switch (str)
            {
                case "startswith":
                    return FilterOperator.StartsWith;
                case "eq":
                    return FilterOperator.IsEqualTo;
                case "neq":
                    return FilterOperator.IsNotEqualTo;
                default:
                    return FilterOperator.Contains;
            }
        }
    }

Also do not forget the reference dll using tags

using Kendo.Mvc;
using Kendo.Mvc.Extensions;
using Kendo.Mvc.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;

Usages

you can call the below datasource and bind it to your grid.

    var dataSource= new kendo.data.DataSource({

        type: "webapi",
        dataType: "json",        
        serverPaging: true,
        serverSorting: true,
        serverFiltering: true,
        pageSize: 500,
        transport: {
            read: {
                url: home + "/Action/GetDataMethod"
            }
        },
        schema: {
            total: "Total",
            data: "Data"
            
        }
        
    });

Controller

 public JsonResult GetDataMethod( string filter,string sort, string page, string pageSize)
        {
            DataSourceRequest request = new DataSourceRequest();
            request = DataSourceHelper.GetDataSourceRequest(filter, sort, page, pageSize);
           						
            var result = BusinessLogic.GetData();//your logic to get millions of data
            if (result != null)
            {
                var filterResult = result.Data.ToDataSourceResult(request);
                request.Page = 1;
                request.PageSize = result.Total;
                result.Total= (int)result.WorkQueueItems.ToDataSourceResult(request).Total;
                result.Data= (IEnumerable<DataView>)filterResult.Data;
            }
            return  Json(result, Constants.ResultTypes.ApplicationJson, System.Text.Encoding.Unicode, JsonRequestBehavior.AllowGet);
        } 

Note: Total is required to construct the paging section.

PL SQL Constraints with conditions

TABLE_NAME.category_Type ='ABC,DEF,GEF'

ANOTHER_TABLE.Value have ABC,DEF,GEF in different rows

I created a trigger to check each comma separated values should belong to ANOTHER_TABLE.

CREATE OR REPLACE  TRIGGER TRIGGER_NAME
 BEFORE  INSERT or UPDATE ON  TABLE_NAME
 REFERENCING OLD AS old    NEW AS new
FOR EACH ROW
BEGIN
DECLARE
  num_rows integer;
  dup_rows integer;
  temptype nvarchar2(100);
BEGIN
--  Check each comma separated values are from the ANOTHER_TABLE table where category ='Type'     
--  else throw error
select TO_CHAR( regexp_replace(TO_CHAR(:NEW.category_Type), '[[:space:]]*','')) into temptype from dual;

FOR i IN
   (SELECT trim(regexp_substr(temptype , '[^,]+', 1, LEVEL)) l
     FROM dual
       CONNECT BY LEVEL <= regexp_count(temptype , ',')+1
     )
   LOOP
 
SELECT
     count(1) into num_rows       
FROM   ANOTHER_TABLE where category ='Type' and value=i.l;

if(num_rows=0) then
      RAISE_APPLICATION_ERROR(-20000,'type did not match with expected ANOTHER_TABLE.value');
      end if;

    END LOOP;

  END;
END;