Archive for August, 2011

Deploying 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 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 version at the virtual directory level – IIS 5.0/IIS 6.0 – IIS 7.0

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 -

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
SELECT TOP(@rowsPerPage * @pageNo) ResultNum = ROW_NUMBER() OVER (ORDER BY customerid), * FROM saleslt.customer
SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNo - 1) * @rowsPerPage)

3. Using Common Table Expression (Used Adventureworks DB) and ROW_NUMBER() - Approach 2
WITH CTE_Customers
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 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

7. Cursor approach can be seen

8. ROWCOUNT approach
DECLARE @Sort int /* the type of the sorting column */
SELECT @Sort = CustomerID FROM saleslt.customer  ORDER BY CustomerID
SELECT * FROM saleslt.customer WHERE CustomerID >= @Sort ORDER BY CustomerID
Other References
Getting department wise top salaried employee records ...
EmployeeId INT,
DepartmentId INT,
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;


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.
  • CTEs are more like temporary views than anything else. Execution plan will show CTEs inlined into the query, not materialised and stored.

SSIS Reference

Posted: August 4, 2011 in SSIS
Tags: , ,

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



Refer for all possible conversions @

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


Refer intersting article (4 parts) by Madhivanan @