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 |
+---------------------------------------------------------------------------+-------+
Example of IEnumerable and Yield
Thursday, October 16, 2008
Using Resource files in Web Service (.NET 3.5) and Regular expression with LINQ
Those who has tried to use resource file in ASP.NET web service know well there is issue in using resource files in web service. Following code snippet targets 3 points:
1. Using resource file in web service or HttpHandlers.
2. Regular expression for converting resource strings with respective localized strings
3. LINQ integration in Regular expression.
Suppose placeholder for resource string in text is like ({BR[MyResourceString]BR}) . Following function will return localized string.
*/
public static string LocalizedHTML(string inputHtml)
{
if (string.IsNullOrEmpty(inputHtml)) return "";
Regex reg = new Regex(@"\(\{IA\[(.*?)\]IA\}\)", RegexOptions.Multiline | RegexOptions.Singleline
| RegexOptions.IgnoreCase | RegexOptions.Compiled);
System.Resources.ResourceManager RM =
new System.Resources.ResourceManager("Resources.Language",
global::System.Reflection.Assembly.Load("App_GlobalResources"));
return reg.Replace(inputHtml, m => (!string.IsNullOrEmpty(RM.GetString(m.Groups[1].Value)) ? RM.GetString(m.Groups[1].Value) : m.Value));
}
Monday, October 13, 2008
Finding installed FTS IFilters on SQL 2005
Saturday, August 30, 2008
10 Myths about Open Source Software in Business
Saturday, August 16, 2008
Easy log parsing with FileHelper
The idea is very simple
You can strong type your flat file (fixed or delimited) simply describing a class that maps to each record and later read/write your file as an strong typed .NET array
Sunday, August 03, 2008
I want a better...
Every block - next level of public data processing
"EveryBlock is a new experiment in journalism, offering a Web "newspaper" for every city block in Charlotte, Chicago, New York, Philadelphia and San Francisco — with more cities to come. Enter any address, neighborhood or ZIP code in those cities, and the site shows you recent public records, news articles and other Web content that’s geographically relevant to you. To our knowledge, it’s the most granular approach to local news ever attempted."
Monday, July 28, 2008
Better searching with Cuil (hopefully)
Sunday, July 27, 2008
Reference Cards
http://refcards.com/
I found reference of this site having SQL server cheat sheet.
Thursday, June 05, 2008
Web Development Helper
Download link: http://www.codeplex.com/webdevhelper/Release/ProjectReleases.aspx?ReleaseId=11062
Monday, May 26, 2008
.NET Regex Balanced Grouping
Friday, May 23, 2008
Google plays foul
Personally, I feel, if you don't like someone, you should not criticize him in filthy language. Being in democracy doesn't mean you strike someone below the belt, specially when someone is top public figure. In 110 crore population, all may not happy with Sonia's government. People should express their view in ethical way.
Whats wrong with Google. I am agree that boy should have not used foul language, but who gives rights to Google to hand over the information. This was not any terrorist activity. If Google thinks, user is using foul language, he should have simple blocked the user. Activity done by Google, simply indicates that they have nothing to do with providing a free social networking platform to public, its all about BUSINESS, its all about getting POWER, its all about MONEY. I read somewhere IBM helped Nazis in holocaust by providing data of Jews population. Google has not done that bad right!!! Well done Google!!.
Friday, February 08, 2008
You have seen Y2K, worse to come!!!
According to computer scientists, Unix and Linux system will be massively affected. But of course, still having plenty of time. Surprisingly Perl 10 is Y2038 safe but thats not the reason why I love this language.
Read this:
It is explained that Unix and similar operating systems do not calculate time based on the Gregorian calendar. Instead, they are known to simply count time in seconds from their arbitrary "birthday", that is, GMT 00:00:00, Thursday, January 1, 1970. The accepted practice among software programmers is to use a 32-bit variable for this number (32-bit signed time_t). The largest possible value for the end integer in this calculation is 2**31-1 = 2,147,483,647. So, 2,147,483,647 seconds after Unix's birthday falls on Tuesday, January 19, 2038. And one second later, theoretically Unix systems will revert to their birth date (like an odometer switching back from 999999 to 000000).
See original Post.
Thursday, January 17, 2008
Definitions related with SQL Server
DML
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.
Examples: SELECT, UPDATE, INSERT, DELETE
DDL
DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.
Examples: CREATE, ALTER, DROP statements
DCL
DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.
Examples: GRANT, REVOKE statements
TCL
TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.
Monday, January 07, 2008
Fun with WWW::Mechanize
WWW::Mechanize is really a very handy module if you want to automate web page related tasks. Following PERL script downloads the Perl Cook Book pages in a directory. You can put this Perl script in a scheduler and all pages will be collected in few days.
use strict;
use WWW::Mechanize;
my $mech = WWW::Mechanize->new();
my $ouputDir = 'E:\work\documents\PerlCookBook';
my @urls = (
'http://www.perl.com/cookbook/perlckbk2/solution.csp?day=1',
'http://www.perl.com/cookbook/perlckbk2/solution.csp?day=2',
'http://www.perl.com/cookbook/perlckbk2/solution.csp?day=3',
'http://www.perl.com/cookbook/perlckbk2/solution.csp?day=4',
'http://www.perl.com/cookbook/perlckbk2/solution.csp?day=5'
);
foreach my $url (@urls) {
GetPage($url);
}
sub GetPage {
my $url = $_[0];
$mech->get($url);
if ( $mech->success() ) {
my $content = $mech->content();
if ( $content =~ /\<h2 class="head1"\>(.*)?\<\/h2>/ ) {
my $title = $1;
#$title =~ s/\s+//g;
my $filePath = $ouputDir . "\\PCB_" . $title . ".html";
if ( !-e $filePath ) {
open( WR, ">$filePath" ) or die "Can't create file $filePath\n";
print WR $content;
close WR;
print "File was created successfully\n";
}
else {
print "File already exists\n.";
}
}
}
else {
print "Reqeust was not successful\n";
}
}
Friday, January 04, 2008
Recompilation in SQL Server 2005
Before executing SQL statement, SQL server checks for the validity and correctness of the query and generates the execution plan if it's not all already available. No need to say compilation is very CPU intensive process. Before executing the already compiled SQL statement, SQL server perform several other checks and if any of these checks fails, SQL server perform again compilation of SQL statement. Such compilations are known as recompilations. Following are some reasons for recompilation:
- Schema change
- Statistics change
- Deferred compile
- SET option change
- Temporary table change
- Stored procedure created with RECOMPILE query hint or OPTION(RECOMPILE)
In SQL Server 2000, if you need to recompile a portion of stored procedure, you need to compile the whole stored procedure, however in SQL Server 2005, you can perform recompilation at statement level. Less recompilation means less CPU consumption and less contention.
For more information, see Identifying Recompilation
Wednesday, January 02, 2008
Considerations while designing own types
Though Value Types are created on stack and in some situation gives better performance as compare to Reference Types, we should not tempt with choosing Value Type for every situation. General misconception is that if we are not having complex functionality, we should choose Value Type. We should not forget that size of instance also does matter in copy operation. In functions, Value Types are passed by copying the instance as a parameter. Similarly if function is returning Value Type, that is also copied in caller function's memory space. We can make our type as Value Type in following situations:
- Type has no members that modify any of the type's instance field. In other words, type is immutable.
- Type should not lie in inheritance chain. That means, it should inherit from any other type and also any other type should derived from it.
- Instances of the type are small in size (16-20 bytes or less)
- Instances of the type are large (greater than 20 bytes) and are not passed as argument or return value in functions.
Most promising technologies in 2008
Welcome 2008. I see following technologies which can take a big leap in 2008:
- Microsoft Silverlight framework – facilitates you to run a desktop like application in Internet Explorer.
- Ultra Mobile PC (UMPC)- Size of a your hardback book, weight less than a Kg, no hard drive, only flash memory of approx 4 Gb. Visit Asus EEE
- ADSL2+: Broadband speed up to 24 Mbps (Of course not in India J)
- IPTV: Progress in this field was hampered in past. But now with the increase in broadband speed, this technology is ready to take off.