Tuesday, August 30, 2011

SQL Server 2008 : FOR XML RAW

This is amazing to see how we can get the output of SQL Queries in XML Format.

See complete article from Pinal Dave in subscription email below.



  Anugrah Atreya


From: Journey to SQLAuthority [mailto:no-reply@wordpress.com]
Sent: Tuesday, August 30, 2011 12:16 PM
To: Anugrah Atreya
Subject: Daily digest for August 30, 2011


SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - What is XML? - Day 30 of 35

Let's look at another example from the Employee table.  If you ran the reset script for this chapter, you should see 14 JProCo employees showing in your Employee table.

Next we will add FOR XML RAW to view the result from the Employee table as an XML output using the raw mode. We have changed our Employee table result to output as XML RAW. Notice that every row of our XML RAW output is labeled "row" by default.

We next will add a root to our output. We will add the keyword ROOT to our existing code (see figure below) and then look at our revised output.  We are adding the keyword ROOT in order to see a root node in our XML output. We now see the root node (a.k.a., the root element). Not only is our output more readable and organized, but this is considered "well-formed XML"

Now let's put the data into elements. We can see each employee now has three sub-elements under the top element, which is "row"

Each row has three child elements (FirstName, LastName, and LocationID). The exception is John Marshbank, who only has two elements. If we query the Employee table, we quickly see the reason for this is that John Marshbank is the only one with a NULL LocationID. John Marshbank has just two sub-elements beneath the top element, "row".

Our mystery is solved – we understand John Marshbank's having just two data sub-elements is caused by his LocationID value having a NULL value.  Suppose the program which needs to consume our result requires three data sub-elements. Or suppose company policy specifies that each employee record must contain three data sub-elements. John Marshbank's record doesn't meet the criteria and would thus be in violation of the policy.


For fields in SQL Server which include a null value for some records but are populated with regular values in the other records, you will seem to have missing tags for the null record. Often this is alright, as missing tags are presumed to be null. The XSINIL option allows you to force an XML tag to be present for every field in the query, even if the underlying data has NULL field values. Our next example will show us how to make a LocationID tag appear for John Marshbank.

If you require all tags to be present (even if they have no data), then you can specify the XSINIL option for your XML stream. The XSINIL option will force tags to be present for all fields of all records, including those which contain null values. Let's rerun our prior code and add the XSINIL option.

We now see a third sub-element for John Marshbank. The LocationID tag is no longer missing. It is present and shows the value xsi:nil="true" in place of a LocationID. Our objective has been met:  John Marshbank's record now includes three data elements thanks to XSINIL.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLInteropChapter1.0Setup.sql script from Volume 5.

Add a comment to this post


Monday, August 29, 2011

SQLServer : TABLE Valued Functions

SELECT * FROM GetCategoryProducts('Medium-stay')

Where GetCategoryProducts is not a View or Table but a function which can take input arguments as well.


See complete post in subscription email from http://blog.sqlauthority.com/ below.



  Anugrah Atreya

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Table-Valued Functions - Day 26 of 35

Answer simple quiz at the end of the blog post and -

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter5.1Setup.sql script from Volume 4.

Table-Valued Functions

Scalar-valued functions return a single value. Table-valued functions return tabular result sets ("tabular" meaning like a table). Table-valued functions look a lot like views because they both show us a tabular result set based on an underlying query. Table-valued functions can be based on one or more base tables.

Creating and Implementing Table-Valued Functions

The body of a table-valued function will essentially contain a query.  Let's begin with a query containing four fields and all of the records from the CurrentProducts table.

This query will become the heart of a new table-valued function, GetAllProducts.  By placing the query within the set of parentheses after the keyword RETURN, we have the body of the function.  The RETURNS TABLE keyword specifies that the table-valued function GetAllProducts must return the result in the form of a table.

(SELECT ProductID, ProductName, RetailPrice, Category
FROM CurrentProducts)
Just how do you query a table-valued function?  The syntax is somewhat similar to how you would run a SELECT statement against a table or a view. All functions need to be called by using a set of parentheses with all required parameters inside them. If the function has no parameters (which is currently the case with GetAllProducts), then you will simply include an empty set of parentheses.

