SQL replication

SQL replication is a process for sharing/distributing data between different databases and synchronizing between those databases. You can use SQL replication to distribute data to a variety of network points like other database servers, mobile users, etc. You can perform the replication over many different kinds of networks and this won�t affect the end result.
In every SQL replication there are 2 main players called Publisher and Subscriber. The Publisher is the replication end point that supplies the data and the replication Subscriber is the replication end point that uses the data from the Publisher. Depending on the replication architecture a replication can have one or more Publishers and of course any replication will have one or more Subscribers.
MS SQL Server offers several main replication types. The Transactional replication is usually used when there�s need to integrate data from several different locations, offloading batch processing, and in data warehousing scenarios.
Another replication type is the Snapshot replication. The Snapshot replication is commonly performed when a full database refresh is appropriate or as a starting point for transactional or merge replications.
The third important SQL replication type is the Merge replication. The Merge replication is used whenever there is a possibility for a data conflicts across distributed server applications.

SQL NOT IN

SELECT * FROM Employees
WHERE [Last Name] NOT IN (N'Anderson', N'Shepherd');

SQL String Functions

String Functions

Some of the String Functions comes very handy at times. Let us discuss them one by one.

ASCII()

Returns the ASCII code value of the leftmost character of a character expression.

Syntax

ASCII ( character_expression ) 

SELECT ASCII('A') 

SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'The Csharp'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END
SET NOCOUNT OFF

Output:

-----------
65
----------- ----
84          T
----------- ----
104         h
----------- ----
101         e
----------- ----
and so on..... 

SQL DateName

DATENAME()

DATENAME() is very common and most useful function to find out the date name from the datetime value.
Example

 

-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

Output :

 Today Is Saturday
   Month IsAugust

Parameter Array in asp.net

Parameter Array
Parameter arrays allow a variable number of arguments to be passed into a function member. The definition of the parameter has to include the params modifier, but the use of the parameter has no such keyword. A parameter array has to come at the end of the list of parameters, and must be a single-dimensional array. When using the function member, any number of parameters (including none) may appear in the invocation, so long as the parameters are each compatible with the type of the parameter array. Alternatively, a single array may be passed, in which case the parameter acts just as a normal value parameter. For example:
void ShowNumbers (params int[] numbers)
{
foreach (int x in numbers)
{
Console.Write (x+" ");
}
Console.WriteLine();
}
...

int[] x = {1, 2, 3};
ShowNumbers (x);
ShowNumbers (4, 5);
Output:
1 2 3
4 5

private string Concatenate(string separator, params object[] parts)
{
System.Text.StringBuilder buffer = new System.Text.StringBuilder();
string sepValue = "";
foreach (object o in parts)
{
buffer.AppendFormat("{0}{1}", sepValue, o);
sepValue = separator;
}
return buffer.ToString();
}

Output parametersLike reference parameters, output parameters don't create a new storage location, but use the storage location of the variable specified on the invocation. Output parameters need the out modifier as part of both the declaration and the invocation - that means it's always clear when you're passing something as an output parameter.
Output parameters are very similar to reference parameters
Example :
void Foo (out int x)
{
// Can't read x here - it's considered unassigned
// Assignment - this must happen before the method can complete normally
x = 10;
// The value of x can now be read:
int a = x;
}

// Declare a variable but don't assign a value to it
int y;

// Pass it in as an output parameter, even though its value is unassigned
Foo (out y);
// It's now assigned a value, so we can write it out:
Console.WriteLine (y);
Output:
10

Update Trigger

Create TRIGGER Tgr_Update  ON  LeaveDetails


AFTER UPDATE
AS
BEGIN
Select  Top 1 @Empid = Empid from LeaveDetails 


  IF( @Empid > 0 AND @Empid IS NOT NULL)
 
   BEGIN
          update dbo.MasterLeave set TotalLeaveWithoutPay = 0, RemainingLeave = 1
          where Empid =@Empid
  END
END