Expert Programmers Blog

How do I set isolation level in DB2?

2010-12-08T09:13:00.001-08:00

If you want your users to use an isolation level of UR who are doing adhoc queries or typing SQLs using DB2 CLP or some form of the applications.

By using DB2 CLP

Use SET CURRENT ISOLATION LEVEL command

db2 connect to sample

db2 set current isolation level UR

In C/C++ programs using DB2 CLI

Set SQLSetConnectAttr function with the SQL_ATTR_TXN_ISOLATION attribute to set the transaction isolation level for the current connection referenced by the ConnectionHandle.

For ODBC, embedded SQL, SQLJ, embedded Cobol

Set value of TXNISOLATION keyword in the db2cli.ini file.

For JDBC JCC Type-IV driver

You can set setTransactionIsolation method in the java.sql interface connection.

Connection Pooling and Connection Concentrator

2010-12-08T09:12:00.001-08:00

Connection pooling is the methodology to place a connection for later re-use after an application has disconnected (no longer requires the connection). The next application which issues the "Please connect to database xxxxx" request would be given a connection in the connection pool matching the requested database. If none exist in the pool, generate a new connection. This saves the overhead of connection tear down/connection establishment. The application itself is unaware of what is going on under the covers. Typically, Application Server products (such as WebSphere) provide the connection pooling infrastructure. A DB2 Connect Server also provides Connection Pooling of backend connections to zDB2.

DB2 UDB/Connect connection concentration (CC), on the other hand, is a capability of these products to multiplex X application connections onto Y db2 agents where X > Y. db2 agents are the DB2 processes that serve an application connection. The use of the CC within a DB2 Linux Unix and Windows (LUW) server allows reduced resource usage at the DB2 Server (fewer db2agents required).

What are simple, segmented and partitioned table spaces ?

2010-12-07T23:06:00.003-08:00

Simple Tablespace:

· Can contain one or more tables

· Rows from multiple tables can be interleaved on a page under the DBAs control and maintenance

Segmented Tablespace:

· Can contain one or more tables

· Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments

Partitioned Tablespace:

· Can contain one table

· Tablespace is divided into parts and each part is put in a separate VSAM dataset.

What is check pending and copy pending status in DB2?

2010-12-07T23:06:00.001-08:00

· Check pending status of a table is set on when you are loading into table with log no and that table has some referential constraint with other table. Copy pending status is set on when you are loading into a table without a NOCOPYPEND option.

· If the table is loaded with ENFORCE NO option then the table space get into check pending status. That means table space is loaded without enforcing constraints. CHECK utility needs to be run on table space. If table is loaded with LOG NO option then it gets into copy pending status. The meaning is image copy is needed on table space.

How to fetch top 10 records in DB2?

2010-12-03T02:11:00.001-08:00

I was believing that DB2 does not have feature to read top rows. But i was wrong. Syntex for fetching the top rows in DB2 is different from SQL Server. So i thought i will share this peace of information with my readers. You can get the tops 10 row in DB2 using the the below query.

SELECT * FROM EmployeeMaster ORDER BY MonthSal DESC FETCH FIRST 10 ROWS ONLY

Hope it will help you guyes. Happy programming :)

Comparison of Crystal Reports Server and SSRS product features

2010-11-25T01:25:00.001-08:00

Feature	Crystal Reports Server	SSRS
Report Server Features
Publishing	InfoView and Windows client	Visual Studio–based Report Designer, Web-based Report Manager (main user interface), Microsoft Windows SharePoint Services, and Report Viewer control
Schedule calendar	Template schedules can be used by different reports	Shared schedules can be used by different reports
Export formats	Word, Excel, and PDF	Excel, Text, XML, PDF, TIFF, and MHTML (or custom rendering extensions)
Delivery channels	MAPI, file, FTP, and e-mail	E-mail and file share (or custom delivery extensions)
Data-based subscriptions	Available (through Business View semantic layer only)	Dynamic query-based subscriptions
Alerts	Available	Available
Events	Custom, file, and scheduled	Customizable through Web Services application programming interface (API)
Report history	Report instances	Snapshots
Linked reports	Available through coding of hyperlinks only when using Crystal Reports Server or Business Objects Enterprise	Simple dialog box to create links to a shared report, with the option of customizing properties of the linked report
Report Types
Drill down reports	N-depth levels	N-depth levels
Report with subreport	Only one level of subreports	N-depth levels
Cross-tab reports	Available	Available
OLAP reports	Available	Available
Multi-column reports	In details and groups	In details and groups
Labels	Standard or user-defined	User-defined
Report Object Features
Maps	MapInfo (ActiveX control)	Available
Formulas	Crystal or Basic syntax; formulas can be reused in server repository.	Visual Basic .NET expressions or embedded .NET assembly objects
SQL expressions	Sentences processed by SQL engine (formulas processed by Crystal engine)	Not needed, because there is direct interface to SQL database native dialect
Parameters	Dynamic parameters available (only when Business View or Universe is used)	Dynamic parameters available
Cascading parameters	Available only in Business Views	Available; can define values for one parameter, based on another parameter's value.
Pictures, lines, and figures	Not possible to apply conditional formatting	Fully customizable
Custom and user-defined functions	Functions from a report can be exported to global repository	Use .NET assemblies
Data region	Must use several detail sections, subreports, and user functions	Database field brings back first record for display; data region controls display of multiple records.
Shared data sources	Business view functionality	Can be shared by multiple reports
Multiple data regions on a report (tables, matrices, charts, and so on)	Not available	Available
Grouping and Sorting Features
Top-N, bottom-N, top %, bottom %	Available; includes wizards	Available
Export	Exports through viewers	Exports through viewer or subscription services using rendering extensions
Viewer Features
Formats	Native Crystal, Excel, PDF, RTF, and Word	HTML 3.2/4.0, PDF, Excel, XML, CSV, TIFF, and MHTML
Navigation in parts	Can be defined	Uses bookmarks on report objects
Group navigation	Available as group tree	Available as document map
Drill-down on same page	Not available	Available

Sync Framework for SQL Azure

2010-11-10T03:39:00.003-08:00

SummarySQL Azure Database is a cloud database service from Microsoft. SQL Azure provides web-facing database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This document is not intended to provide comprehensive information on SQL Azure Data Sync. However, the intent is to provide best practices guidelines on synchronizing SQL Azure with SQL Server and to supplement the information available at the links in the References section.

Sync Framework

Microsoft Sync Framework is a data synchronization platform that can be used to synchronize data across multiple data platforms, by using the specific database providers. A key aspect of Sync Framework is the ability to create custom providers. Providers enable any data sources to participate in the Sync Framework synchronization process, allowing peer-to-peer synchronization to occur. To include support for SQL Azure, the Microsoft Sync team released the Microsoft Sync Framework Power Pack for SQL Azure which includes a provider for SQL Azure and a Data Sync tool for SQL Azure.

The SqlAzureSyncProvider is an optimized SQL Azure provider that handles some of the complexities involved in handling transactions with SQL Azure by using Table-valued parameters to reduce round trips to the server and also by using an intelligent "back-off-algorithm" to scale down the batch size of BCP during synchronization when it hits the throttling levels.

The Data Sync tool is a wizard that helps connect between SQL Server and SQL Azure and automate the synchronization process.

Steps in setting up Synchronization

Following are the steps in setting up a Synchronization between an on-premise SQL Server and SQL Azure.

Download and install the Microsoft Sync Framework Power Pack for SQL Azure November CTP

o Also download the Walkthrough Microsoft Sync Framework Power Pack for SQL Azure November CTP.doc from above location. The document has step-by-step instructions in setting up Synchronization between an on-premises SQL Server and SQL Azure.

For larger databases you may see faster synchronization times if you create multiple scopes. Since each scope has one thread allocated to it, creating multiple scopes allows for parallel data migrations.

Scopes in Sync Framework

A scope is the combination of tables and filters. For example, you could define a filtered scope named Sales-WA that contains only the sales data for the state of Washington from the customer sales table. If you define another filter on the same table, such as Sales-OR, this is a different scope. If you define filters, Sync Framework does not automatically handle the deletion of rows that no longer satisfy a filter condition. For example, if a user or application updates a value in a column that is used for filtering, a row moves from one scope to another. The row is sent to the new scope that the row now belongs to, but the row is not deleted from the old scope. Your application must handle this situation.

Guidelines for efficient Scoping

· Each scope has one thread allocated to it from the OS. So distributing the tables across multiple scopes will help parallelize the data migrations

· Put static/changing at a very low rate tables in one scope and reduce their sync frequency

· Group frequently changing tables in different scopes

· Put logical related tables (Primary Key-Foreign key dependency or logical dependency) in one scope

· Scopes that are only read on the client should be marked as download only as this streamlines the sync workflow and decreases sync times

· It is better to minimize the number of clients that are in each scope with the best case being a different scope for each client. This minimizes contention on the server and is ideal for the hub-spoke case where all changes flow through a single server vs. being synced between clients

