USE northwind
DECLARE @startIndex INT,
@pageLength INT
SET @startIndex = 6
SET @pageLength = 5
SELECT *
FROM (SELECT o.shipname,
o.shipaddress,
Row_number()
OVER(ORDER BY o.customerid DESC) AS rownumber
FROM orders AS o
JOIN [order details] AS od
ON od.orderid = o.orderid
WHERE o.customerid = 'VINET') AS shipmentdetail
WHERE rownumber BETWEEN @startIndex AND (@startIndex + @pageLength - 1)
Blogs on .NET and LAMP Technologies
There's often more than one correct thing.
There's often more than one right thing.
There's often more than one obvious thing.
--Larry Wall
Sunday, November 30, 2008
Paging in SQL Server 2005 using OVER
Sunday, November 23, 2008
Creating a user and giving permissions in database
Sometimes we need to create special users for our database. For example, in one of my applications, I need to provide appropriate permissions for remote server window service (which is running as a local system account on remote server), so that it can connect to my database and can perform read-write operations. I found following links helpful for understanding basics of SQL server security:
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
http://msdn.microsoft.com/en-us/library/ms187750.aspx
To cut long story short, following is SQL command to create and give read-write permissions to remote machine. Suppose remore server name is 'TESTDOMAIN\testserver'.
GoGO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'testdomain\testserver$')
CREATE LOGIN [testdomain\testserver$] FROM WINDOWS
GO
IF NOT EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'testdomain\testserver$')
CREATE USER [testdomain\testserver$] FOR LOGIN [testdomain\testserver$] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [testdomain\testserver$]
EXEC sp_addrolemember 'db_datareader', 'testdomain\testserver$'
EXEC sp_addrolemember 'db_datawriter', 'testdomain\testserver$'
GO
Saturday, November 15, 2008
Mime Types for Office 2007 documents
+---------------------------------------------------------------------------+-------+
| application/vnd.openxmlformats-officedocument.wordprocessingml.document | .docx |
| application/vnd.openxmlformats-officedocument.wordprocessingml.template | .dotx |
| application/vnd.openxmlformats-officedocument.presentationml.presentation | .pptx |
| application/vnd.openxmlformats-officedocument.presentationml.slideshow | .ppsx |
| application/vnd.openxmlformats-officedocument.presentationml.template | .potx |
| application/vnd.openxmlformats-officedocument.spreadsheetml.sheet | .xlsx |
| application/vnd.openxmlformats-officedocument.spreadsheetml.template | .xltx |
+---------------------------------------------------------------------------+-------+