How To: Turn on database statistics

Execute the following T-SQL:
ALTER DATABASE SET AUTO_UPDATE_STATISTICS ON

HOW TO: Get the database compability level

Execute the following query to get the compability level of all the databases.

SELECT name, compatibility_level FROM sys.databases

To change the compatibility level:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 100

How To: Get the lastest version of SQL Server 2008

The @@VERSION returns the version, build date and other information about the SQL Server.
In Sql Server 2008, the return format is

major.minor.build.incremental-build

Example: SELECT @@VERSION

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

HO TO: Clear procedure cache and empty the data cache

When profiling and optimizing a stored procedure or query, it is usefull to clean the sql server stored procedure and data cache.
The following T-SQL performes the task:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Transact-SQL: Control whether concatenation results are treated as nullor empty string values

Concatenation of strings with a null value will result in NULL.

For example:

DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + @NullString

Will return NULL and not ‘MyString.

Until now we could use

SET CONCAT_NULL_YIELDS_NULL OFF

When concatenating a null value with a string yields the string itself (the null value is treated as an empty string).

Microsoft has issued the following warning:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And this leaves us with the ISNULL solution:

DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + ISNULL(@NullString, '')

 

 

Linq e WCF

Exposing LINQ Generated Classes through WCF is very simple.
Just set the Serialization Mode property to Unidirectional.
The Linq designer will decorate the Linq classes with the DataContract and DataMember attributes.

Linq - How to attach objects from different data contexts

After retrieving an object with Linq to Sql, if an update (Attach) is tried on the same object, the following exception is thrown:

An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext.

Example:

Product product = null;

Product originalProduct = new Product();

// Gte product to change its properties

using (myDataContext dc1 = new myDataContext())

{

   product = dc1.Products.Single(p=>p.ID==457);

}

product.Description = "MyProduct";

using (myDataContext dc2 = new myDataContext())

{

   dc2.Products.Attach(product , originalProduct);

   dc2.SubmitChanges(); 

}

The best solution to fix this behavior is to set the DeferredLoadingEnabled property of the DataContext to false of the retrieve entity query.

Example:

Product product = null;

Product originalProduct = new Product();

// Gte product to change its properties

using (myDataContext dc1 = new myDataContext())

{

   dc1.Datacontext.DeferredLoadingEnabled = false;

   product = dc1.Products.Single(p=>p.ID==457);

}

product.Description = "MyProduct";

using (myDataContext dc2 = new myDataContext())

{

   dc2.Products.Attach(product , originalProduct);

   dc2.SubmitChanges(); 

}

 

The DeferredLoadingEnabled property of the DataContext controls the deferred loading options of LINQ.

How to use the DBCC SHRINKFILE statement to shrink the transaction log file

To shrink the transaction log file, execute the following statements:

USE MyDatabase
BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS

If the transaction log does not shrinks, run the statements again to make more of the virtual log files inactive.

1) BACKUP LOG MyDatabase TO DISK='C:\MyDatabase.bak'
Backup the transaction log to make most of the active virtual log files inactive

2)DBCC SHRINKFILE (MyDatabase_log, 300) WITH NO_INFOMSGS
Shrink the transaction log file

Running SSIS package on SQL Server Agent

A SSIS package can be executed using SQL Server agent.

To execute the package a Proxy Account and a credential must be configured in SQL Server.

Problems:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314).  The step failed.

The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:

1) The proxy account have the "Act as part of the operating system" privilege.

2) The SQL Server Account is in the group

SQLServer2005MSSQLUser$<server>$<instance>

3) The Proxy account is in the group

SQLServer2005DTSLUser$<server>$<instance>

SSIS - Send mail from script task

Sending a mail form an integration service package (SSIS) is implemented using the .Net framework classes.

The only question is how to use a SMTP Connection Manager task to configure the SMTP Server.

Public Sub Main()

    Dim mailMessage As MailMessage

    Dim smtpClient As SmtpClient

    Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection Manager").AcquireConnection(Dts.Transaction), String)

    Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)

    mailMessage = New MailMessage("from@myMail.net", "to@myMail.net")

    mailMessage.Body = "myMessage"

    mailMessage.Subject = "mySubject"

    smtpClient = New SmtpClient(smtpServer)

    smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

    mailMessage.IsBodyHtml = True

    smtpClient.Send(mailMessage)

    Dts.TaskResult = Dts.Results.Success

End Sub

SQL Server Defragmentation

SQL Server stores the data and indexes on data files in a physical disk, this is called external fragmentation. As all files on the windows system, fragmentation degrades I/O performance.
SQL Server has also a internal fragmentation that occurs when records are removed from the database pages, but the space is not freed.
If data files have external fragmentation, index rebuilding will take longer since there is a I/O bottleneck.

Defragmentation is the process to fix fragmentation.
For each type of fragmentation, there is a solution:
1) Internal fragmentation:
Use the following script from SQL Server 2005 BOL to defragment or DBCC DBREINDEX .

2) External fragmentation:
Use a defragmentation tool like diskeeper to defragment the file system.

Developing a maintenance plan for SQL Server is a best practice.

Prevent Windows from paging the SQL Server data to virtual memory on disk

Paging the SQL Server data to virtual memory on disk reduces overall performance.
The Enable the Lock Pages in Memory Option is a windows policy that prevents Windows from paging the SQL Server data to virtual memory on disk.
The option is disabled by default.

This privilege must be enabled to configure Address Windowing Extensions (AWE), that must be activated on sql server.

To enable the lock pages in memory option:
1) Execute gpedit.msc on Start->Run
2) Expand Computer Configuration->Windows Settings
3) Expand Security Settings->Local Policies
4) Select the User Rights Assignment folder
5) Double-click Lock pages in memory and add the account that runs sqlservr.exe

In SQL Server Activate AWE.

Max Degree of Parallelism

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.

The value must be equal to the number of physical processors, not including hyperthreading.

One possible calculation formula is:

select case

         when cpu_count / hyperthread_ratio > 8 then 8

         else cpu_count / hyperthread_ratio

       end as optimal_maxdop_setting

from sys.dm_os_sys_info;

 

Configure via Management Studio:

1. Open SQL Server 2005 Management Studio
2- Once the tool loads, navigate to the intended server in the Object Explorer and right click on server name
3. Select the 'Properties' option
4. Select the 'Advanced' page from the left navigation
5. Review the configurations for the 'Parallelism' section

Configure via T-SQL Script:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 8;

GO

RECONFIGURE WITH OVERRIDE;

GO

T-SQL And Linq To Sql Reference

Blogger Tags:

Exists

1. T-SQL

select * from Orders o

where exists (select 1 from Product where ProductType = 'Memory'               

              and ProductId = o.ProductID)

2. Linq

using (MyDataContext dc = new MyDataContext())

{    

    var orders = from o in dc.Orders    

                  where (from p in dc.Products    

                         where p.ProductType == 'Memory'    

                         select p.ProductID).Contains(o.ProductID)

                  select o;

}

How To avoid double click a button on Postback

A common problem in developing with asp.net is the user pressing more than once on a submit button.

One way to avoid this question is to use client side JavaScript to disable the button after the user clicked it. The only problem with this approach is if validators are being used. If the Page is not valid, the button will stay disabled and the user cannot press it again.

There is only one problem: When the button is disabled, the page is not submitted and the button event does not fires on the server.

Fortunately the validation library sets the Page_IsValid to false if validation fails or to true otherwise.
The JavaScript function to change the button image can be written as:

function ToggleButton(button, validationGroup)

{

   // If client validators not active

   if (typeof(Page_Validators) == "undefined")

   {

        SetWaitMode(button);

   }

   if (typeof(Page_ClientValidate) == 'function') 

   {

         // Force Page validation 

         Page_ClientValidate(validationGroup);                 

         // If validation passed

         if(Page_IsValid)

         {  

            SetWaitMode(button)       

         }

   }

   else

   {

        SetWaitMode(button);

   }

}

function SetWaitMode(button)