Initialize via snapshots vs. full initialization wherever possible to improve initial sync time by an order of magnitude

How COALESCE function in DB2 works?

2010-11-10T03:39:00.001-08:00

The COALESCE function returns the value of the first non null expression.
The arguments must be compatible. The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all arguments can be null. The result is null only if all arguments are null.
The selected argument is converted, if necessary, to the attributes of the result. The attributes of the result are determined using the Rules for result data types. If the COALESCE function has more than two arguments, the rules are applied to the first two arguments to determine a candidate result type. The rules are then applied to that candidate result type and the third argument to determine another candidate result type. This process continues until all arguments are analyzed and the final result type is determined.
The COALESCE function can also handle a subset of the functions provided by CASE expressions. The result of using COALESCE(e1,e2) is the same as using the expression:

   CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END

VALUE can be specified as a synonym for COALESCE.
Example 1: Assume that SCORE1 and SCORE2 are SMALLINT columns in table GRADES, and that nulls are allowed in SCORE1 but not in SCORE2. Select all the rows in GRADES for which SCORE1 + SCORE2 > 100, assuming a value of 0 for SCORE1 when SCORE1 is null.

   SELECT * FROM GRADES

     WHERE COALESCE(SCORE1,0) + SCORE2 > 100;

Example 2: Assume that a table named DSN8810.EMP contains a DATE column named HIREDATE, and that nulls are allowed for this column. The following query selects all rows in DSN8810.EMP for which the date in HIREDATE is either unknown (null) or earlier than 1 January 1960.

   SELECT * FROM DSN8810.EMP

     WHERE COALESCE(HIREDATE,DATE('1959-12-31')) < '1960-01-01';

The predicate could also be coded as COALESCE(HIREDATE,'1959-12-31') because for comparison purposes, a string representation of a date can be compared to a date.
Example 3: Assume that for the years 1993 and 1994 there is a table that records the sales results of each department. Each table, S1993 and S1994, consists of a DEPTNO column and a SALES column, neither of which can be null. The following query provides the sales information for both years.

   SELECT COALESCE(S1993.DEPTNO,S1994.DEPTNO) AS DEPT, S1993.SALES, S1994.SALES

     FROM S1993 FULL JOIN S1994 ON S1993.DEPTNO = S1994.DEPTNO

     ORDER BY DEPT;

The full outer join ensures that the results include all departments, regardless of whether they had sales or existed in both years. The COALESCE function allows the two join columns to be combined into a single column, which enables the results to be ordered.

Security in the Microsoft Cloud

2010-11-10T03:38:00.001-08:00

Microsoft applies security mechanisms at different layers of the cloud infrastructure to implement a defense-in-depth approach. These layered mechanisms include:

Physical security of the data centers (locks, cameras, biometric devices, card readers, alarms)
Firewalls, application gateways and IDS to protect the network
Access Control Lists (ACLs) applied to virtual local area networks (VLANs) and applications
Authentication and authorization of persons or processes that request access to data
Hardening of the servers and operating system instances
Redundant internal and external DNS infrastructure with restricted write access
Securing of virtual machine objects
Securing of static and dynamic storage containers

Assets are categorized as to the level of security required, based on the potential for damage. Highly sensitive assets are protected by more stringent mechanisms, such as multi-factor authentication (smart cards, biometrics, hardware tokens). The principle of least privilege is followed, whereby persons and processes are given the lowest level of access that is required for them to do their jobs and no more.
Microsoft's online services teams apply the Security Development Lifecycle (SDL) principles (security by design, security by default, and security in deployment + communications) to the online services. To read more about the SDL and SD³+C principles, follow this link.
Windows Azure is deployed within Global Foundation Services datacenters, and thus enjoys the network security benefits provided by GFS. It is the responsibility of application developers to ensure that application data is secured at the application layer. Thus it is up to the application developer to determine whether/which data should be encrypted.
Security in SQL Azure is much like security for an on-site SQL Server, so SQL administrators will find security management at the database level to be a familiar task. Server-level administration is a bit different because the databases may span more than one physical system.
Note:
To find out more about managing security in SQL Azure, check out the MSDN web site.

How do I print the first 100 characters of a large block of text in SQL SERVER?

2010-11-10T03:37:00.001-08:00

You can print, say, the left 100 characters or so of a block of text as follows:

<%
bigtext = "Hello, this is a big paragraph of text, used" & _
"solely for demonstration purposes. The code" & _
"will return the first 100 characters or so" & _
"to simulate some kind of ""excerpt"" where" & _
"you would later click for more info..."

excerpt = left(bigtext, instrRev(LEFT(bigtext, 100), " ")-1) & "..."

response.write excerpt
%>

If you want to add a space before the "...", either take out the -1, or add a leading space, e.g. " ..."

Having said that, if this information is coming from a database, you might want to adjust your query to be more efficient; for example, only returning the first 100 or so characters across the wire (this is especially true for TEXT columns, which often contain far more data than you would ever need to show in an excerpt... so why return it?).

SQL Server:
SQL Server doesn't support an InstrRev-like function, so the query is a little more complicated than it might otherwise be:

SELECT
excerpt = SUBSTRING
(
column,
1,
100-CHARINDEX
(
' ',
REVERSE
(
SUBSTRING
(
column,
1,
100
)
)
)
)+'...'
FROM
table

MS Access:

In Access, the query is a LOT more like the VBScript version, since Access supports VBA:

SELECT
excerpt = LEFT
(
column,
INSTRREV
(
LEFT
(
column,
100
),
" "
)-1
) & "..."
FROM
table

However, you might find that this query doesn't work from. If this is the case, then just take the left 250 characters or so, and then apply the same logic in your ASP code as above.

What is the differenct between Session state and View state?

2010-10-25T09:09:00.001-07:00

In some cases, using view state is not feasible. The alternative for view state is session state. Session state is employed under the following situations:

Large amounts of data - View state tends to increase the size of both the HTML page sent to the browser and the size of form posted back. Hence session state is used.
Secure data - Though the view state data is encoded and may be encrypted, it is better and secure if no sensitive data is sent to the client. Thus, session state is a more secure option.
Problems in serializing of objects into view state - View state is efficient for a small set of data. Other types like DataSet are slower and can generate a very large view state.

What are the new Functionality in BCL in .NET 4 Beta 2?

2010-10-15T04:53:00.001-07:00

Visual Studio 2010 and .NET Framework 4 Beta 2 are now available to download. .NET 4 Beta 2 contains several new BCL features and enhancements in addition to what was included in .NET 4 Beta 1. Many of these improvements were added in large part due to specific feedback and suggestions reported by customers through Microsoft Connect.

· Complex Number

· Location

· IObservable<T>

· Stream.CopyTo

· Guid.TryParse, Version.TryParse, and Enum.TryParse<T>

· Enum.HasFlag

· String.Concat and String.Join overloads that take IEnumerable<T>

· String.IsNullOrWhiteSpace

· Environment.SpecialFolder additions

· Environment.Is64BitProcess and Environment.Is64BitOperatingSystem

· Path.Combine params support

· TimeSpan Globalized Formatting and Parsing

· Stopwatch.Restart

· StringBuilder.Clear

· IntPtr and UIntPtr Addition and Subtraction operators

· ServiceInstaller.DelayedAutoStart

· ObservableCollection<T> moved to System.dll

Get the detail about each bullet points in .NET MSDN.

What is the difference between String.Concat and String.Format? String.Concat vs String.Format.

2010-10-15T04:52:00.001-07:00

Both can be used for concatenating the strings, but the purpose of using both is totally different. As per the MSDN

String.Format: Replaces each format item in a specified string with the text equivalent of a corresponding object's value.

· String.Concat: Concatenates one or more instances of String, or the String representations of the values of one or more instances of Object.

I found many articles comparing the String.Concat and String.Format. Some says first one is better, but some thinks the later one is better. So, I decided to give them my touch. So, here are my findings.

I am reading many 500 words from txt file and later concatenating those words. I found that String.Concat is much faster than String.Format. But String.Format is useful when you want to convert the string into a specific format.

DB2 temporary table using WITH Statement:

2010-08-27T10:30:00.003-07:00

The WITH statement allows you to create temporary tables that can be used within a single query, like our first example, but with the added advantage that the tables can refer to one another.

For example, suppose we want to print out the most expensive books published by each publisher ( pub_name, isbn, author, title, c_price) and to put an "*" beside the most expensive books in the entire library. The output should look like

1 PUB_NAME ISBN AUTHOR TITLE C_PRICE

- -------- ---- ------- ------ -------

AW 1-52 Date DB 28.00

CSP 7-45 Baer Arch 35.00

* PH 2-34 T'baum Netw 37.00

Wiley 3-56 K'rock Queue 25.00

4 record(s) selected.

The SQL is:

with

temp1 as (select pub_name, max(c_price) as maxprice

from book group by pub_name),

temp2 as (select pub_name, maxprice from temp1

where maxprice = (select max(maxprice) from temp1) )

