Settings not to forget in IIS while installing asp.net web application

Deploying asp.net web application in a new server like w2k3, ensure following steps incase if you see HTTP Error 404 – File or directory not found error …

1. Allow asp.net v2.0.50727 under Web Service Extensions section in IIS

2. Run aspnet_regiis -i @ command prompt from the respective (32-bit/64-bit) frameworks directory

3. Set proper asp.net version at the virtual directory level

asp.net Application Life Cycle – IIS 5.0/6.0/7.0 – Links

http://msdn.microsoft.com/en-us/library/ms178473.aspx – IIS 5.0/IIS 6.0

http://msdn.microsoft.com/en-us/library/bb470252.aspx – IIS 7.0

String format function in Javascript – similar to C#

String.prototype.format = function() {
    var args = arguments;
    return this.replace(/{(\d+)}/g, function(match, number) {
        return typeof (args[number] != 'undefined') ? args[number] : '{' + number + '}';
    });
};
Extending javascript global object is not a good idea. This can be defined as a utility funciton with in your application's global js object/file.

Extending  Reference - http://stackoverflow.com/questions/610406/javascript-equivalent-to-printf-string-format

Efficient Pagination using ASP.NET + SQL Server (CTE) + ObjectDataSource approach, LINQ approach – Links

http://www.asp.net/data-access/tutorials/efficiently-paging-through-large-amounts-of-data-cshttp://stackoverflow.com/questions/548475/efficient-way-to-implement-paging

ASP.NET Page Life Cycle – Link

http://msdn.microsoft.com/en-us/library/ms178472.aspx

http://msdn.microsoft.com/en-us/library/dct97kc3.aspx

Pagination in SQL Server

Achieve pagination directly in SQL Server using different approaches ...

1. ROW_NUMBER() approach with inner join
SELECT ees.* FROM saleslt.customer ees
JOIN (SELECT customerid, rownum =ROW_NUMBER() over (ORDER BY customerid) FROM saleslt.customer ) eelist
ON eelist.customerid = ees.customerid
WHERE eelist.rownum BETWEEN 21 AND 30

2. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 1
declare @rowsPerPage int, @pageNo int
SET @rowsPerPage = 10
SET @pageNo = 59
begin
WITH SQLPaging
AS
(
SELECT TOP(@rowsPerPage * @pageNo) ResultNum = ROW_NUMBER() OVER (ORDER BY customerid), * FROM saleslt.customer
)
SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNo - 1) * @rowsPerPage)
end

3. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 2
WITH CTE_Customers
AS
(
SELECT CEILING(ROW_NUMBER() OVER (ORDER BY customerid ASC))/10 AS PageNumber, * FROM saleslt.Customer
)
SELECT * FROM CTE_Customers WHERE PageNumber = 10

4. Using sub query
SELECT * FROM
   (SELECT TOP 10 * FROM
     (SELECT TOP 30 * FROM saleslt.customer order by CustomerID asc) AS T2
	ORDER BY customerid DESC) T3
ORDER BY customerid ASC

5/6. Generalizing sub query approach using dynamic query in stroedproc, temporary table approach using dynamic query in storedproc
http://www.beansoftware.com/ASP.NET-Tutorials/Paging-Stored-Procedures.aspx

7. Cursor approach can be seen
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

8. ROWCOUNT approach
DECLARE @Sort int /* the type of the sorting column */
SET ROWCOUNT 21
SELECT @Sort = CustomerID FROM saleslt.customer  ORDER BY CustomerID
SET ROWCOUNT 10
SELECT * FROM saleslt.customer WHERE CustomerID >= @Sort ORDER BY CustomerID
Other References

• http://weblogs.asp.net/eporter/archive/2006/10/17/ROW_5F00_NUMBER_28002900_-OVER-Not-Fast-Enough-With-Large-Result-Set.aspx

Partition By in SQL Query

Getting department wise top salaried employee records ...
DECLARE @EMPLOYEE TABLE (
EmployeeId INT,
DepartmentId INT,
EmpSal MONEY
)
INSERT INTO @EMPLOYEE (EmployeeId,DepartmentId,EmpSal)
SELECT 100, 1, 25000 UNION ALL
SELECT 101, 1, 35000 UNION ALL
SELECT 102, 1, 20000 UNION ALL
SELECT 103, 2, 27000 UNION ALL
SELECT 104, 2, 37000 UNION ALL
SELECT 105, 2, 26000

SELECT  departmentid, employeeid, empsal FROM
(SELECT departmentid, employeeid, empsal, DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY empsal desc)  Salrank FROM @EMPLOYEE) EmployeeDataWithRank
WHERE salrank = 1;

Common Table Expression, Temp Table, Table Data Type

 

Temp tables

• Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.
• Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.
• Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

Table Variables

• These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.
• Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.
• Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

CTE

• CTEs are more like temporary views than anything else. Execution plan will show CTEs inlined into the query, not materialised and stored.

http://msdn.microsoft.com/en-us/library/ms175972.aspx

http://msdn.microsoft.com/en-us/library/ms186243.aspx

SSIS Reference

4 Ways to run SSIS packages outside visual studio

• Using SQL Server Management Studio
• SQL Server Agent job to schedule package to execute one time or timely manner
• Commandline excute package utility (dtexec /f mypackage.dtsx)
• Graphical execute package utility

 

 

SQL Server – Data Type Converions

Refer for all possible conversions @ http://msdn.microsoft.com/en-us/library/ms187928.aspx

Following one helps to extact date from datetime format ...

CONVERT(VARCHAR(10),GETDATE(),111)

Refer intersting article (4 parts) by Madhivanan @ http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx