WCF Service Web.Config File Setting.

<configuration>
<connectionStrings>

<add name="WebEntities" connectionString="metadata=res://*/Web.csdl|res://*/Web.ssdl|res://*/Web.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=local;initial catalog=DBName;user id=sa;password=Pass@123;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"/>

</connectionStrings>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
<add key="SecurityKey" value="Key123"/>
<add key="ShapeUrl" value="http://Web.in/Path/"/>
</appSettings>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
</system.web>
<system.serviceModel>
<behaviors>
<serviceBehaviors>
<behavior name="">
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
</behavior>
</serviceBehaviors>
</behaviors>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true"
 multipleSiteBindingsEnabled="true" />
</system.serviceModel>
</configuration>

Get Json Result From WebService using C#

               var client = new RestClient();
                client.EndPoint = @"URL";
                client.Method = HttpVerb.POST;
                client.PostData = "{postData: value}";
                var json = client.MakeRequest();
                XmlDocument xmlData = JsonToXML(json);--Json To XML

------------------------------------------------------------------
public XmlDocument JsonToXML(string json)
        {
            XmlDocument doc = new XmlDocument();

            using (var reader = JsonReaderWriterFactory.CreateJsonReader(Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max))
            {
                XElement xml = XElement.Load(reader);
                doc.LoadXml(xml.ToString());
            }

            return doc;
        }
--------------------------------------------CLASS Rest Client------------------------------------------
using System;
using System.IO;
using System.Net;
using System.Text;

public enum HttpVerb
{
    GET,
    POST,
    PUT,
    DELETE
}

namespace HttpUtils
{
    public class RestClient
    {
        public string EndPoint { get; set; }
        public HttpVerb Method { get; set; }
        public string ContentType { get; set; }
        public string PostData { get; set; }

        public RestClient()
        {
            EndPoint = "";
            Method = HttpVerb.GET;
            ContentType = "text/xml";
            PostData = "";
        }
        public RestClient(string endpoint)
        {
            EndPoint = endpoint;
            Method = HttpVerb.GET;
            ContentType = "text/xml";
            PostData = "";
        }
        public RestClient(string endpoint, HttpVerb method)
        {
            EndPoint = endpoint;
            Method = method;
            ContentType = "text/xml";
            PostData = "";
        }

        public RestClient(string endpoint, HttpVerb method, string postData)
        {
            EndPoint = endpoint;
            Method = method;
            ContentType = "text/xml";
            PostData = postData;
        }


        public string MakeRequest()
        {
            return MakeRequest("");
        }

        public string MakeRequest(string parameters)
        {
            var request = (HttpWebRequest)WebRequest.Create(EndPoint + parameters);

            request.Method = Method.ToString();
            request.ContentLength = 0;
            request.ContentType = ContentType;

            if (!string.IsNullOrEmpty(PostData) && Method == HttpVerb.POST)
            {
                var encoding = new UTF8Encoding();
                var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(PostData);
                request.ContentLength = bytes.Length;

                using (var writeStream = request.GetRequestStream())
                {
                    writeStream.Write(bytes, 0, bytes.Length);
                }
            }

            using (var response = (HttpWebResponse)request.GetResponse())
            {
                var responseValue = string.Empty;

                if (response.StatusCode != HttpStatusCode.OK)
                {
                    var message = String.Format("Request failed. Received HTTP {0}", response.StatusCode);
                    throw new ApplicationException(message);
                }

                // grab the response
                using (var responseStream = response.GetResponseStream())
                {
                    if (responseStream != null)
                        using (var reader = new StreamReader(responseStream))
                        {
                            responseValue = reader.ReadToEnd();
                        }
                }

                return responseValue;
            }
        }

    } // class

}

Create Window Service Setup Using Cmd prompt(.bat file)

-----------------------------For Install-------------------------------------------------
@ECHO OFF

echo Installing WindowsService...
echo ---------------------------------------------------
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil /i C:\RapnetService\Debug\ImportRapnetData.exe
echo ---------------------------------------------------
echo Service Install Done Successfully.
pause
---------------------------------For Uninstall---------------------------------------------
@ECHO OFF

echo Installing WindowsService...
echo ---------------------------------------------------
C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\InstallUtil /u C:\RapnetService\Debug\ImportRapnetData.exe
echo ---------------------------------------------------
echo  Service UnInstall Done Successfully.
pause

Marge column in table using angular

-------------------------Controller----------------------------------------------
[HttpGet]
        public JsonResult GetColorData()
        {
            List<String> _List = new List<String>();
            try
            {
                _List = GetList();

                var groupedColorList = _List.GroupBy(u => u.Color)
                                                     .Select(grp => new { Color = grp.Key, Colors = grp.ToList() })
                                                     .ToList();

                var jsonResult = Json(new
                {
                    ColorList = groupedColorList
                }, JsonRequestBehavior.AllowGet);
                jsonResult.MaxJsonLength = Int32.MaxValue;
                return jsonResult;
            }
            catch (Exception ex)
            {
                return Json(new
                    {
                        ColorList = _List
                    }, JsonRequestBehavior.AllowGet);
            }
        }
---------------------------------------------View------------------------------------------------
   <tbody ng-repeat="group in ColorList ">
                        <tr ng-repeat="x in group.Colors">
                            <td rowspan="{{group.Colors.length}}" ng-hide="$index>0">{{x.Color}}
                            </td>
                         </tr>