select case when t.maxprice = t2.maxprice then '*' else '' END,

k.pub_name, isbn, author, title, c_price

from book k, temp1 t , temp2 t2

where k.pub_name = t.pub_name and

k.c_price = t.maxprice

order by k.pub_name;

Notice how we referred to the table temp1 in the query which defined table temp2. This would not be possible if we had used our first approach of putting both temp1 and temp2 in the from-clause of the select statement as follows:

select case when temp1.maxprice = temp2.maxprice then '*' else '' END,

k.pub_name, isbn, author, title, c_price

from book k,

table (select pub_name, max(c_price) as maxprice

from book group by pub_name) as temp1 ,

table (select pub_name, maxprice from temp1

where maxprice = (select max(maxprice) from temp1) )

as temp2

where k.pub_name = temp1.pub_name and

k.c_price = temp1.maxprice

order by k.pub_name;

This is because the table temp1 does not exist in the schema we are currently using - db2inst1 or pletcha - but only in the query currently being executed. However, in the definition of temp2 that is precisely where the query looks for the temp1 mentioned. Since it doesn't find it we get an error message (SQL0204N).

So the value of the with-clause is that you can have several temporary tables and refer to one in the construction of some other. You also have the advantage of simplifying the final select statement so it is easier to read.

One last comment about the case statement used. In C, C++ and Java there is a statement of the form

(condition) ? <expression_1> : <expression_2>;

This statement evaluates (condition), if it is true it returns <expression_1> else it returns <expression_2>. This is precisely the behaviour of the case statement. The statement

case when temp1.maxprice = temp2.maxprice then '*' else '' END

evaluates temp1.maxprice = temp2.maxprice and if it is true then the case statement returns '*'; otherwise it returns the empty string.

An observation. Using the case statement gives you a means of taking some of the search logic, which normally appears within the where-clause, and moving it into the select-list. There are moments when this can be very useful.

DB2 temporary table for single Session:

2010-08-27T10:30:00.001-07:00

Temporary tables which survive for an entire session need to be created in a user tablespace which is a user temporary space. This kind of table space is not created automatically and so needs to be created by the sysadmin. You will need to do this if you are running db2 on your own computer. This is most easily done using the Control Center however the default syntax for doing this is:

CREATE USER TEMPORARY TABLESPACE some_name PAGESIZE 4 K

MANAGED BY SYSTEM USING ('/home/db2inst1/db2inst1/NODE0000/container_name')

EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32

TRANSFERRATE 0.9 BUFFERPOOL IBMDEFAULTBP;

This code has already been run on avalon.

Once this tablespace exists and you are granted use of this space (by me),

db2 grant use of tablespace some_name to user some_user

you are able to create temporary tables which can be used to hold temporary, non-logged data for the duration of your session (until you terminate the database connection). To terminate a session just execute

db2 connect reset

db2 terminate

Upon doing this all temporary tables you created during the recent session are dropped.

There are a couple of ways you can define a temporary table. A complete list of the possible syntaxes can be found in the SQL Reference Manual under declare global temporary table. For now we will look at only two.

declare global temporary table table_name1

LIKE <table_name2> on commit preserve rows not logged

declare global temporary table table_name

(column1 some_data_type, column2 some_data_type, ...)

on commit preserve rows not logged

Neither of these statements actually puts any rows into the temporary table. To do that you must use the normal insert into table_name syntax. However to do this you need to know what schema the temporary tables are stored in. The schema is called SESSION. This schema name can not be left off whenever you are using a global temporary table. This is because no reference to such a table is to be found in SYSIBM.SYSTABLES so if you leave off the schema name and a table exists in your current schema with the same name then that table will be used instead.

If we were to answer the same question these notes began with using a global temporary table the SQL syntax would be

1: Find the most expensive books sold by each publisher.

set current schema = LIBRARY;

declare global temporary table Temp1

(pub_name varchar(7), maxprice numeric(8,2))

on commit preserve rows not logged ;

insert into SESSION.Temp1 (pub_name, maxprice )

select pub_name, max(c_price) from book group by pub_name;

select bk.pub_name, bk.author, bk.title, bk.c_price

from book bk, SESSION.Temp1 t

where bk.pub_name = t.pub_name and bk.c_price = t.maxprice

One curious characteristic of the declare global ... command is that if you do not include the on commit preserve rows section then the default behaviour is to wipe out the contents of the temporary table whenever a commit statement is issued (explicitly or implicitly) which would otherwise write all table data to disk. Since a commit transaction statement might be executed by db2 between the insert into and select statements above you might end up with an empty answer set of you don't explicitly save the data in the temporary table.

DB2 temporary tables for Single SQL Statement:

2010-08-27T10:29:00.000-07:00

Temporary tables which survive for the duration of a single SQL statement are those defined in the from clause of such a statement. They are used when you wish to break up a query into steps but don't need the intermediate tables for anything other than the final results of the query being developed.

1: Find the most expensive books sold by each publisher.

select pub_name, author, title, c_price

from book bk, table(select pub_name, max(c_price) as maxprice

from book group by pub_name) as temp1 t

where bk.pub_name = t.pub_name and bk.c_price = t.maxprice

The table temp1 is a temporary table which will not exist after the execution of the query (t is its alias). We use temp1 to break the query logic up into two steps - first find the highest book price for each publisher and second find the books published by that publisher that cost that much.

The alternative to doing things this way is to use a corelated subquery.

1: Find the most expensive books sold by each publisher.

select pub_name, author, title, c_price

from book bk

where bk.c_price = ( select max(c_price) from book bk1

where bk1.pub_name = bk.pub_name)

This query, since it is corelated on pub_name, will need to have its subquery reexecuted for every row in book and so theoretically, if there are many books published by the same publisher, we may need to recalculate the exactly same max price over and over again instead of once per publisher.

The use of the temporary table means we build the table only once but it will be "bigger" since we collect all max(c_price) values for all publishers. In order to know which is faster, it is probably a good idea to execute both and see which runs faster.

DB2 Temporary Tables

2010-08-27T10:28:00.000-07:00

DB2 has three kinds of temporary tables which we will study.

· Temporary tables which exist only during the execution of a single sql statement,

· Temporary tables which survive for the duration of a single session.

· Temporary tables that are part of a WITH-statement.

Managing roles and roles project in Microsoft Azure

2010-06-08T22:18:00.001-07:00

Windows Azure Tools for Microsoft Visual Studio extend Visual Studio to enable the creation, building, debugging, running and packaging of scalable services on Windows Azure.

You can create a new Cloud Service using the New Project dialog in Visual Studio and selecting the "Cloud Service" template.

This will bring up a dialog which enables you to add Web and Worker roles to your Cloud Service.

If you create a Cloud Service that contains a Web Role and Worker Role, your project structure will look similar to the one below:

The Cloud Service project:

Builds and packages your Cloud Service for the Development Fabric
Provides Run and Debug functionality in the Development Fabric
Publishes your package to the Windows Azure Service Package format
Enables you to configure the roles and role projects in the Cloud Service

This blog will walk through using the Roles node in the Cloud Service project.

Inside the Cloud Service project you will see a node called "Roles". The Roles node provides a graphical view of the Visual Studio projects that are associated with the roles defined in the Service Definition file (ServiceDefinition.csdef).

You can use the Roles node to:

Change the project associated with the Web or Worker role in your Cloud Service
Add or remove roles from your Cloud Service
Set Role properties

Changing a project associated with a role

At some stage you might want to change the project associated with a specific role in your cloud service. You have two options:

Associate a role with a new project
Associate a role with an existing project

Associate a role with a new project

In order to create a new project for a specific role in your cloud service, you:

Open the Roles node in the Cloud Service
Right click on the existing node you want to change the project association and choose "Associate with->New … Project" (note that the menu item text changes to reflect the role type)
The New Project dialog is displayed with the appropriate project template for the Role type you selected.

· Name the project and hit OK

A new project is created for you in the solution

· The Roles node is updated to show the association to the newly created project

· The previous project associated with the role remains in the solution.

Associate with an existing role project

In order to associate an existing project with a specific role in your cloud service, you:

Load or create the role project you want to associate into the same solution as your cloud service.
Right click on the existing node you want to change the project association and choose "Associate with->… Role project in solution" (note that the menu item text changes to reflect the role type)
The "Associate with Role Project" is displayed. This dialog lists the projects in the current solution that have the same Role type as the role you are changing.

· Choose the project you want to associate the role with and hit Ok.

The roles node is updated to display the project association

· The previous project associated with the role remains in the solution.

Add or Remove Roles from a Cloud Service

You can also use the Roles node to add or remove roles from a Cloud Service. While you could do this directly in the service definition file, using the Roles node allows Visual Studio to update the Service Configuration file at the same time.

Adding a Role to a Cloud Service

Adding a role to Cloud Service is simple, right click the Roles node in a Cloud Service and select the type of role you want to add to your cloud service.