{

    button.src = "Shared/images/Wait.gif";

    document.forms[0].submit();

    window.setTimeout("DisableButton( '" + button.id  + "')", 0);           

}

function DisableButton(buttonID) 

{

    window.document.getElementById(buttonID).disabled = true;

}

This solution changes the button image, but doesn't solve the problem, since the user can still press the button.

A more elegant solution is to use two Divs, on with the wait image and other with the button. When the button is pressed it's Div is hidden and the one with the Wait image is shown.

<div id="btnSaveDiv2" runat="server" style="display: none; float: left">

    <img src="Shared/images/Wait.gif"></img>

</div>

<div id="btnSaveDiv1" runat="server" style="display: block; float: left">

    <asp:ImageButton runat="server" ID="btnSave" OnClientClick="javascript:ToggleButton(this, 'AddNewNoteValidationGroup')" ImageUrl="~/Common/images/layout/b_save.gif" OnClick="btnAddNote_Click" ValidationGroup="AddNewNoteValidationGroup" />

</div>

function ToggleButton(button, validationGroup)

{

   // If client validators not active

   if (typeof(Page_Validators) == "undefined")

   {

        DisableButton(button);

   }

   if (typeof(Page_ClientValidate) == 'function') 

   {

         // Force Page validation 

         Page_ClientValidate(validationGroup);                 

         // If validation passed

         if(Page_IsValid)

         {  

            DisableButton(button)       

         }

   }

   else

   {

        DisableButton(button);

   }

}

function DisableButton(button)

{   

    div1 = window.document.getElementById(button.id + 'Div1');

    div2 = window.document.getElementById(button.id + 'Div2');

    div1.style.display = "none";

    div2.style.display = "block";

}

 

This solution works with ASP.Net Ajax and with ASP.Net validators.
The final step is to create a Server Control... :)

References:

Understanding ASP.NET Validation Library

.

JavaScript Url Parser

Parsing the URL with JavaScript is a tedious task and can spend a lot of time.

A simple JavaScript library is the solution.

Poly9's Polyvalent JavaScript URL Parser is a complete URL parser that extracts the information from complex URLs:

http://user:password@WebServer.com/extension?argument1=value1#fragment

Get de JS File here.

The parser doesn't return the virtual directory.

A possible solution is to create a custom library and add the following functions:

// url format http://server/vdir/page.aspx?QueryStringParams

function GetVirtualDirectory(url) 

{ 

    var urlParts = url.split("/");     

    if (EndsWith(urlParts[3], ".aspx"))

    {

        return "";

    }

    return urlParts[3] + "/";    

} 

function EndsWith(str, end)

{

    var reg = new RegExp (end + "$");

    return reg.test(str);

}

How to resolve JavaScript and CSS files path in master pages

JavaScript and css files path in master pages is a problem if the aspx pages are not in the same structure. For example, you may want some page on the site root (like default.aspx, login.aspx and logout.aspx) and others on a folder to organize the site.

 

The first attempt to solve this problem is use code blocks (<%= ... %>) with ResolveUrl, which give the following error:
The Controls collection cannot be modified because the control contains code blocks (i.e. <% … %>).

The second attempt is to use  <%# ... %>) and ResolveUrl, but nothing is returned to the browser.

 

The third attempt is to data bind the HtmlHead in code behind of the master page, since it derives from Control:

protected override void OnLoad(EventArgs e)

{

    base.OnLoad(e);

    Page.Header.DataBind();

}

In the master page write: 

<style type="text/css"> 

  @import '<%# ResolveUrl("~/shared/css/style.css") %>'; 

  @import '<%# ResolveUrl("~/shared/css/grid.css") %>'; 

</style> 

<script type='text/javascript' src='<%# ResolveUrl ("~/shared/js/library.js") %>'></script>

The css problem can also be resolved with:

<head runat="server"> 

<link href="shared/css/style.css" rel="stylesheet" type="text/css" /> 

</head> 

The path is relative to the master page, and Asp.Net will solve the path based on the master page location.

The JavaScript files are still the problem. The third solution will fix it, or some code behind is in order!!!

A more elaborate solution would be to use resources and the WebResource.axd handler....