<tbody>
------------------------------------Module------------------------------
 $scope.ColorList = [];
angular.element(document).ready(function () {
           
            loaderShow();
            $http({
                method: "Get",
                url: '@Url.Action("GetColorData", "Color")',
                dataType: "json",
                contentType: "application/json; charset=utf-8",
            }).success(function (response) {
                $scope.ColorList = response.ColorList ;

            });
            loaderHide();
        });
 

Output table from database or SP directly into excel file using SQL.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [SP_EXCEL_EXPORT_FORMAT]
    (
      @dbName VARCHAR(100) = '[1308]--DataBaseName' ,
      @sql VARCHAR(8000) = 'SELECT Employee_ID,Employee_Name,Employee_Address,Employee_City FROM dbo.Employee_Master' ,
      @fullFileName VARCHAR(100) = 'D:\BhavdipTala.xls'
    )
AS
    BEGIN
        IF @sql = ''
            OR @fullFileName = ''
            BEGIN
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
-- if DB isn't passed in set it to master
        SELECT  @dbName = 'use ' + @dbName + ';'
--print 'use'
        IF OBJECT_ID('##TempExportData') IS NOT NULL
            DROP TABLE ##TempExportData
        IF OBJECT_ID('##TempExportData2') IS NOT NULL
            DROP TABLE ##TempExportData2
-- insert data into a global temp table
        DECLARE @columnNames VARCHAR(8000) ,
            @columnConvert VARCHAR(8000) ,
            @tempSQL VARCHAR(8000)

        SELECT  @tempSQL = LEFT(@sql, CHARINDEX('from', @sql) - 1)
                + ' into ##TempExportData ' + SUBSTRING(@sql,
                                                        CHARINDEX('from', @sql)
                                                        - 1, LEN(@sql))
--print '##TempExportData'
        EXEC(@dbName + @tempSQL)
        IF @@error > 0
            BEGIN
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
        SELECT  @columnNames = COALESCE(@columnNames + ',', '') + '['
                + column_name + ']' ,
                @columnConvert = COALESCE(@columnConvert + ',', '')
                + 'convert(nvarchar(4000),' + '[' + column_name + ']'
                + CASE WHEN data_type IN ( 'datetime', 'smalldatetime' )
                       THEN ',121'
                       WHEN data_type IN ( 'numeric', 'decimal' ) THEN ',128'
                       WHEN data_type IN ( 'float', 'real', 'money',
                                           'smallmoney' ) THEN ',2'
                       WHEN data_type IN ( 'datetime', 'smalldatetime' )
                       THEN ',120'
                       ELSE ''
                  END + ') as ' + '[' + column_name + ']'
        FROM    tempdb.INFORMATION_SCHEMA.Columns
        WHERE   table_name = '##TempExportData'

--print 'columnNames: ' + @columnNames
--print 'columnConvert: ' + @columnConvert