Note that you have an option to add a new project for that role or use an existing role project. Once a role is added to the cloud service, the appropriate entries are also made in the service configuration file.

Removing a Role from a Cloud Service

Removing a role from a Cloud Service is simple, right click the role you want to remove and select "Remove". Visual Studio will remove the role from the service definition as well as any service configuration entries for that role.

Note that removing a role in a cloud service does not do anything to the project currently associated with that role – removing simply removes the association between the role and that project.

Setting Role Properties

Select one of the Role project associations under the Roles node, right click and select "Properties".

This will allow you to use the Properties Window to set properties on the Role such as enabling full trust or setting the instance count.

25 sites that pay you to write blogs

2010-06-08T07:05:00.001-07:00

http://www.hongkiat.com/blog/sites-that-pays-you-to-blog/

How to populate rotator control from database?

2010-06-06T03:55:00.001-07:00

Introduction

In this article i would like to present my Banner Rotator Control. The idea of it is diffrent from the ASP.Net AdRotator control. This control is a database driven and the Banners are rotating based on specific time (not page refresh). Each Banner will have a display time (in seconds) to move to next banner (a javascript is doing the rotation in client side)
The source code (ASP.Net Server Control Project), and the asp.net website for testing the control are already attached at the top of this article. Step-By-Step i am going to explain the following:

The Control Features
How to use it
The Different ways of using the Control
Explaination of the ASP.NET Server Control (Class Library)

The Control Features

Database Driven: Banner specifications such as width, hieght a clickable are derived from the database as well as the list of banners. The Banners will be loads from the database once and they will rotate on the client side
Is supports most of images formats (JPG,GIF,BMP and PNG also flash files (swf))
Cache: User can enable caching in the control and specify the cache duration. this will release the load of the database
Hit Counter functionality for each banner click
Support Multi-Languages
Fully customize by the developer: they can control the way how it will be display, repeating vertically and horizentally, reserve the banner area even if there are no banners loaded, specify conditon for banner display based on Page URL, QueryString and Used-Define conditions.

How to use this control? Get full article here....

How to send space and & using query string in .net.

2010-06-06T03:51:00.001-07:00

If you write this code and try them you will see that you have a problems with space and & characters, e.g. if you need to send a variable which contains & such as "Mark & Spencer". There must be a solution for this problem. If you look to Google�s query string you will see that it contains a lot of %20. This is the solution of our third disadvantage. Replace space with %20 and & with %26 for example.

private void btnSubmit_Click(object sender, System.EventArgs e)

{

string p1 = this.txtName.Text.Replace("&","%26");

p1 = this.txtName.Text.Replace(" ","%20");

string p2 = this.txtLastName.Text.Replace("&","%26");

p2 = this.txtName.Text.Replace(" ","%20");

"WebForm2.aspx?" +

"Name=" + p1 +

"&LastName=" + p2;

Response.Redirect(p2);

}

Since this is a such a common problem Asp.Net should have some way to solve. There it is Server.UrlEncode. Server.UrlEncode method changes your query strings to so that they will not create problems.

Previous post you may like

Advantages and disadvantages of using query string

How to pass query string in asp.net? Advantage and disadvantages of using query string.

2010-06-06T03:44:00.001-07:00

Introduction

Often you need to pass variable content between your html pages or aspx webforms in context of Asp.Net. For example in first page you collect information about your client, her name and last name and use this information in your second page.

For passing variables content between pages ASP.NET gives us several choices. One choice is using QueryString property of Request Object. When surfing internet you should have seen weird internet address such as one below.

http://www.localhost.com/Webform2.aspx?name=manas&lastName=sahu

This html addresses use QueryString property to pass values between pages. In this address you send 3 type of information.

Webform2.aspx: this is the page your browser will go.
Name: name variable which is set to manas
lastName: lastName variable which is set to sahu

As you have guessed ? starts your QueryString, and & is used between variables. Building such a query string in Asp.Net is very easy. Our first form will have 2 textboxes and one submit button.

Put this code to your submit button event handler.

private void btnSubmit_Click(object sender, System.EventArgs e)

{

Response.Redirect("Webform2.aspx?Name=" +

this.txtName.Text + "&LastName=" +

this.txtLastName.Text);

}

Our first code part builds a query string for your application and send contents of your textboxes to second page. Now how to retrieve this values from second page. Put this code to second page page_load.

private void Page_Load(object sender, System.EventArgs e)

{

this.txtBox1.Text = Request.QueryString["Name"];

this.txtBox2.Text = Request.QueryString["LastName"];

}

Request.QueryString is overloaded with a second way. You can also retrieve this values using their position in the querystring. There is a little trick here. If your QueryString is not properly built Asp.Net will give error.

private void Page_Load(object sender,

System.EventArgs e)

{

this.txtBox1.Text = Request.QueryString[0];

this.txtBox2.Text = Request.QueryString[1];

}

Some other ways to reach contents of QueryString.

foreach( string s in Request.QueryString)

{

Response.Write(Request.QueryString[s]);

}

for (int i =0;i < Request.QueryString.Count;i++)

{

Response.Write(Request.QueryString[i]);

}

Advantages of this approach

It is very easy.

Disadvantages of this approach

QueryString have a max length (most browser support 256 character), If you have to send large information this approach does not work.
QueryString is visible in your address part of your browser so you should not use it with sensitive information.
QueryString can not be used to send & and space characters.

Difference between Cloud Computing and Grid Computing. Cloud Computing versus Grid Computing

2010-06-02T08:10:00.000-07:00

Cloud computing

Cloud computing is the use of a 3rd party service(Web Services) to perform computing needs. Here Cloud depicts Internet . With cloud computing, companies can scale up to massive capacities in an instant without having to invest in new infrastructure. Cloud computing is benefit to small and medium-sized businesses. Basically consumers use what they need on the Internet and pay only for what they use.

Cloud computing incorporates infrastructure as a service (IaaS), platform as a service (PaaS) and software as a service (SaaS) as well as Web 2.0

Cloud computing eliminates the costs and complexity of buying, configuring, and managing the hardware and software needed to build and deploy applications, these applications are delivered as a service over the Internet (the cloud).

Example:

Amazon Web Services (AWS) AWS delivers a set of services that together form a reliable, scalable platform 'in the cloud'. These pay-as-you-use cloud computing services include Amazon S3, Amazon EC2, Amazon SimpleDB, Amazon SQS, Amazon FPS, and others.
Salesforce.com Delivers businesses over the internet using the software as a service model.
Google Apps - Software-as-a-service for business email, information sharing and security

Grid computing

Grid computing is a form of distributed computing whereby resources of many computers in a network is used at the same time, to solve a single problem. Grid systems are designed for collaborative sharing of resources. It can also be thought of as distributed and large-scale cluster computing

Grid computing is making big contributions to scientific research, helping scientists around the world to analyze and store massive amounts of data by sharing computing resources. Here are some real world examples of Grid Computing.

SETI (Search for Extraterrestrial Intelligence) @Home project
SETI@home is a scientific experiment that uses Internet-connected computers in the Search for Extraterrestrial Intelligence (SETI). You can participate by running a free program that downloads and analyzes radio telescope data.

BOINC (Berkeley Open Infrastructure for Network Computing)
Developed by University of California, Berkeley, BOINC lets you contribute computing power on your home PC to projects doing research in many scientific areas. BOINC is a common one for academic projects seeking public volunteers; more are listed at the end of the article.

BOINC was originally developed to support the SETI@home project before it became useful as a platform for other distributed applications in areas as diverse as mathematics, medicine, molecular biology, climatology, and astrophysics.

Folding@home (abbreviated as FAH or F@h)
Folding@home is a distributed computing project, that very simply stated, studies protein folding and misfolding. Understanding how proteins self-assemble ("protein folding") is a holy grail of modern molecular biophysics.

Einstein@Home
Einstein@Home is a program that uses your computer's idle time to search for spinning neutron stars (also called pulsars) using data from the LIGO gravitational wave detector. It also searches for radio pulsars in binary systems, using data from the Arecibo Observatory in Puerto Rico.

GIMPS Great Internet Mersenne Prime Search
GIMPS, the Great Internet Mersenne Prime Search, was formed in January 1996 to discover new world-record-size Mersenne primes. GIMPS harnesses the power of thousands of small computers like yours to search for these "needles in a haystack".

LHC@home
LHC@home is a volunteer computing program which enables you to contribute idle time on your computer to help physicists develop and exploit particle accelerators, such as CERN's Large Hadron Collider.

World Community Grid
World Community Grid's mission is to create the largest public computing grid benefiting humanity, which is funded and operated by IBM. Using the idle time of computers around the world, World Community Grid's research projects have analyzed aspects of the human genome, HIV, dengue, muscular dystrophy, and cancer.

Grids tend to be more loosely coupled, heterogeneous, and geographically dispersed compared to conventional cluster computing systems.

Difference between Cloud Computing and SaaS. Cloud Computing Versus SaaS

2010-06-02T08:05:00.000-07:00

