SQL Server access Previous / Next rows and first / last row from partitioned resultset

From SQL Server 2012, new built-in analytic functions are introduced, using that we can easily access subsequent/previous row in the same result set without the use of a self-join and first/last value in an ordered set of values.

In this post, let us see some examples for LEAD(), LAG(), FIRST_VALUE() and LAST_VALUE()

SQL Server - Example for invoking sql file through SQLCMD

SQLCMD utility lets us execute Transact-SQL statements, Stored procedures, and other script files at the command prompt,
in SSMS Query Editor in SQLCMD mode,
in a Windows script file or
in an operating system (Cmd.exe) job step of a SQL Server Agent job.

In this post, let us see an example for invoking sql file through SQLCMD

SQL Server Error - Msg 9412, Level 16, State 1

We will get this error when we are trying to query XML in SQL Server. If that XML is invalid, Say for example if the closing tag ">" is missed in the input XML

Below error message will be throwed :

Msg 9412, Level 16, State 1, Line 1
XML parsing: line 6, character 5, '>' expected


To resolve the error:

SQL Server - Error Msg 9400, Level 16, State 1, Line

We will get this error when we are trying to query XML in SQL Server. 
 
Each and every XML node should have closing node like this <element>....</element>. XML will become invalid when it has backslash "\" instead of forward slash "/"

Below error will be throwed  when input XML doesn't have proper enclosing node :

Msg 9400, Level 16, State 1, Line 1
XML parsing: line 6, character 5, unexpected end of input
 

To resolve the error:


SQL Server - Error Msg 9455, Level 16, State 1, Line

We will get this error when we are trying to query XML in SQL Server. 

Each and every XML node should have closing node like this <element>....</element>. XML will become invalid when it has backslash "\" instead of forward slash "/" and below error will be throwed :

Msg 9455, Level 16, State 1, Line 1
XML parsing: line 6, character 2, illegal qualified name character

To resolve the error :


SQL Server - Transactional Replication - Configuring subscriber

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring subscriber at server 3

In SSMS - > Under Object Explorer - > Replication -> Local Subscriptions -> New Subscriptions...

SQL Server - Transactional Replication - Configuring Publisher

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring publisher at server 2

In SSMS - > Under Object Explorer - > Replication - > Local Publications -> New Publication...

SQL Server - Transactional Replication - Configuring distributor

As mentioned in my previous post, In this post, let us see step by step tutorial in configuring distributor at server 1

In SSMS - > Under Object Explorer - > Replication - > right-click Configure Distribution...

SQL Server - Transactional Replication

As mentioned in MSDN BOL :

Transactional replication typically starts with a snapshot of the publication database objects and data.

As soon as the initial snapshot is taken, subsequent data changes made at the Publisher are usually delivered to the Subscriber as they occur (in near real time).

How Transactional Replication Works