-- execute select query to insert data and column names into new temp table
        SELECT  @sql = 'select ' + @columnNames
                + ' into ##TempExportData2 from (select ' + @columnConvert
                + ', ''2'' as [temp##SortID] from ##TempExportData union all select '''
                + REPLACE(REPLACE(REPLACE(@columnNames, ',', ''', '''), '[',
                                  ''), ']', '')
                + ''', ''1'') t order by [temp##SortID]'

--print '##TempExportData2'

        EXEC (@sql)
        PRINT @sql
-- build full BCP query
        DECLARE @bcpCommand VARCHAR(8000)
        SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'
        SET @bcpCommand = @bcpCommand + ' ' + @fullFileName
            + ' -w -T -U sa -P sa","-CRAW'
        EXEC master..xp_cmdshell @bcpCommand
--print 'bcpCommand'
        IF @@error > 0
            BEGIN
--print 'error>0'
                SELECT  0 AS ReturnValue -- failure
                RETURN
            END
        DROP TABLE ##TempExportData
        DROP TABLE ##TempExportData2
        SET @columnNames = ' '
        SET @columnConvert = ' '
        SET @tempSQL = ' '
        SELECT  1 AS ReturnValue

    END

How can call stored procedure inside function in SQL server ?

 DECLARE @SQL VARCHAR(500)

 SELECT  @SQL = 'osql -S' + @@servername
                                + ' -E -q "exec [DataBaseName].[dbo].StoreProcedureName"'

EXEC master..xp_cmdshell @SQL

How to display a custom filter in the Dev Express Grid Control column filter popup.

 public static void ShowFilterPopupCheckedListBox(GridView gridView1, object sender, FilterPopupCheckedListBoxEventArgs e)
        {
            e.CheckedComboBox.BeginUpdate();
            try
            {
                gridView1.OptionsFilter.AllowColumnMRUFilterList = false;
                e.CheckedComboBox.Items.Clear();
                List<string> Availble = new List<string>();
           
                for (int i = 0; i < gridView1.RowCount; i++)
                {
                    try
                    {
                        DevExpress.XtraGrid.Views.Grid.FilterItem f = new DevExpress.XtraGrid.Views.Grid.FilterItem(gridView1.GetRowCellValue(i, e.Column.FieldName).ToString(), gridView1.GetRowCellValue(i, e.Column.FieldName).ToString());
                        if (i == 0)
                        {
                            Availble.Add(f.Text);
                            e.CheckedComboBox.Items.Add(f);
                        }
                        if (!Availble.Contains(f.Text))
                        {
                            Availble.Add(f.Text);
                            e.CheckedComboBox.Items.Add(f);
                        }

                    }
                    catch (Exception Ex)
                    {
                    }
                }
                Availble.Sort();
                e.CheckedComboBox.Items.Clear();
                for (int i = 0; i < Availble.Count; i++)
                {
                    DevExpress.XtraGrid.Views.Grid.FilterItem f = new DevExpress.XtraGrid.Views.Grid.FilterItem(Availble[i].ToString(), Availble[i].ToString());
                    e.CheckedComboBox.Items.Add(f);
                }
            }
            finally
            {
                e.CheckedComboBox.EndUpdate();
            }
        }

How to get list of all Folder in network sharing path using SQL Server.

   IF OBJECT_ID('tempdb..#TempTableName') IS NOT NULL
    DROP TABLE #TempTableName ;

   CREATE TABLE #TempTableName
    (
      id INT IDENTITY(1, 1) ,
      subdirectory NVARCHAR(512) ,
      depth INT
    ) ;

   INSERT   #TempTableName
            ( subdirectory
                       
            )
            EXEC MASTER.dbo.xp_cmdshell 'dir DriveName:\  /b /o:n /ad'
   IF NOT EXISTS ( SELECT   1
                   FROM     #TempTableName )
    BEGIN
        EXEC XP_CMDSHELL 'net use DriveName: \\ServerName\DriveName /user:domain\USERNAME PASSWORD'---code for Mapping drive in local Pc
    END

    

How to convert records in a table to XML format using T-SQL?

DECLARE @stSqlString NVARCHAR(MAX)  = ''
DECLARE @stStrXml XML  

           
SET @stSqlString = 'set @stStrXml=(select *  from TableName FOR XML AUTO, elements)'
EXEC sp_executesql @stSqlString, N'@stStrXml XML OUTPUT',@stStrXml OUTPUT

 SELECT  @stStrXml

Six important .NET concepts: Stack, heap, value types, reference types, boxing, and unboxing

What goes inside when you declare a variable?

When you declare a variable in a .NET application, it allocates some chunk of memory in the RAM. This memory has three things: the name of the variable, the data type of the variable, and the value of the variable.
That was a simple explanation of what happens in the memory, but depending on the data type, your variable is allocated that type of memory. There are two types of memory allocation: stack memory and heap memory. In the coming sections, we will try to understand these two types of memory in more detail.

Stack and heap

In order to understand stack and heap, let’s understand what actually happens in the below code internally.

Hide   Copy Code

public void Method1()
{
    // Line 1
    int i=4;

    // Line 2
    int y=2;

    //Line 3
    class1 cls1 = new class1();
}

It’s a three line code, let’s understand line by line how things execute internally.

• Line 1: When this line is executed, the compiler allocates a small amount of memory in the stack. The stack is responsible for keeping track of the running memory needed in your application.
• Line 2: Now the execution moves to the next step. As the name says stack, it stacks this memory allocation on top of the first memory allocation. You can think about stack as a series of compartments or boxes put on top of each other.
Memory allocation and de-allocation is done using LIFO (Last In First Out) logic. In other words memory is allocated and de-allocated at only one end of the memory, i.e., top of the stack.

• Line 3: In line 3, we have created an object. When this line is executed it creates a pointer on the stack and the actual object is stored in a different type of memory location called ‘Heap’. ‘Heap’ does not track running memory, it’s just a pile of objects which can be reached at any moment of time. Heap is used for dynamic memory allocation.

One more important point to note here is reference pointers are allocated on stack. The statement, Class1 cls1; does not allocate memory for an instance of Class1, it only allocates a stack variable cls1 (and sets it to null). The time it hits the new keyword, it allocates on "heap".
Exiting the method (the fun): Now finally the execution control starts exiting the method. When it passes the end control, it clears all the memory variables which are assigned on stack. In other words all variables which are related to int data type are de-allocated in ‘LIFO’ fashion from the stack.
The big catch – It did not de-allocate the heap memory. This memory will be later de-allocated by the garbage collector.

Now many of our developer friends must be wondering why two types of memory, can’t we just allocate everything on just one memory type and we are done?
If you look closely, primitive data types are not complex, they hold single values like ‘int i = 0’. Object data types are complex, they reference other objects or other primitive data types. In other words, they hold reference to other multiple values and each one of them must be stored in memory. Object types need dynamic memory while primitive ones needs static type memory. If the requirement is of dynamic memory, it’s allocated on the heap or else it goes on a stack.

Value types and reference types

Now that we have understood the concept of Stack and Heap, it’s time to understand the concept of value types and reference types. Value types are types which hold both data and memory on the same location. A reference type has a pointer which points to the memory location.
Below is a simple integer data type with name i whose value is assigned to another integer data type with name j. Both these memory values are allocated on the stack.
When we assign the int value to the other int value, it creates a completely different copy. In other words, if you change either of them, the other does not change. These kinds of data types are called as ‘Value types’.

When we create an object and when we assign an object to another object, they both point to the same memory location as shown in the below code snippet. So when we assign obj to obj1, they both point to the same memory location.
In other words if we change one of them, the other object is also affected; this is termed as ‘Reference types’.

So which data types are ref types and which are value types?

In .NET depending on the data type, the variable is either assigned on the stack or on the heap. ‘String’ and ‘Objects’ are reference types, and any other .NET primitive data types are assigned on the stack. The figure below explains the same in a more detail manner.

Boxing and unboxing

Wow, you have given so much knowledge, so what’s the use of it in actual programming? One of the biggest implications is to understand the performance hit which is incurred due to data moving from stack to heap and vice versa.
Consider the below code snippet. When we move a value type to reference type, data is moved from the stack to the heap. When we move a reference type to a value type, the data is moved from the heap to the stack.
This movement of data from the heap to stack and vice-versa creates a performance hit.
When the data moves from value types to reference types, it is termed ‘Boxing’ and the reverse is termed ‘UnBoxing’.

If you compile the above code and see the same in ILDASM, you can see in the IL code how ‘boxing’ and ‘unboxing’ looks. The figure below demonstrates the same.

Performance implication of boxing and unboxing

In order to see how the performance is impacted, we ran the below two functions 10,000 times. One function has boxing and the other function is simple. We used a stop watch object to monitor the time taken.
The boxing function was executed in 3542 ms while without boxing, the code was executed in 2477 ms. In other words try to avoid boxing and unboxing. In a project where you need boxing and unboxing, use it when it’s absolutely necessary.
With this article, sample code is attached which demonstrates this performance implication.

Currently I have not included source code for unboxing but the same holds true for it. You can write code and experiment it using the stopwatch class.

Move Folder One Drive To Other Drive Using Sql

       DECLARE @ToDirectory VARCHAR(500)
        SET @ToDirectory ="C:\FolderName"

        DECLARE @FromDirectory VARCHAR(500)
        SET @FromDirectory = "D:\"

        DECLARE @CopyFromToQuery VARCHAR(500)
        SET @CopyFromToQuery ='robocopy  '+@FromDirectory + ' ' + @ToDirectory   + ' /E /MOVE'

        PRINT @CopyFromToQuery
       
        EXEC master..xp_cmdshell @CopyFromToQuery

Magento API soap call in C#: Integration

Adding a Web Reference:

 First, create a new Asp.net C# web application. Then right click on the project and select 'Add service Reference'.

After selecting 'Add Service Reference'. You will get a new window called 'Add Service Reference'. Click on 'Advanced' button.

Then, you will another window called 'Service Reference Settings'. Click on 'Add Web Reference' button.

Give your Magento hosted URL.Then click on 'Go' button to get the Magento Service.
There are two SOAP version in Magento. I have used version 2 here.
EX:
SOAP v1 url: http://Hostname/api/soap/?wsdl=1
SOAP v2 url: http://Hostname/api/v2_soap/?wsdl=

 

On successful, you will get the reference in your project as like below.

What should I do when SQL Server query is stuck ?( it Help for Solve SQL Query Time Out Error)

First, open SQL Server Activity Monitor, you should wait some time before data is loaded

The Activity Monitor is available in SQL Server 2008 Management Studio is a great tool which can be used to get a quick overview of SQL Server 2008 system performance. The Activity Monitor tool in the previous version of SQL Server used to display information related to processes, lock by objects and locks by process. There are many enhancements in Activity Monitor in SQL Server 2008 like a graphical display of processor time, waiting tasks, database I/O's, batch requests, processes, resource waits, data file I/O's and also information about the most expensive queries.
To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission

Plz Show Recent Expensive Queries( it Help for SQL  Time Out Error Solve)

One of the first lines of defense in determining why a query is stuck is to use sp_who2. It will shows all the sessions that are currently established in the database. These are denoted as SPID’s, or server process Id’s.

sp_who2

The first 50 results are system SPIDs. Generally these do not effect slowdowns of the system. These system events include the Checkpoint writer, Log writers and task schedulers. User processes are SPID numbers 50 and over. In diagnosing slowdowns it is these SPIDs that are the potential resource hogs.

There are four main things to look for when when diagnosing slowdowns:

• Blocking
• High CPU usage
• High IO usage
• Multiple entries for the same SPID (representing parallelism)

When a number is shown in the column named BlkBy, this represents the SPID that is currently stopping the SPID in the row shown. Sometimes many rows will show SPID numbers in the BlkBy column. This is because there is a chain of blockers. The way this occurs usually starts with one “lead” blocker blocking another process. In turn, the process that is being blocked, blocks others. This occurs down a chain. It can be a messy situation. In order to rectify, you may have to kill the lead blocker. If it happens often, you will want to research why this particular process is blocking. So, before you kill any process, find out what statement it is running first. To do this, execute DBCC INPUTBUFFER

DBCC INPUTBUFFER(500) -- 500 represent the SPID you see in the result of sp_who2

Once you find the blocker, you may need to kill it. To do so use the kill command (replace the number below with the blocking SPID)
IMPORTANT: before the kill, keep a copy of the SQL statement being run for later investigation

KILL 500

sp_who2 does provide limited information regarding slowdowns.

For more information use the following script that shows the SQL statement being run, only sessions that have a current executing request, reads and writes for the current command, along with the number of reads and writes for the entire SPID and the protocol being used (TCP, NamedPipes, or Shared Memory)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  SPID = er.session_id ,
        BlkBy = er.blocking_session_id ,
        ElapsedMS = er.total_elapsed_time ,
        CPU = er.cpu_time ,
        IOReads = er.logical_reads + er.reads ,
        IOWrites = er.writes ,
        Executions = ec.execution_count ,
        CommandType = er.command ,
        ObjectName = OBJECT_SCHEMA_NAME(qt.objectid, dbid) + '.'
        + OBJECT_NAME(qt.objectid, qt.dbid) ,
        SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                 ( CASE WHEN er.statement_end_offset = -1
                                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                             * 2
                                        ELSE er.statement_end_offset
                                   END - er.statement_start_offset ) / 2) ,
        Status = ses.status ,
        [Login] = ses.login_name ,
        HOST = ses.host_name ,
        DBName = DB_NAME(er.database_id) ,
        LastWaitType = er.last_wait_type ,
        StartTime = er.start_time ,
        Protocol = con.net_transport ,
        transaction_isolation = CASE ses.transaction_isolation_level
                                  WHEN 0 THEN 'Unspecified'
                                  WHEN 1 THEN 'Read Uncommitted'
                                  WHEN 2 THEN 'Read Committed'
                                  WHEN 3 THEN 'Repeatable'
                                  WHEN 4 THEN 'Serializable'
                                  WHEN 5 THEN 'Snapshot'
                                END ,
        ConnectionWrites = con.num_writes ,
        ConnectionReads = con.num_reads ,
        ClientAddress = con.client_net_address ,
        Authentication = con.auth_scheme
FROM    sys.dm_exec_requests er
        LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
        LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
        OUTER APPLY ( SELECT    execution_count = MAX(cp.usecounts)
                      FROM      sys.dm_exec_cached_plans cp
                      WHERE     cp.plan_handle = er.plan_handle
                    ) ec
ORDER BY er.blocking_session_id DESC ,
        er.logical_reads + er.reads DESC ,
        er.session_id

The following script will show the blocking processes (lead blocker)

   
SELECT  spid ,
        sp.status ,
        loginame = SUBSTRING(loginame, 1, 12) ,
        hostname = SUBSTRING(hostname, 1, 12) ,
        blk = CONVERT(CHAR(3), blocked) ,
        open_tran ,
        dbname = SUBSTRING(DB_NAME(sp.dbid), 1, 10) ,
        cmd ,
        waittype ,
        waittime ,
        last_batch ,
        SQLStatement = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                 ( CASE WHEN er.statement_end_offset = -1
                                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                             * 2
                                        ELSE er.statement_end_offset
                                   END - er.statement_start_offset ) / 2)
FROM    master.dbo.sysprocesses sp
        LEFT JOIN sys.dm_exec_requests er ON er.session_id = sp.spid
        OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE   spid IN ( SELECT    blocked
                  FROM      master.dbo.sysprocesses )
        AND blocked = 0

The following script will find most expensive queries (remark the needed ORDER BY)


SELECT DISTINCT TOP 10
        t.TEXT QueryName ,
        s.execution_count AS ExecutionCount ,
        s.max_elapsed_time AS MaxElapsedTime ,
        ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime ,
        s.creation_time AS LogCreatedOn ,
        ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC

LETS CHECK, do the following steps and run the above scripts when needed

-- open new query window (SESSION A) and run the following 3 lines USE [AdventureWorks] BEGIN TRANSACTION UPDATE  Sales.SalesOrderDetail SET     [UnitPrice] = 1  -- this update takes ~30sec -- while the above update in progress, open a new query window (SESSION B) and run sp_who2 -- open another query window (SESSION C) and run the following line, it will be blocked by SESSION A because the transaction there is not committed yet UPDATE  Sales.SalesOrderDetail SET     [UnitPrice] = 2 -- while the above update in progress, go to SESSION B query window and run again sp_who2, you will see the blocking -- return to SESSION A query window and rollback the transaction. This will free the blocking in SESSION C (after the update finishes, takes ~30sec) ROLLBACK

If you find this solution useful,. Please Like and comment of page. Thanks!

Table Header Fix Js

DownLoad  Js :- https://drive.google.com/open?id=0B3Ios8rotFsYUmQyV193MjdUQncDow

 <script src="../tableHeadFixer.js"></script>

  $("#tableOne").tableHeadFixer({ 'left': 1, 'foot': true });

means :- Left Side Column 1 Fix Posstion

Find Location using latitude and longitude in SQL SERVER

   Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);

    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get', 'http://maps.google.com/maps/api/geocode/xml?latlng=72.8756324,72.8756324&sensor=false','false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object
   
 

Displaying headers and footers in a PDF generated by Rotativa(MVC)

string header = Server.MapPath("~/bin/PrintHeader.html");//Path of PrintHeader.html File
string footer = Server.MapPath("~/bin/PrintFooter.html");//Path of PrintFooter.html File

            string customSwitches = string.Format("--header-html  \"{0}\" " +
                                   "--header-spacing \"0\" " +
                                   "--footer-html \"{1}\" " +
                                   "--footer-spacing \"10\" " +
                                   "--footer-font-size \"10\" " +
                                   "--header-font-size \"10\" ", header, footer);

            return new Rotativa.ViewAsPdf("_PDF_Desgin.cshtml", List)//_PDF_Desgin.cshtml is control Page
            {
                FileName = "PdfFileName.pdf",
                PageSize = Size.A4,
                PageOrientation = Orientation.Landscape,
                CustomSwitches = customSwitches
            };

-----------------------------------PrintHeader PAGE--------------------------------------------------
<!DOCTYPE html>
<html><head><script>
function subst() {
    var vars={};
    var x=document.location.search.substring(1).split('&');
    for(var i in x) {var z=x[i].split('=',2);vars[z[0]] = unescape(z[1]);}
    var x=['frompage','topage','page','webpage','section','subsection','subsubsection'];
    for(var i in x) {
        var y = document.getElementsByClassName(x[i]);
        for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
    }
}
</script>
</head><body style="border: 0; margin: 0;" onload="subst()">

    <table style="width: 100%">
        <tr>
            <td colspan="2" align="center">My Header Name</td>
        </tr>
        <tr>
            <td colspan="2" align="right">DATE :-11/05/2015</td>
        </tr>
        <tr>
            <td colspan="2" align="right">Page <span class="page"></span>of <span class="topage"></span></td>
        </tr>
    </table>

</body>
</html>
-------------------------------------------END-----------------------------------------------------------
-----------------------------------PrintFooter PAGE--------------------------------------------------
<!DOCTYPE html>
<html>
<body>

<table>
  <tr>
    <td colspan="2">My Header Name</td>
  </tr>
  <tr>
    <td colspan="2">DATE :-11/05/2016</td>
  </tr>
  <tr>
    <td colspan="2">Page 1 of 10</td>
  </tr>
</table>

</body>
</html>
-------------------------------------------END-----------------------------------------------------------

 -------------------------------BASIC INFORMATION-------------------------------------------------          

Reduced Functionality

Some versions of wkhtmltopdf are compiled against a version of QT without the wkhtmltopdf patches. These versions are missing some features, you can find out if your version of wkhtmltopdf is one of these by running wkhtmltopdf --version if your version is against an unpatched QT, you can use the static version to get all functionality.
Currently the list of features only supported with patch QT includes:

• Printing more then one HTML document into a PDF file.
• Running without an X11 server.
• Adding a document outline to the PDF file.
• Adding headers and footers to the PDF file.
• Generating a table of contents.
• Adding links in the generated PDF file.
• Printing using the screen media-type.
• Disabling the smart shrink feature of webkit.

License

Copyright (C) 2008,2009 Wkhtmltopdf Authors.
License GPLv3+: GNU GPL version 3 or later http://gnu.org/licenses/gpl.html. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law.

Authors

Written by Jakob Truelsen. Patches by Mário Silva, Benoit Garret and Emmanuel Bouthenot.

Synopsis

wkhtmltopdf [OPTIONS]... <input file> [More input files] <output file>

General Options

	--allow	<path>	Allow the file or files from the specified folder to be loaded (repeatable)
-b,	--book*		Set the options one would usually set when printing a book
	--collate		Collate when printing multiple copies
	--cookie	<name> <value>	Set an additional cookie (repeatable)
	--cookie-jar	<path>	Read and write cookies from and to the supplied cookie jar file
	--copies	<number>	Number of copies to print into the pdf file (default 1)
	--cover*	<url>	Use html document as cover. It will be inserted before the toc with no headers and footers
	--custom-header	<name> <value>	Set an additional HTTP header (repeatable)
	--debug-javascript		Show javascript debugging output
-H,	--default-header*		Add a default header, with the name of the page to the left, and the page number to the right, this is short for: --header-left='[webpage]' --header-right='[page]/[toPage]' --top 2cm --header-line
	--disable-external-links*		Do no make links to remote web pages
	--disable-internal-links*		Do no make local links
-n,	--disable-javascript		Do not allow web pages to run javascript
	--disable-pdf-compression*		Do not use lossless compression on pdf objects
	--disable-smart-shrinking*		Disable the intelligent shrinking strategy used by WebKit that makes the pixel/dpi ratio none constant
	--disallow-local-file-access		Do not allowed conversion of a local file to read in other local files, unless explecitily allowed with --allow
-d,	--dpi	<dpi>	Change the dpi explicitly (this has no effect on X11 based systems)
	--enable-plugins		Enable installed plugins (such as flash
	--encoding	<encoding>	Set the default text encoding, for input
	--extended-help		Display more extensive help, detailing less common command switches
	--forms*		Turn HTML form fields into pdf form fields
-g,	--grayscale		PDF will be generated in grayscale
-h,	--help		Display help
	--htmldoc		Output program html help
	--ignore-load-errors		Ignore pages that claimes to have encountered an error during loading
-l,	--lowquality		Generates lower quality pdf/ps. Useful to shrink the result document space
	--manpage		Output program man page
-B,	--margin-bottom	<unitreal>	Set the page bottom margin (default 10mm)
-L,	--margin-left	<unitreal>	Set the page left margin (default 10mm)
-R,	--margin-right	<unitreal>	Set the page right margin (default 10mm)
-T,	--margin-top	<unitreal>	Set the page top margin (default 10mm)
	--minimum-font-size	<int>	Minimum font size (default 5)
	--no-background		Do not print background
-O,	--orientation	<orientation>	Set orientation to Landscape or Portrait
	--page-height	<unitreal>	Page height (default unit millimeter)
	--page-offset*	<offset>	Set the starting page number (default 1)
-s,	--page-size	<size>	Set paper size to: A4, Letter, etc.
	--page-width	<unitreal>	Page width (default unit millimeter)
	--password	<password>	HTTP Authentication password
	--post	<name> <value>	Add an additional post field (repeatable)
	--post-file	<name> <path>	Post an aditional file (repeatable)
	--print-media-type*		Use print media-type instead of screen
-p,	--proxy	<proxy>	Use a proxy
-q,	--quiet		Be less verbose
	--read-args-from-stdin		Read command line arguments from stdin
	--readme		Output program readme
	--redirect-delay	<msec>	Wait some milliseconds for js-redirects (default 200)
	--replace*	<name> <value>	Replace [name] with value in header and footer (repeatable)
	--stop-slow-scripts		Stop slow running javascripts
	--title	<text>	The title of the generated pdf file (The title of the first document is used if not specified)
-t,	--toc*		Insert a table of content in the beginning of the document
	--use-xserver*		Use the X server (some plugins and other stuff might not work without X11)
	--user-style-sheet	<url>	Specify a user style sheet, to load with every page
	--username	<username>	HTTP Authentication username
-V,	--version		Output version information an exit
	--zoom	<float>	Use this zoom factor (default 1)

Items marked * are only available using patched QT.

Headers And Footer Options

	--footer-center*	<text>	Centered footer text
	--footer-font-name*	<name>	Set footer font name (default Arial)
	--footer-font-size*	<size>	Set footer font size (default 11)
	--footer-html*	<url>	Adds a html footer
	--footer-left*	<text>	Left aligned footer text
	--footer-line*		Display line above the footer
	--footer-right*	<text>	Right aligned footer text
	--footer-spacing*	<real>	Spacing between footer and content in mm (default 0)
	--header-center*	<text>	Centered header text
	--header-font-name*	<name>	Set header font name (default Arial)
	--header-font-size*	<size>	Set header font size (default 11)
	--header-html*	<url>	Adds a html header
	--header-left*	<text>	Left aligned header text
	--header-line*		Display line below the header
	--header-right*	<text>	Right aligned header text
	--header-spacing*	<real>	Spacing between header and content in mm (default 0)

Items marked * are only available using patched QT.

Table Of Content Options

	--toc-depth*	<level>	Set the depth of the toc (default 3)
	--toc-disable-back-links*		Do not link from section header to toc
	--toc-disable-links*		Do not link from toc to sections
	--toc-font-name*	<name>	Set the font used for the toc (default Arial)
	--toc-header-font-name*	<name>	The font of the toc header (if unset use --toc-font-name)
	--toc-header-font-size*	<size>	The font size of the toc header (default 15)
	--toc-header-text*	<text>	The header text of the toc (default Table Of Contents)
	--toc-l1-font-size*	<size>	Set the font size on level 1 of the toc (default 12)
	--toc-l1-indentation*	<num>	Set indentation on level 1 of the toc (default 0)
	--toc-l2-font-size*	<size>	Set the font size on level 2 of the toc (default 10)
	--toc-l2-indentation*	<num>	Set indentation on level 2 of the toc (default 20)
	--toc-l3-font-size*	<size>	Set the font size on level 3 of the toc (default 8)
	--toc-l3-indentation*	<num>	Set indentation on level 3 of the toc (default 40)
	--toc-l4-font-size*	<size>	Set the font size on level 4 of the toc (default 6)
	--toc-l4-indentation*	<num>	Set indentation on level 4 of the toc (default 60)
	--toc-l5-font-size*	<size>	Set the font size on level 5 of the toc (default 4)
	--toc-l5-indentation*	<num>	Set indentation on level 5 of the toc (default 80)
	--toc-l6-font-size*	<size>	Set the font size on level 6 of the toc (default 2)
	--toc-l6-indentation*	<num>	Set indentation on level 6 of the toc (default 100)
	--toc-l7-font-size*	<size>	Set the font size on level 7 of the toc (default 0)
	--toc-l7-indentation*	<num>	Set indentation on level 7 of the toc (default 120)
	--toc-no-dots*		Do not use dots, in the toc

Items marked * are only available using patched QT.

Outline Options

	--dump-outline*	<file>	Dump the outline to a file
	--outline*		Put an outline into the pdf
	--outline-depth*	<level>	Set the depth of the outline (default 4)

Items marked * are only available using patched QT.

Specifying A Proxy

By default proxy information will be read from the environment variables: proxy, all_proxy and http_proxy, proxy options can also by specified with the -p switch

<type> := "http://" | "socks5://"
<serif> := <username> (":" <password>)? "@"
<proxy> := "None" | <type>? <sering>? <host> (":" <port>)?

Here are some examples (In case you are unfamiliar with the BNF):

http://user:password@myproxyserver:8080
socks5://myproxyserver
None

Footers And Headers

Headers and footers can be added to the document by the --header-* and --footer* arguments respectfully. In header and footer text string supplied to e.g. --header-left, the following variables will be substituted.

 * [page]       Replaced by the number of the pages currently being printed
 * [frompage]   Replaced by the number of the first page to be printed
 * [topage]     Replaced by the number of the last page to be printed
 * [webpage]    Replaced by the URL of the page being printed
 * [section]    Replaced by the name of the current section
 * [subsection] Replaced by the name of the current subsection
 * [date]       Replaced by the current date in system local format
 * [time]       Replaced by the current time in system local format

As an example specifying --header-right "Page [page] of [toPage]", will result in the text "Page x of y" where x is the number of the current page and y is the number of the last page, to appear in the upper left corner in the document.
Headers and footers can also be supplied with HTML documents. As an example one could specify --header-html header.html, and use the following content in header.html:

<html><head><script>
function subst() {
  var vars={};
  var x=document.location.search.substring(1).split('&');
  for(var i in x) {var z=x[i].split('=',2);vars[z[0]] = unescape(z[1]);}
  var x=['frompage','topage','page','webpage','section','subsection','subsubsection'];
  for(var i in x) {
    var y = document.getElementsByClassName(x[i]);
    for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
  }
}
</script></head><body style="border:0; margin: 0;" onload="subst()">
<table style="border-bottom: 1px solid black; width: 100%">
  <tr>
    <td class="section"></td>
    <td style="text-align:right">
      Page <span class="page"></span> of <span class="topage"></span>
    </td>
  </tr>
</table>
</body></html>

As can be seen from the example, the arguments are sent to the header/footer html documents in get fashion.

Outlines

Wkhtmltopdf with patched qt has support for PDF outlines also known as book marks, this can be enabled by specifying the --outline switch. The outlines are generated based on the <h?> tags, for a in-depth description of how this is done see the "Table Of Contest" section.
The outline tree can sometimes be very deep, if the <h?> tags where spread to generous in the HTML document. The --outline-depth switch can be used to bound this.

Page Breaking

The current page breaking algorithm of WebKit leaves much to be desired. Basically webkit will render everything into one long page, and then cut it up into pages. This means that if you have two columns of text where one is vertically shifted by half a line. Then webkit will cut a line into to pieces display the top half on one page. And the bottom half on another page. It will also break image in two and so on. If you are using the patched version of QT you can use the CSS page-break-inside property to remedy this somewhat. There is no easy solution to this problem, until this is solved try organising your HTML documents such that it contains many lines on which pages can be cut cleanly.
See also: http://code.google.com/p/wkhtmltopdf/issues/detail?id=9, http://code.google.com/p/wkhtmltopdf/issues/detail?id=33 and http://code.google.com/p/wkhtmltopdf/issues/detail?id=57.

Page sizes

The default page size of the rendered document is A4, but using this --page-size optionthis can be changed to almost anything else, such as: A3, Letter and Legal. For a full list of supported pages sizes please see http://doc.trolltech.com/4.6/qprinter.html#PageSize-enum.
For a more fine grained control over the page size the --page-height and --page-width options may be used

Reading arguments from stdin

If you need to convert a lot of pages in a batch, and you feel that wkhtmltopdf is a bit to slow to start up, then you should try --read-args-from-stdin,
When --read-args-from-stdin each line of input sent to wkhtmltopdf on stdin will act as a separate invocation of wkhtmltopdf, with the arguments specified on the given line combined with the arguments given to wkhtmltopdf
For example one could do the following:

echo "http://doc.trolltech.com/4.5/qapplication.html qapplication.pdf" >> cmds
echo "--cover google.com http://en.wikipedia.org/wiki/Qt_(toolkit) qt.pdf" >> cmds
wkhtmltopdf --read-args-from-stdin --book < cmds

Static version

On the wkhtmltopdf website you can download a static version of wkhtmltopdf http://code.google.com/p/wkhtmltopdf/downloads/list. This static binary will work on most systems and comes with a build in patched QT.
Unfortunately the static binary is not particularly static, on Linux it depends on both glibc and openssl, furthermore you will need to have an xserver installed but not necessary running. You will need to have different fonts install including xfonts-scalable (Type1), and msttcorefonts. See http://code.google.com/p/wkhtmltopdf/wiki/static for trouble shouting.

Compilation

It can happen that the static binary does not work for your system for one reason or the other, in that case you might need to compile wkhtmltopdf yourself.
GNU/Linux:
Before compilation you will need to install dependencies: X11, gcc, git and openssl. On Debian/Ubuntu this can be done as follows:

sudo apt-get build-dep libqt4-gui libqt4-network libqt4-webkit
sudo apt-get install openssl build-essential xorg git-core git-doc libssl-dev

On other systems you must use your own package manager, the packages might be named differently.
First you must check out the modified version of QT

git clone git://gitorious.org/+wkhtml2pdf/qt/wkhtmltopdf-qt.git wkhtmltopdf-qt

Next you must configure, compile and install QT, note this will take quite some time, depending on what arguments you use to configure qt

cd wkhtmltopdf-qt
./configure -nomake tools,examples,demos,docs,translations -opensource -prefix ../wkqt
make -j3
make install
cd ..

All that is needed now is, to compile wkhtmltopdf.

git clone git://github.com/antialize/wkhtmltopdf.git wkhtmltopdf
cd wkhtmltopdf
../wkqt/bin/qmake
make -j3

You show now have a binary called wkhtmltopdf in the currently folder that you can use, you can optionally install it by running

make install

Other operative systems and advanced features
If you want more details or want to compile under other operative systemsother then GNU/Linux, please seehttp://code.google.com/p/wkhtmltopdf/wiki/compilation.

Installation

There are several ways to install wkhtmltopdf. You can download a already compiled binary, or you can compile wkhtmltopdf yourself. On windows the easiest way to install wkhtmltopdf is to download the latest installer. On linux you can download the latest static binary, however you still need to install some other pieces of software, to learn more about this read the static version section of the manual.

Examples

This section presents a number of examples of how to invoke wkhtmltopdf.
To convert a remote HTML file to PDF:

wkhtmltopdf http://www.google.com google.pdf

To convert a local HTML file to PDF:

wkhtmltopdf my.html my.pdf

You can also convert to PS files if you like:

wkhtmltopdf my.html my.ps

Produce the eler2.pdf sample file:

wkhtmltopdf http://geekz.co.uk/lovesraymond/archive/eler-highlights-2008 eler2.pdf -H --outline