I have often wondered what is the difference between Cloud Computing and SaaS (Software as a Service). Tonight I decided to look into this and see if I could come up with some clear definition. Let's see what we can find.

According to Wikipedia "Cloud computing is Internet ('cloud') based development and use of computer technology ('computing')." Well that makes sense. The internet has always been depicted as a cloud in network diagrams. Development of computer applications and the use of computer technology to use those applications makes up the 'Computing' part of our question.

So let's take a look at the definition of SaaS from Wikipedia. "Software as a service (SaaS, typically pronounced 'sass') is a model of software deployment where an application is hosted as a service provided to customers across the Internet." Uh oh. It seems to me that 'an application is hosted as a service provided to customers across the Internet' is pretty much the same as 'an application is hosted as a service provided to customers across the Internet' through the internet cloud of Cloud Computing.

I could have stopped here and decided that they mean the same thing. But what kept nagging at me are some companies selling SaaS services and some selling Cloud Computing services. Let's take a look at some of these and see if we can come up with some differentiation.

Difference between Virtualization and Cloud Computing. Virtualization versus Cloud Computing

2010-06-02T08:02:00.001-07:00

Cloud Computing and Virtualization are completely two different technologies. Both of them are considered as future saving technologies and that is the only similarity between them. In the process of Cloud Computing easily scalable and mostly virtualized data resources are provided to the users over the internet. Using Cloud computing colocation provider or Data Centers providers offer managed IT services via a hosted software as a service model. In Cloud Computing a database can be physically located somewhere in secure remote location and data can be accessed through clients computer using the database server to analyze and recover the data. This eliminates the need of a costly in-house equipments and department. Cloud Computing make use of virtualized resources such as servers, computing devices and networks. Cloud Computing provider own the hardware and the providing host manage all the services to the clients according to their usage basis. On the other hand Virtualization is creating virtual versions of technologies like servers, operating system, network resources and storage devices. Virtualization basically enables a single user to access multiple physical devices. In virtualization, either its one operating system using multiple computers to evaluate database or a single computer controlling several machines. Here we will learn how Cloud Computing and virtualization are different from each other.

Cloud Computing

The easily availability of computers has opened new doors in the field of information technology. Instead of creating own Data Center all the Data Center services can be acquired from IT server providers with outstanding IT infrastructure which is strong, flexible and safe. The cost which either wise would have been invested in building a Data Center can be avoided with acquiring related services on a usage basis. Fees for the managed IT services is similar to the operating costs of a Data Center. Cloud Computing seems attractive option, since it cuts a lot costs and also remove operating costs. In a traditional IT department equipments and staff are occupied in the peak hours, however in off hours they sat idle. Most of the servers are not operable in off hours, even when they are operational its not necessary that they are giving their 100% of their potential. Also a Data Center services provider also needs to upgrade latest technologies in order to keep the data safe and secure. On the other side these services are provided by Cloud Computing provider.

Virtualization

There are many types of virtualization, but all of them focus on usage format and control that improves efficiency. What this efficiency means is a single task running over multiple computers through unoccupied computer or a single terminal running multiple tasks. One more advanced advantage of virtualization is hosting an application to many users, thus preventing several needed software's from being installed again and again. Data is combined into a central computer from databases, hard drives and USB drives. This process increase security and accessibility by using replication. In an IT company physical resources can be divided into many virtual networks allowing the Central IT resources to be available to all the departments via local networks. Computer devices which are allocated to individual staff members or which are only dedicated to a specific software application are highly efficient and they are much cheaper.

What is difference between Cloud Computing and Grid Computing? Cloud Computing versus Grid Computing.

2010-06-02T08:00:00.001-07:00

Introduction

You may have wondered about cloud computing as compared to grid computing. In this article, I talk about cloud computing service types and the similarities and differences between cloud and grid computing. I look at why cloud computing may be advantageous over grid computing, what issues to consider in both, and some security concerns. I use Amazon Web Services as an example.

To get cloud computing to work, you need three things: thin clients (or clients with a thick-thin switch), grid computing, and utility computing. Grid computing links disparate computers to form one large infrastructure, harnessing unused resources. Utility computing is paying for what you use on shared servers like you pay for a public utility (such as electricity, gas, and so on).

With grid computing, you can provision computing resources as a utility that can be turned on or off. Cloud computing goes one step further with on-demand resource provisioning. This eliminates over-provisioning when used with utility pricing. It also removes the need to over-provision in order to meet the demands of millions of users.

Infrastructure as a Service and more

A consumer can get service from a full computer infrastructure through the Internet. This type of service is called Infrastructure as a Service (IaaS). Internet-based services such as storage and databases are part of the IaaS. Other types of services on the Internet are Platform as a Service (PaaS) and Software as a Service (SaaS). PaaS offers full or partial application development that users can access, while SaaS provides a complete turnkey application, such as Enterprise Resource Management through the Internet.

To get an idea of how Infrastructure as a Service (IaaS) is used in real life, consider The New York Times that processed terabytes of archival data using hundreds of Amazon's EC2 instances within 36 hours. If The New York Times had not used EC2, it would have taken it days or months to process the data.

The IaaS divides into two types of usage: public and private. Amazon EC2 uses public server pools in the infrastructure cloud. A more private cloud service uses groups of public or private server pools from an internal corporate data center. You can use both types to develop software within the environment of the corporate data center, and, with EC2, temporarily extend resources at low costsay for testing purposes. The mix may provide a faster way of developing applications and services with shorter development and testing cycles.

Amazon Web services

With EC2, customers create their own Amazon Machine Images (AMIs) containing an operating system, applications, and data, and they control how many instances of each AMI run at any given time. Customers pay for the instance-hours (and bandwidth) they use, adding computing resources at peak times and removing them when they are no longer needed. The EC2, Simple Storage Service (S3), and other Amazon offerings scale up to deliver services over the Internet in massive capacities to millions of users.

Amazon provides five different types of servers ranging from simple-core x86 servers to eight-core x86_64 servers. You do not have to know which servers are in use to deliver service instances. You can place the instances in different geographical locations or availability zones. Amazon allows elastic IP addresses that can be dynamically allocated to instances.

Cloud computing

With cloud computing, companies can scale up to massive capacities in an instant without having to invest in new infrastructure, train new personnel, or license new software. Cloud computing is of particular benefit to small and medium-sized businesses who wish to completely outsource their data-center infrastructure, or large companies who wish to get peak load capacity without incurring the higher cost of building larger data centers internally. In both instances, service consumers use what they need on the Internet and pay only for what they use.

The service consumer no longer has to be at a PC, use an application from the PC, or purchase a specific version that's configured for smartphones, PDAs, and other devices. The consumer does not own the infrastructure, software, or platform in the cloud. He has lower upfront costs, capital expenses, and operating expenses. He does not care about how servers and networks are maintained in the cloud. The consumer can access multiple servers anywhere on the globe without knowing which ones and where they are located.

Grid computing

Cloud computing evolves from grid computing and provides on-demand resource provisioning. Grid computing may or may not be in the cloud depending on what type of users are using it. If the users are systems administrators and integrators, they care how things are maintained in the cloud. They upgrade, install, and virtualize servers and applications. If the users are consumers, they do not care how things are run in the system.

Grid computing requires the use of software that can divide and farm out pieces of a program as one large system image to several thousand computers. One concern about grid is that if one piece of the software on a node fails, other pieces of the software on other nodes may fail. This is alleviated if that component has a failover component on another node, but problems can still arise if components rely on other pieces of software to accomplish one or more grid computing tasks. Large system images and associated hardware to operate and maintain them can contribute to large capital and operating expenses.

Similarities and differences

Cloud computing and grid computing are scalable. Scalability is accomplished through load balancing of application instances running separately on a variety of operating systems and connected through Web services. CPU and network bandwidth is allocated and de-allocated on demand. The system's storage capacity goes up and down depending on the number of users, instances, and the amount of data transferred at a given time.

Both computing types involve multitenancy and multitask, meaning that many customers can perform different tasks, accessing a single or multiple application instances. Sharing resources among a large pool of users assists in reducing infrastructure costs and peak load capacity. Cloud and grid computing provide service-level agreements (SLAs) for guaranteed uptime availability of, say, 99 percent. If the service slides below the level of the guaranteed uptime service, the consumer will get service credit for receiving data late.

The Amazon S3 provides a Web services interface for the storage and retrieval of data in the cloud. Setting a maximum limits the number of objects you can store in S3. You can store an object as small as 1 byte and as large as 5 GB or even several terabytes. S3 uses the concept of buckets as containers for each storage location of your objects. The data is stored securely using the same data storage infrastructure that Amazon uses for its e-commerce Web sites.

While the storage computing in the grid is well suited for data-intensive storage, it is not economically suited for storing objects as small as 1 byte. In a data grid, the amounts of distributed data must be large for maximum benefit.

A computational grid focuses on computationally intensive operations. Amazon Web Services in cloud computing offers two types of instances: standard and high-CPU.