To view all of the table-valued functions contained in the JProCo database from within the Object Explorer tree, traverse this path:

OE > Databases > JProCo > Programmability > Functions > Table-valued Functions

Views versus Parameterized Table-Valued Functions

Views and table-valued functions are both useful ways to see the result set for a pre-defined query.  There is no way to pass a variable into a view and change the way it runs.  Views are hard-coded and their criterion does not change.  A table-valued function can display different results by passing values into its parameter(s) at runtime.  Let's begin by selecting all 'No-Stay' records from the CurrentProducts table.  We want to turn this query into a function and allow that function to pick the category.

We're going to enclose our query in parentheses, indent it, and then add some code to create a function. We will create the GetCategoryProducts function which takes a @Category parameter. The query within the table-valued function will predicate on the value passed in when the function is called.

Change the parameter value to 'Medium-stay' and run this query. The GetCategoryProducts function now returns164 records.

SELECT * FROM GetCategoryProducts('Medium-stay')

Whenever you call a function, you must remember to use a set of parentheses and include the parameter(s) which the function expects. Let's demonstrate the error message which results from forgetting to include the needed parameter within the parentheses. SQL Server's error message tells us that our code doesn't match the function's parameter signature.  In other words, it reminds us that we need to specify a category.

SELECT * FROM GetCategoryProducts()

Msg 313, Level 16, State 3, Line 1
An insufficient number of arguments were supplied for the procedure or function GetCategoryProducts.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter8.1Setup.sql script from Volume 4.

Question 26

You need to create two functions that will each return a scalar result of the number of hours each user has logged for: 1) the current day, and 2) month to date.  You will pass in the user ID as a parameter value. What two things must you do?

1.     Create a function that returns a list of values representing the login times for a given user.

2.     Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.

3.     Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.

4.     Create a function that returns a number value representing the number of hours that a user has logged for the current month.


Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

Add a comment to this post



WordPress.com | Thanks for flying with WordPress!
Manage Subscriptions | Unsubscribe | Reach out to your own subscribers with WordPress.com.

Trouble clicking? Copy and paste this URL into your browser: http://subscribe.wordpress.com

Friday, August 5, 2011


We think we know everything about Design Patterns, but it's a continuously evolving world……


Found this interesting ppt on CQRS



Feed: Martin Fowler
Posted on: Thursday, July 14, 2011 7:03 PM
Author: Martin Fowler
Subject: Bliki: CQRS


CQRS stands for Command Query Responsibility Segregation. It's a pattern that I first heard described by Greg Young. At its heart is a simple notion that you can use a different model to update information than the model you use to read information. This simple notion leads to some profound consequences for the design of information systems.

The mainstream approach people use for interacting with an information system is to treat it as a CRUD datastore. By this I mean that we have mental model of some record structure where we can create new records, read records, update existing records, and delete records when we're done with them. In the simplest case, our interactions are all about storing and retrieving these records.

As our needs become more sophisticated we steadily move away from that model. We may want to look at the information in a different way to the record store, perhaps collapsing multiple records into one, or forming virtual records by combining information for different places. On the update side we may find validation rules that only allow certain combinations of data to be stored, or may even infer data to be stored that's different from that we provide.

As this occurs we begin to see multiple representations of information. When users interact with the information they use various presentations of this information, each of which is a different representation. Developers typically build their own conceptual model which they use to manipulate the core elements of the model. If you're using a Domain Model, then this is usually the conceptual representation of the domain. You typically also make the persistent storage as close to the conceptual model as you can.

This structure of multiple layers of representation can get quite complicated, but when people do this they still resolve it down to a single conceptual representation which acts as a conceptual integration point between all the presentations.

The change that CQRS introduces is to split that conceptual model into separate models for update and display, which it refers to as Command and Query respectively following the vocabulary of CommandQuerySeparation. The rationale is that for many problems, particularly in more complicated domains, having the same conceptual model for commands and queries leads to a more complex model that does neither well.

