How to obtain current SQL Server version using sql query

SELECT SERVERPROPERTY('productversion') As Version, SERVERPROPERTY ('edition') As Edition, SERVERPROPERTY ('productlevel') As Updates

The first columns shows current version as number, the second one is the edition (standard, express etc.) and the third one shows which version level the server belongs to (SP1, SP2 etc.)

Trick to start web application with another SQL Server database

The following exception is thrown when you try to use one application with the another database. The problem is caused by asp.net membership provider which cannot recognize its schema.

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_reqsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

First of all be sure the aspnet membership provider is registered on that database using aspnet_regsql.exe.

Then the solution is: taking the web application offline and then online using Web Site Administration Tool. That makes some changes in web.config in the pages tag and globalization if needed. Then you can go!

SQL pagination: get fixed number of rows page by page

See the following examples:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable) As Tmp
WHERE SortNum Between 11 AND 20

or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between @StartRow AND @StartRow+@NumberOfRows-1


or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between (@page-1)*10+1 AND (@page)*10


The first one gives the second page of 10 records.
The second example shows the usage of WITH keyword and getting rows passed as parameter of stored procedure.
The third one shows you how to use 1 passed parameter as number of the desired page.

How to set your master page by the nested page

The PreInit Page event n the nested page can be used for setting some Master page parameters.

protected void Page_PreInit(object sender, EventArgs e)
{
(this.Master as MasterType).Property = value;
}

Then the Master page will be loaded and rendered using these settings.
Master is the reference to the Master page. MasterType is the type of master page used.
Property is the public variable which must be set before loading page.

Using Page_PreInit the Master page can be set dynamically.

Sql Date Manipulation

DATEPART (datepart, date) returns the desired part of the date specified by datepart parameter: year,
quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond.

Example: DATEPART(month, GETDATE()) returns current month number

DAY(date), MONTH(date), YEAR(date) return respectively the number of the day, month or year of the passed date.

To add or subtract date parts from a given date use this function:
DATEADD (datepart , number, date)
where datepart is one of the following parameter (datepart): year,
quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond.

Example: DATEADD(day, -7, GETDATE()) return the date week ago.

DATEDIFF (datepart, startdate, enddate) gives you the difference between two dates in desired date part (see DATEPART date parts above). Startdate should be date before end date. Otherwise a negative number will be returned.

Example: DATEADD(day, DATEADD(day, -7, GETDATE()), GETDATE()) return 7 days.