Issues to consider

Four issues stand out with cloud and grid computing: threshold policy, interoperability issues, hidden costs, and unexpected behavior.

Threshold policy

Let's suppose I had a program that did credit card validation in the cloud, and we hit the crunch for the December buying season. Higher demand would be detected and more instances would be created to fill that demand. As we moved out of the buying crunch, the need would be diminished and the instances of that resource would be de-allocated and put to other use.

To test if the program works, develop, or improve and implement, a threshold policy in a pilot study before moving the program to the production environment. Check how the policy detects sudden increases in the demand and results in the creation of additional instances to fill in the demand. Also check to determine how unused resources are to be de-allocated and turned over to other work.

Interoperability issues

If a company outsources or creates applications with one cloud computing vendor, the company may find it is difficult to change to another computing vendor that has proprietary APIs and different formats for importing and exporting data. This creates problems of achieving interoperability of applications between these two cloud computing vendors. You may need to reformat data or change the logic in applications. Although industry cloud-computing standards do not exist for APIs or data import and export, IBM and Amazon Web Services have worked together to make interoperability happen.

Hidden costs

Cloud computing does not tell you what hidden costs are. For instance, companies could incur higher network charges from their service providers for storage and database applications containing terabytes of data in the cloud. This outweighs costs they could save on new infrastructure, training new personnel, or licensing new software. In another instance of incurring network costs, companies who are far from the location of cloud providers could experience latency, particularly when there is heavy traffic.

Unexpected behavior

Let's suppose your credit card validation application works well at your company's internal data center. It is important to test the application in the cloud with a pilot study to check for unexpected behavior. Examples of tests include how the application validates credit cards, and how, in the scenario of the December buying crunch, it allocates resources and releases unused resources, turning them over to other work. If the tests show unexpected results of credit card validation or releasing unused resources, you will need to fix the problem before running the application in the cloud.

Security issues

In February 2008, Amazon's S3 and EC2 suffered a three-hour outage. Even though an SLA provides data recovery and service credits for this type of outage, consumers missed sales opportunities and executives were cut off from critical business information they needed during the outage.

Instead of waiting for an outage to occur, consumers should do security testing on their ownchecking how well a vendor can recover data. The test is very simple. No tools are needed. All you have to do is to ask for old data you have stored and check how long it takes for the vendor to recover. If it takes too long to recover, ask the vendor why and how much service credit you would get in different scenarios. Verify if the checksums match the original data.

An area of security testing you should do is to test a trusted algorithm to encrypt the data on your local computer, and then try to access data on a remote server in the cloud using the decryption keys. If you can't read the data once you have accessed it, the decryption keys are corrupted, or the vendor is using its own encryption algorithm. You may need to address the algorithm with the vendor.

Another issue is the potential for problems with data in the cloud. To protect the data, you may want to manage your own private keys. Check with the vendor on the private key management. Amazon will give you the certificate if you sign up for it.

Software development in cloud

To develop software using high-end databases, the most likely choice is to use cloud server pools at the internal data corporate center and extend resources temporarily with Amazon Web services for testing purposes. This allows project managers to better control costs, manage security, and allocate resources to clouds a project is assigned to. The project managers could also assign individual hardware resources to different cloud types: Web development cloud, testing cloud, and production cloud. The cost associated with each cloud type may differ from one another. The cost per hour or usage with the development cloud is most likely lower than the production cloud, as additional features, such as SLA and security, are allocated to the production cloud.

The managers can limit projects to certain clouds. For instance, services from portions of the production cloud can be used for the production configuration. Services from the development cloud can be used for development purpose only. To optimize assets at varying stages of the project of software development, the managers can get cost-accounting data by tracking usage by project and user. If the costs are found to be high, managers can use Amazon EC2 to temporarily extend resources at a very low cost provided that security and data recovery issues have been resolved.

Environmentally friendly cloud computing

One incentive for cloud computing is that it may be more environmentally friendly. First, reducing the number of hardware components needed to run applications on the company's internal data center and replacing them with cloud computing systems reduces energy for running and cooling hardware. By consolidating these systems in remote centers, they can be handled more efficiently as a group.

Second, techniques for cloud computing promote telecommuting techniques, such as remote printing and file transfers, potentially reducing the need for office space, buying new furniture, disposing of old furniture, having your office cleaned with chemicals and trash disposed, and so on. They also reduce the need for driving to work and the resulting carbon dioxide emissions.

Conclusion

This article helps you plan ahead for working with cloud by knowing how cloud computing compares to grid computing, how you can resolve issues in cloud and grid computing, and what security issues exist with data recovery and managing private keys in a pay-on-demand environment. Potential consumers' demands for increased capacities over the Internet present a challenge for the developers and other members of a project team. Being aware of and resolving the issues of Web application design and potential security issues can make your team's experiences trouble-free.

Invalid object name 'sys.configurations' in SQL Azure

2010-05-26T06:49:00.000-07:00

Problem

Just trying to get started with a connection....

I was able to create a database using the Web-based Server Administration page. When I try to Add a Connection in VS 2008, the "Test Connection" succeeds, but I get the error:

Invalid object name 'sys.configurations'. (Microsoft SQL Server, Error: 208)

The same error occurs when trying to connect in SQL Server 2008 Management Studio.

At one point, I got this error:

Unable to add data connection in Server Explorer:

The current user account was invoked with SETUSER or SP_SETAPPROLE. Changing databases is not allowed.

Solution

Adding a connection to SQL Azure in VS isn't currently working. We are looking into it, but for now you can use SSMS. Follow these steps to get started:

1. cancel out of the first connection dialog that pops up.
2. click on the New Query button from the toolbar (upper left)
3. entered
Server name: full servername
Login: just the username (no @...)
4. Under Options, Connect to Database, I typed TestDb1
5. Hit connect.

How to make a database online or offline in SQL Server?

2010-05-26T06:23:00.001-07:00

In SQL Server 7.0 the functionality of taking the database offline is hidden from the Enterprise Manager. So you would take the database offline using T-SQL statement i.e. using sp_dboption procedure. For example to take the 'pubs' database offline you execute the following statement

sp_dboption 'pubs','offline','true'

But before taking the database offline ensure that no processes are using the database. You can make the database online by using the 'false' keyword as

sp_dboption 'pubs','offline','false'

Whereas in SQL Server 2000 you can take the database offline by using the Enterprise Manager i.e. By right clicking on the 'database' and then choose 'All tasks' and then choose 'Take offline'. (Or) Alter database pubs set offline (replace 'offline' with 'online' to bring the database back online) (Or) by using sp_dboption as above.

SQL Server 2005 ranking functions - ROW_NUMBER()

2010-05-25T08:12:00.001-07:00

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is

ROW_NUMBER() OVER ([<partition_by_clause>]<order_by_clause>)

Here comes the first sample query that will return the top 10 selling items in the AdventureWorks OLTP database. A rank will be assigned based on the total sales "SUM(A.LineTotal)" of each item.
The "TOP 10" clause is used to obtain the top 10 rows based on "ORDER BY SUM(A.LineTotal) DESC" after the GROUP BY clause.
You should note that adding a clause like "WHERE RowNumber <= 10" or "WHERE ROW_NUMBER() <= 10" does not work here, as the ROW_NUMBER() function is done after the result set is obtained from the query.

SELECT TOP 10 RowNumber = ROW_NUMBER() OVER (ORDER BY SUM(A.LineTotal) DESC)
  , A.ProductID
  , ProductTotal = SUM(A.LineTotal)
  , B.ProductNumber
  , ProductName = B.Name
FROM Sales.SalesOrderDetail AS A
  INNER JOIN Production.Product AS B
   ON A.ProductID = B.ProductID
GROUP BY A.ProductID, B.ProductNumber, B.Name
ORDER BY SUM(A.LineTotal) DESC

---------- following is the result ----------

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            ProductID   ProductTotal
-------------------- ----------- ---------------------------------------
1                    782         4400592.800400
2                    783         4009494.761841
3                    779         3693678.025272
4                    780         3438478.860423
5                    781         3434256.941928
6                    784         3309673.216908
7                    793         2516857.314918
8                    794         2347655.953454
9                    795         2012447.775000
10                   753         1847818.628000

==========
The following query returns the top 3 sales persons for each product.
"PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

SELECT Z.*
FROM (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name) Z
WHERE Z.RowNumber <= 3
ORDER BY Z.ProductID, Z.RowNumber

----- CTE (common table expression) can also be used for the query above -----

WITH Z
AS (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name)
SELECT Z.*
FROM Z
WHERE Z.RowNumber <= 3
ORDER BY Z.ProductID, Z.RowNumber

---------- following is part of the result ----------

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            SalesPersonID ProductID   ProductTotal
-------------------- ------------- ----------- ---------------------------------------
1                    276           707         10859.642388
2                    277           707         10410.012222
3                    285           707         9890.467534
1                    276           708         11677.535352
2                    277           708         11673.985659
3                    285           708         10129.459479
1                    279           709         1141.795500
2                    276           709         875.211250
3                    277           709         826.785950

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