By separate models we most commonly mean different object models, probably running in different logical processes, perhaps on separate hardware. A web example would see a user looking at a web page that's rendered using the query model. If they initiate a change that change is routed to the separate command model for processing, the resulting change is communicated to the query model to render the updated state.

There's room for considerable variation here. The in-memory models may share the same database, in which case the database acts as the communication between the two models. However they may also use separate databases, effectively making the query-side's database by a real-time ReportingDatabase. In this case there needs to be some communication mechanism between the two models or their databases.

The two models might not be separate object models, it could be that the same objects have different interfaces for their command side and their query side, rather like views in relational databases. But usually when I hear of CQRS, they are clearly separate models.

CQRS naturally fits with some other architectural patterns.

  • As we move away from a single representation that we interact with via CRUD, we can easily move to a task-based UI.
  • Interacting with the command-model naturally falls into commands or events, which meshes well with Event Sourcing.
  • Having separate models raises questions about how hard to keep those models consistent, which raises the likelihood of using eventual consistency.
  • For many domains, much of the logic is needed when you're updating, so it may make sense to use EagerReadDerivation to simplify your query-side models.
  • CQRS is suited to complex domains, the kind that also benefit from Domain-Driven Design.

When to use it

Like any pattern, CQRS is useful in some places, but not in others. Many systems do fit a CRUD mental model, and so should be done in that style. CQRS is a significant mental leap for all concerned, so shouldn't be tackled unless the benefit is worth the jump.

In particular CQRS should only be used on specific portions of a system (a Bounded Context in DDD lingo) and not the the system as a whole. In this way of thinking, each Bounded Context needs its own decisions on how it should be modeled.

So far I see benefits in two directions. Firstly is handling complexity - a complex domain may be easier to tackle by using CQRS. I do have to hedge this, usually there's enough overlap between the command and query sides that sharing a model is easier. Each domain has different characteristics.

The other main benefit is in handling high performance applications. CQRS allows you to separate the load from reads and writes allowing you to scale each independently. If your application sees a big disparity between reads and writes this is very handy. Even without that, you can apply different optimization strategies to the two sides. An example of this is using different database access techniques for read and update.

If your domain isn't suited to CQRS, but you have demanding queries that add complexity or performance problems, remember that you can still use a ReportingDatabase. CQRS uses a separate model for all queries. With a reporting database you still use your main system for most queries, but offload the more demanding ones to the reporting database.

It's also true that we haven't seen enough uses of CQRS in the field yet to be confident that we understand its pros and cons. So while CQRS is a pattern I'd certainly want in my toolbox, I wouldn't keep it at the top.

Further Reading

View article...

Wednesday, August 3, 2011

Installing a Generic Text Printer

You can use the instructions below to install the Windows Generic / Text Only printer driver.

1.     Click Start | Control Panel | Printers and Faxes. If you do not see Control Panel after you click Start, click Start | Settings | Control Panel | Printers and Faxes.

2.     On the Printers and Faxes window, double-click Add a Printer.

3.     On the Add Printer Wizard window, click Next.

4.     On the Local or Network window, check Local printer attached to this computer and uncheck Automatically detect and install my Plug and Play printer. Click Next to continue.

5.     On the Select a Printer Port window, select FILE: (Print to File) from the Use the following port: drop-down list. Click Next to continue.

6.     On the Install Printer Software window, click Generic from the Manufacturer pane. Click Generic / Text Only from the Printers pane. Click Next to continue.

7.     On the Name Your Printer window, use the Generic / Text Only printer name in the Printer name: field. In the Do you want to use this printer as the default printer field, click No. ClickNext to continue.

8.     On the Printer Sharing window, click Do not share this printer. Click Next to continue.

9.     On the Print Test Page window, click No. You do not want to print a text page. Click Next to continue.

10.  Click Finish to finish adding your printer.

11.  If Windows XP displays the Print to File window, click Cancel.


Anugrah A