Shrinking Truncate Log File – Log Full

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO 

you can get availability space, file name, physical location using following query.

SELECT name AS 'File Name' , physical_name AS 'Physical Name',
        size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

Regulare Expression for Comma Separated Email

Regular Expression Validation Expression for Comma Separated Email Address

\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*([,]\s*\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*)*

Change Authentication Mode into SQL Server 2008

This topic describes how to change the server authentication mode in SQL Server 2008 by using SQL Server Management Studio.

To Change security authentication mode

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To Enable the "sa login

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the login.
3. On the Status page, in the Login section, click Enabled, and then click OK

Client ID mode in asp.net

We have been using ClientID’s in ASP.NET 2.0/3.5 that makes each control to generate the unique client side id attribute to that page or browser. But these ID’s were long and randomly generated. Developers who work on Client-Side programming that uses Java Script, JQuery or Ajax have been suffering a lot spending considerable amount of time when it they need to reference those ClientID’s in the client side scripts.

Now the good news is that, ASP.NET 4.0 comes with new ClientIDMode property, gives full control to the developer on the ClientID’s generated by ASP.NET controls.

ClientIDMode can take the following four possible values

• AutoID - ASP.NET generate IDs as it does in v3.5 and earlier versions.
• Static - ASP.NET use exactly the same ID given to the server control for its client-side ID.
• Predictable - ASP.NET try to generate IDs that are guessable from looking at the structure of the page.
• Inherit - ASP.NET generate a client-side ID for the page or control using the same ClientIDMode as its parent. i.e. the client ID gets inherited from the parent control.

You can set this property in 3 ways

1.Control Level

2.Page Level

3.Application Level

Setting ClientIDMode at Control Level

Each and every server control in ASP.NET 4.0 has this property and the default value is inherit.

1.<asp:panel id="pnl" runat="server" cssclass="newStyle1"

2.ClientIDMode ="Static"> </asp:panel>

Setting ClientIDMode at Page Level

1.<%@ Page Language="C#" ClientIDMode ="Inherit"

2.AutoEventWireup="true"

3.CodeBehind="Category.aspx.cs"

4.Inherits="WebApplication3.Cat" %>

Setting ClientIDMode at Application Level

You need to set it at System.Web section of Web.config

1.<system.web>

2.<pages clientIDMode="Predictable">

3.</pages> 

4.</system.web>

ClientIDRowSuffix

Another interesting feature of the ClientID improvement in ASP.NET 4.0 is the ClientIDRowSuffix .This can be applied to DataBound or List controls. This is used to take control on how ID values for template controls in databound controls are generated. This requires that the ClientIDMode is set to Predictable.

1.<asp:GridView runat="server" 

2.ID="gvEmp" AutoGenerateColumns="False" 

3.ClientIDMode ="Predictable" ClientIDRowSuffix="EMPID">

4.</asp:GridView>

Best Practices

• Add ClientIDMode = "Static" in application level web.config
• Using ClientIDMode = "AutoId" will work the best even in worst cases.
• Add ClientIDMode = "Predictable" to each List Control Children Item Template of Databound Controls.
• When ever naming conflicts occurs Override ClientIDMode to Predictable
• When working with Web Server Control Development leave at default behavior i.e. Inherit from parent
• Override if and only if necessary that might be in individual sub controls

View Page Number and Report Generated On. in SSRS

     
Page Number:

a.      Total Page : 10

b.      Current Page: 2

·         Header

o   Right Click à Insert à Text Box à Set following Expression 

 

Report Generated On

           

o   Right Click à Insert à TextBox à Set following Expression

o   Use following Expression for Report Generated On

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

ROW_NUMBER

·         Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK

·         Returns the rank of each row within the partition of a result set.

DENSE_RANK

·         Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE

·         Distributes the rows in an ordered partition into a specified number of groups

Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.