2010-05-25T07:58:00.000-07:00

RANK(), DENSE_RANK() and NTILE() are newly added functions in SQL Server 2005 T-SQL syntax. Another ranking function is ROW_NUMBER() that I have blogged earlier.

RANK() returns the rank of each row within the partition of a result set. When there is a tie, the same rank is assigned to the tied rows.

For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10

DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.

For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7

NTILE(integer_expression) breaks the rows within a partition into groups, while the number of groups is specified by "integer_expression". This is useful when percentile rank is required.

NTILE(N) returns 1 for the rows in the first group, 2 for those in the second group, and returns N for the last (N-th) group. Each group contains the same number of rows, or, if the number of rows in a partition is not divisible by "integer_expression", lower-numbered groups (starting from 1, 2, ...) will each contain one more row. For example,

NTILE(2): 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6

The basic syntax follows.

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)

RANK() OVER ([<partition_by_clause>] <order_by_clause>)

DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)

NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

==========
Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

SELECT i.ProductID
  , ProductName = p.Name
  , i.LocationID
  , i.Quantity
  , RowNumber = ROW_NUMBER()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , Quartile = NTILE(4)
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , Rank = RANK()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
  , DenseRank = DENSE_RANK()
                  OVER (PARTITION BY i.LocationID
                        ORDER BY i.Quantity)
FROM Production.ProductInventory i
       INNER JOIN Production.Product p
             ON i.ProductID = p.ProductID
WHERE i.LocationID in (3, 4, 5)
ORDER BY i.LocationID, RowNumber;

----- here comes the query result -----
(ProductName is intentionally removed for better presentation)

ProductID   LocID Quantity RowNumber Quartile Rank DenseRank
----------- ----- -------- ---------- ----- ---------- ------
492         3     17       1          1     1          1
496         3     30       2          1     2          2
493         3     41       3          2     3          3
494         3     49       4          3     4          4
495         3     49       5          4     4          4
494         4     12       1          1     1          1
492         4     14       2          1     2          2
493         4     24       3          2     3          3
496         4     25       4          3     4          4
495         4     35       5          4     5          5
317         5     158      1          1     1          1
318         5     171      2          1     2          2
351         5     179      3          1     3          3
319         5     184      4          1     4          4
952         5     192      5          1     5          5
400         5     260      6          1     6          6
815         5     265      7          1     7          7
401         5     283      8          1     8          8
352         5     300      9          1     9          9
488         5     318      10         1     10         10
477         5     323      11         1     11         11
476         5     324      12         1     12         12
949         5     336      13         1     13         13
487         5     337      14         2     14         14
950         5     342      15         2     15         15
332         5     344      16         2     16         16
945         5     347      17         2     17         17
948         5     347      18         2     17         17
951         5     348      19         2     19         18
802         5     350      20         2     20         19
803         5     356      21         2     21         20
804         5     363      22         2     22         21
399         5     366      23         2     23         22
398         5     372      24         2     24         23
320         5     372      25         2     24         23
484         5     374      26         2     26         24
481         5     374      27         3     26         24
479         5     390      28         3     28         25
816         5     406      29         3     29         26
327         5     408      30         3     30         27
819         5     409      31         3     31         28
482         5     427      32         3     32         29
485         5     427      33         3     32         29
818         5     428      34         3     34         30
821         5     432      35         3     35         31
817         5     443      36         3     36         32
820         5     446      37         3     37         33
486         5     515      38         3     38         34
480         5     515      39         3     38         34
483         5     531      40         4     40         35
316         5     532      41         4     41         36
321         5     540      42         4     42         37
330         5     548      43         4     43         38
329         5     558      44         4     44         39
328         5     568      45         4     45         40
323         5     568      46         4     45         40
324         5     568      47         4     45         40
478         5     568      48         4     45         40
331         5     574      49         4     49         41
322         5     587      50         4     50         42
350         5     622      51         4     51         43

String class and String Builder class in .NET

2010-05-25T07:55:00.001-07:00

String concatenation is one of the commonly used operations among programmers. If you don't handle the string concatenation in .NET properly, it may decrease the performance of an application.

You can concatenate strings in two ways -

First, traditional way of using string and adding the new string to an existing string. In the .NET Framework, this operation is costly. When you add a string to an existing string, the Framework copies both the existing and new data to the memory, deletes the existing string, and reads data in a new string. This operation may be very resource consuming in lengthy string concatenation operations.
The second and better way to concatenate strings in .NET is using the StringBuilder class. The StringBuidler class provides the Append method, that inserts a new string to an existing string.

What is Transaction Scop class in .NET

2010-05-25T07:46:00.001-07:00

The Sytem,Tansactions infrastructure provides both an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class, in which transactions are automatically managed by the infrastructure.

It is recommended that you create implicit transactions using the TransactionScope class, so that the ambient transaction context is automatically managed for you. You should also use the TransactionScope and DependentTransaction class for applications that require the use of the same transaction across multiple function calls or multiple thread calls. For more information on this model, see the Implementing An Implicit Transaction Using Transaction Scope topic. For more information on writing a transactional application, see Writing A Transactional Application.

Upon instantiating a TransactionScope by the new statement, the transaction manager determines which transaction to participate in. Once determined, the scope always participates in that transaction. The decision is based on two factors: whether an ambient transaction is present and the value of the TransactionScopeOption parameter in the constructor. The ambient transaction is the transaction your code executes in. You can obtain a reference to the ambient transaction by calling the static Current property of the Transaction class. For more information on how this parameter is used, please see the "Transaction Flow Management" section of the Implementing An Implicit Transaction Using Transaction Scope topic.

If no exception occurs within the transaction scope (that is, between the initialization of the TransactionScope object and the calling of its Dispose method), then the transaction in which the scope participates is allowed to proceed. If an exception does occur within the transaction scope, the transaction in which it participates will be rolled back.

When your application completes all work it wants to perform in a transaction, you should call the Complete method only once to inform that transaction manager that it is acceptable to commit the transaction. Failing to call this method aborts the transaction.

A call to the Dispose method marks the end of the transaction scope. Exceptions that occur after calling this method may not affect the transaction.

If you modify the value of Current inside a scope, an exception is thrown when Dispose is called. However, at the end of the scope, the previous value is restored. In addition, if you call Dispose on Current inside a transaction scope that created the transaction, the transaction aborts at the end of the scope.

// This function takes arguments for 2 connection strings and commands to create a transaction

// involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the

// transaction is rolled back. To test this code, you can connect to two different databases

// on the same server by altering the connection string, or to another 3rd party RDBMS by

// altering the code in the connection2 code block.

static public int CreateTransactionScope(

    string connectString1, string connectString2,

    string commandText1, string commandText2)

    // Initialize the return value to zero and create a StringWriter to display results.

    int returnValue = 0;

    System.IO.StringWriter writer = new System.IO.StringWriter();

try

        // Create the TransactionScope to execute the commands, guaranteeing

        // that both commands can commit or roll back as a single unit of work.

        using (TransactionScope scope = new TransactionScope())

            using (SqlConnection connection1 = new SqlConnection(connectString1))

                // Opening the connection automatically enlists it in the

                // TransactionScope as a lightweight transaction.

                connection1.Open();

                // Create the SqlCommand object and execute the first command.

                SqlCommand command1 = new SqlCommand(commandText1, connection1);

                returnValue = command1.ExecuteNonQuery();

                writer.WriteLine("Rows to be affected by command1: {0}", returnValue);

                // If you get here, this means that command1 succeeded. By nesting

                // the using block for connection2 inside that of connection1, you

                // conserve server and network resources as connection2 is opened

                // only when there is a chance that the transaction can commit.

                using (SqlConnection connection2 = new SqlConnection(connectString2))

                    // The transaction is escalated to a full distributed

                    // transaction when connection2 is opened.

                    connection2.Open();

                    // Execute the second command in the second database.

                    returnValue = 0;

                    SqlCommand command2 = new SqlCommand(commandText2, connection2);

                    returnValue = command2.ExecuteNonQuery();

                    writer.WriteLine("Rows to be affected by command2: {0}", returnValue);

        // The Complete method commits the transaction. If an exception has been thrown,

        // Complete is not  called and the transaction is rolled back.

        scope.Complete();

    catch (TransactionAbortedException ex)

        writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);

    catch (ApplicationException ex)

        writer.WriteLine("ApplicationException Message: {0}", ex.Message);

    // Display messages.

    Console.WriteLine(writer.ToString());

    return returnValue;

What is Transaction class in .NET

2010-05-25T07:40:00.001-07:00

The System.Transactions namespace provides both an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class, in which transactions are automatically managed by the infrastructure. It is highly recommended that you use the easier implicit model for development. To get started, please see the Implementing An Implicit Transaction Using Transaction Scope topic. For more information on writing a transactional application, see Writing A Transactional Application.

The Transaction class contains methods used by developers implementing resource managers for enlistment. It also provides functionalities for cloning a transaction and controlling the current transaction context. You can obtain the current transaction, if one is set, using the static Current property.

Get the full article here.

What is static constructor in C#.NET? How many type of constructor are there in C#.NET?

2010-05-25T05:20:00.001-07:00

C# supports two types of constructor, a class constructor (static constructor) and an instance constructor (non-static constructor).

Static constructor is used to initialize static data members as soon as the class is referenced first time, whereas an instance constructor is used to create an instance of that class with <new> keyword. A static constructor does not take access modifiers or have parameters and can't access any non-static data member of a class.

Since static constructor is a class constructor, they are guaranteed to be called as soon as we refer to that class or by creating an instance of that class.

You may say, why not initialize static data members where we declare them in the code. Like this :

private static int id = 10;
private static string name = "jack";

Static data members can certainly be initialized at the time of their declaration but there are times when value of one static member may depend upon the value of another static member. In such cases we definitely need some mechanism to handle conditional initialization of static members. To handlesuch situation, C# provides static constructor.

Let me explain you with examples :

//File Name : Test.cs
using System;
namespace Constructor
{
class Test
{
//Declaration and initialization of static data member
private static int id = 5;
public static int Id
{
get
{
return id;
}
}
public static void print()
{
Console.WriteLine("Test.id = " + id);
}
static void Main(string[] args)
{
//Print the value of id
Test.print();
}
}
}

In the above example, static data member <id> is declared and initialized in same line. So if you compile and run this program your output would look similar to this :

Test.id = 5

Lets create one more class similar to class Test but this time the value of its static data member would depend on the value of static data member <id> of class Test.id.

//File Name : Test1.cs
using System;
namespace Constructor
{
class Test1
{
private static int id ;
//Static constructor, value of data member id is set conditionally here.
//This type of initialization is not possible at the time of declaration.
static Test1()
{
if( Test.Id < 10 )
{
id = 20;
}
else
{
id = 100;
}
Console.WriteLine("Static<Class> Constructor for Class Test1 Called..");
}
public static void print()
{
Console.WriteLine("Test1.id = " + id);
}
static void Main(string[] args)
{
//Print the value of id
Test1.print();
}
}
}

As you can see in the above static constructor, static data member <id> is initialized conditionally. This type of initialization is not possible at the time of declaration. This is where static constructor comes in picture. So if you compile and run this program your output would look similar to this :

Static<Class> Constructor for Class Test1 Called..
id = 20

Since <id> in class Test was initialized with a value of 5, therefore <id> in class Test1 got initialized to a value of 20.

Some important point regarding static constructor from C# Language Specification and C# Programmer's Reference :

1) The static constructor for a class executes before any instance of the class is created.
2) The static constructor for a class executes before any of the static members for the class are referenced.
3) The static constructor for a class executes after the static field initializers (if any) for the class.
4) The static constructor for a class executes at most one time during a single program instantiation
5) A static constructor does not take access modifiers or have parameters.
6) A static constructor is called automatically to initialize the class before the first instance is created or any static members are referenced.
7) A static constructor cannot be called directly.
8) The user has no control on when the static constructor is executed in the program.
9) A typical use of static constructors is when the class is using a log file and the constructor is used to write entries to this file.

Hopefully, this would clear the confusion about static constructor.

Comparing Files from Different Folders Using SQL Server and XML

2010-05-19T22:58:00.001-07:00

Problem
Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.

To further illustrate the issue I have set this up into three folders:

Source - files to be processed
Destination - files that have been processed
NewFiles - holding folder for new files to be processed after they have been compared

Elaborating the problem in depth, let's say I have three text files (or any type of file) in the Source folder and the Destination folder contains two files. The problem is to identify differences between the files in the Source and Destination folders and copy all the modified or new files from the Source Folder to the folder NewFiles.

Solution
My first task was to get all the files from the specified folder. This can be achieved via the 'DIR' command of DOS. DOS commands can be executed in SQL Server through xp_cmdshell system stored procedure, but it needs to be configured first.

The below code creates a new stored procedure called "MakeXML" which will create a XML document of all of the files in a folder. The code does a few things which you can either turn on or off as desired.

It first drops stored procedure MakeXML so it can create a new sp with this name
It enables xp_cmshell so we can shell out to DOS to get the folder listing
It does a DIR command against the specified folder with the /O-D parameter to order the list by modified date
It then inserts the data into a temporary table
Deletes any rows from this table that are not files that we want to compare
Parses the modified date
Parses the filename
Then it creates this data as a temporary XML object

What are advantages and disadvantages of cursor? Cursor Advantages vs Disadvantages

2010-05-19T22:57:00.000-07:00

Advantage of Cursor:

Row-by-row operations: Cursors are best used when performing row-by-row operations that can't be accomplished with set-based operations (i.e, when you need to fire a stored procedure once per row in a table).

 Efficiency: The query optimizer automatically selects the appropriate query plan, so the developer does not need to design a complex algorithm to access the required data.

 Adaptability: As data changes or indexes are added or dropped, the query optimizer automatically adapts its behavior by using alternative plans.

 Fewer errors: Instead of the developer handling data and algorithms in the application, the SQL Server Compact 3.5 Database Engine natively supports the required operations.

--------------------------------------------------

Disadvantage of Cursor:

 Resources consumed by cursors: it occupies memory from your system that may be available for other processes. Poorly written cursors can completely deplete available memory.

 Speed and performance issues: Cursors can be faster than a while loop but they do have more overhead. If your cursor will not be updating the base tables, use a FAST_FORWARD cursor for optimal performance. The problem with cursor speed is that, in many cases, the operation can be more efficiently written as a set operation or perhaps in a while loop. It's these cursor rewrites that lead to the impression that cursors are evil  or cursed.

 Another factor affecting cursor speed is the number of rows and columns brought into the         cursor. Time how long it takes to open your cursor and fetch statements. If it's lengthy, look carefully at your cursor logic; see if you can remove columns from the declare statement, and change your where clause in the declare statement to only return rows the cursor needs. If the fetch statements themselves are lengthy or consuming too much IO or CPU, look at the cursor declare statement and ensure you have optimal indexes in place on your base tables or temporary tables.

 Wrong tool for the wrong task: Cursors are frequently the wrong tool for the wrong task. They're used for quick-and-dirty programming when a developer does not have a good understanding of set operations or they're used for the wrong task entirely.

 Subtle errors: Cursors sometimes introduce subtle errors. We already looked at a few.

 1. Failing to check the value of @@Fetch_Status

2. Improper indexes on the base tables in your results set or

FETCH statement

3. Too many columns being dragged around in memory, which are

never referenced in the subsequent cursor operations

(probably the result of legacy code)

1.    WHERE clause that brings too many rows into the cursor,

which are subsequently filtered out by cursor logic.

Expert Programmers Blog

How do I set isolation level in DB2?

Connection Pooling and Connection Concentrator

What are simple, segmented and partitioned table spaces ?

What is check pending and copy pending status in DB2?

How to fetch top 10 records in DB2?

Comparison of Crystal Reports Server and SSRS product features

Sync Framework for SQL Azure

How COALESCE function in DB2 works?

Security in the Microsoft Cloud

How do I print the first 100 characters of a large block of text in SQL SERVER?

What is the differenct between Session state and View state?

What are the new Functionality in BCL in .NET 4 Beta 2?

What is the difference between String.Concat and String.Format? String.Concat vs String.Format.

DB2 temporary table using WITH Statement:

DB2 temporary table for single Session:

DB2 temporary tables for Single SQL Statement:

DB2 Temporary Tables

Managing roles and roles project in Microsoft Azure

25 sites that pay you to write blogs

How to populate rotator control from database?

Introduction

The Control Features

How to send space and & using query string in .net.

How to pass query string in asp.net? Advantage and disadvantages of using query string.

Difference between Cloud Computing and Grid Computing. Cloud Computing versus Grid Computing

Cloud computing

Grid computing

Difference between Cloud Computing and SaaS. Cloud Computing Versus SaaS

Difference between Virtualization and Cloud Computing. Virtualization versus Cloud Computing

What is difference between Cloud Computing and Grid Computing? Cloud Computing versus Grid Computing.

Invalid object name 'sys.configurations' in SQL Azure

How to make a database online or offline in SQL Server?

SQL Server 2005 ranking functions - ROW_NUMBER()

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is

ROW_NUMBER() OVER ([<partition_by_clause>]<order_by_clause>)

==========The following query returns the top 3 sales persons for each product."PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

----- CTE (common table expression) can also be used for the query above -----

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

String class and String Builder class in .NET

What is Transaction Scop class in .NET

What is Transaction class in .NET

What is static constructor in C#.NET? How many type of constructor are there in C#.NET?

Comparing Files from Different Folders Using SQL Server and XML

What are advantages and disadvantages of cursor? Cursor Advantages vs Disadvantages

==========
The following query returns the top 3 sales persons for each product.
"PARTITION BY" is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.