Tuesday, 5 February 2008

Tips and Tricks for SQL Queries

Tips and Tricks for SQL Queries
( SQL Server 2000/later )

Table Design

EmployeeName Varchar(50)
JobGrade Char(1)
NetSalary Money

Insert rows

CrissBron A 7000.00
MikeMiles A 5600.00
John A 5000.00
Armados B 3000.00
LeeBron C 2100.00
ThoiChin C 2000.00

1) Find all employees salary starts from Hign to Low
Select * from Employee Order bY NetSalary Desc

2) Find Nth Maximum Salary of employee
a) finding 2nd maximum salary employee

Select Min(netSalary) From
(Select Top 2 NetSalary From Employee Order By NetSalary desc)
As A
b) Finding 5th maximum salary employee
Select Min(netSalary) From
(Select Top 5 NetSalary From Employee Order By NetSalary desc)
As A

3) Add Sequence Number/Row Number and display all rows

Select * from
From Employee
) as A

4)Create New table and record Cloning
Select * Into NewEmployee From Employee
: NewEmployee is new table name

Monday, 4 February 2008

Biztalk Interview Questions ad Ans

1)What does the Value Mapping Functoid do?
A)Returns the second parameter if the first parameter is true

2)How do you use Auto Mapping in the BizTalk 2004 mapper?
A)Select a top level node, hold down the Shift key, drag the top level node over to the other top level node.

3)What is the default mapping for Auto Mapping?
A)The default is by structure. This can be change to by node name on the map properties. (This default in the auto map messes me up every time.)

4)Step-by-step, how do you set up a basic, pure messaging solution taking in a flat file and send an Xml File out to 5 different places?
A) Create a Flat File schema
Create a Custom Pipeline using the Flat File Disassembler
Create a Receive Port
Create 5 Send Ports
Create a Send Port Group
Add each Send Port to the Send Port Group
Create a Filer on the Send Port Group using Bts.ReceivePortName (or something close to this)
Additional: no map is required but it is ok to mention it

5)What is the difference between a Distinguished field and a Promoted Property?
A) Distinguished fields are light weight and can only be used inside an Orchestration.
Promoted Properties are defined inside a property schema, are tracking in SQL, can be tracked in HAT, and can be used for content based routing

6) How do you achieve First-In-First-Out message processing of messages received from multiple sources using an Orchestration?
A) Use a Sequential Convoy to process the messages in the order they are received into the Message Box. Make sure Ordered Delivery is set to True inside the Orchestration Receive Port.

7)At high level, what do Receive Ports and Orchestration Send Port really do in terms of messaging? What about Send Ports and Orchestration Receive Ports?
A) Receive Ports and Orchestration Send Port are both publishers.
Ports and Orchestration Receive Ports are both subscribers

8)When working with Schemas, Maps, Pipelines, and Orchestrations how should the projects be structured?
A) Schemas and Maps in its own project
Or Schemas and Maps together in its own project
Orchestrations in its own project
Pipelines in it own project

9)What is direct binding?
A) Direct binding has three types: direct to message box, self correlating, and partner ports.
Used to route message between the message box and Orchestrations without using bindings or from one Orchestration to another Orchestration.

10)What is BAM used for?
A) BAM is used to monitor business milestones and key metrics in near real-time throughout a process in BizTalk.

11)What is the Rules Engine?
A) Rules are used to provide highly efficient, easily changeable business rules evaluation to Business Processes. This allows rules to be changed without rebuilding and redeploying .net assemblies. The Business Rules Engine (BRE) can also be called from any .net component through the API’s.

12)What are Persistence Points and what causes them?
A) Persistence is when the state of a running Orchestration is stored into SQL.
It is good enough to know various shape and actions cause persistence. More specifically,
it occurs: end of a transactional scope, at a send shape, at a start Orchestration shape, during dehydration, if the system shuts down expectedly or unexpectedly, or the business process suspends or ends.

13)What is the difference between a Document Schema and a Property Schema?
a)A document schema is like any regular schema, whereas a Property schema consists of only child elements under a root node.

14)Can an Envelope schema consist of more than one schema type?
A)Yes. Technically it is possible.

15)Can a flat file message be processing without a pipeline?
A)A Pipeline's job is to convert any external format into XML, be it a flat file or EDI or anything else.

16)Can multiple messages be processed or batched without an envelope schema?
A)It is possible to process multiple messages, without an envelope.

17)What is property promotion, why is it required?
A)When a property is Promoted, it is exposed to the orchestration/send port filters etc.

18)In which scenarios would use a "promoted property" vs "distinguished fields"? A)The rule here is, if you dont want the schema element to appear in send port
filters/debugging information then make it a distinguished field.

19)In Biztalk, what does a message type consist of?
A)A message type consists of the TargetNamespace#RootElement name

20)What are un-typed messages, how does one create them?
A)A message created in BizTalk Orchestration is bound to a schema, this is a typed message.
In un-typed messages, the message is bound to System.Xml.XmlDocument instead of a schema.

21)How does one enable subscriptions in BizTalk?
A)A filter on the Send Port is the first step to enable subscriptions in BizTalk.

22)What is the difference between a delay shape vs a listen shape?
A)A 'Delay' is very much similar to a sleep on the current thread.
A 'Listen' shape is used to wait for an incoming resource, with a timeout period.

23)When you use Call Orchestration shape vs Start Orchestration shape?
A)A Call Orchestration returns the control back to the caller.
A Start Ochestration shape starts the orchestration in a non-deterministic way.

24)What is the difference between a "Message Assignment" shape and an "Expression" shape?
A)A "Message Assignment" shape is used to create a new message and assign values to it.
A Expression shape is used to assign values to variables and also write 'if' conditions.

25)Does BizTalk Orchestrations support recursion?
A)An Orchestration does NOT support recursion.

26)What is the purpose of the property "Activate" in a Receive shape?
A)It is used to invoke a new instance of an Orchestration.

27)Can an orchestration Start without an Activatable receive?
A)A Nested Orchestration can be started without an Activatable receive

29)Is it necessary for all .NET components being called from an Orchestration be Serializable?
A)Yes it is necessary. There are cases where a .NET component need not be Serializable.

30)When do we need set the property "Synchronized" = true for a scope?
A)This needs to be set, when a variable is shared across the branches of a parallel shape.

31)What is the difference between an Exception block and a Compensation block? is it the equivalent of try-catch-finally?

32)In an Orchestration design, Orchestration "A" calls another Orchestration "B", and vice versa. Is it possible to implement this design?
A)It is NOT possible, since it forms a cyclic dependency.

34)List out the three important things to consider while designing a BizTalk orchestration!
A)The Incoming data format, The Business process and The Outgoing data format

35)What is BizTalk?
A)Biztalk is a messaging based integration tool.

36) What is a Message Type (i.e. BTS.MessageType) and how is it used in BizTalk?
A) Message Type is a BizTalk System property that is promoted inside a Pipeline.
It is made up of Document Name Space # Root Node Name.

37) What are the types of Transactions available?
a) Automic , LongRunning, None

38) Explain the Isolation levels in biztalk.
A) BizTalk supports three isolation levels. These are 'Read Committed', 'Repeatable Read' and 'Serializable'.
The last one being the default value.

39) Whend do you set the Synchronized="true" ?
A) When two scopes in a parallel branch are set to Synchronized="true",
then they would be executed independent of each other in such a way that each branch assumes that it is the only one being executed in the system.

40) SN key is required to deploy the Orchestrations?
A) Yes

Biztalk SQL Adapter

This simple SQL Adapter example. Read the article and follow the steps.
Create Solution with name OrderManager.

Right click the Solution and Click Add new Item and select Schema1.XSD

Rename the Schema1.xsd to IncompleteOrder.xsd
Create the XSD structure as mentioned in below image.

Repeat the Step2 to create CompleteOrder.xsd Schema and create the schema structure as mentioned in below image.

Step-4 : create stored procedure in SQL Server.
Create procedure TestSP
(@XMLMsg ntext)
DECLARE @ErrCode Int
INSERT INTO SAP_Import_Msg (iMessage_No, sMessage_XML) VALUES (1, @XMLMsg)
SET @ErrCode = @@ERROR
SET @ErrCode = 0


Select @ErrCode as ErrorCode for XML RAW, XMLDATA

once you complete the Step-4 successfully, you need to extract the XSD schema in your Biztalk application.

Step-5 will explain you how to extract the schema from your stored procedure.

Right-click on the project in the Solution Explorer and select the "Add Generated Items" option. In the list of items, select the Add Adapter option and click on the Open button. The following screen will show up:

In this screen, select the adapter of type "SQL" and click Next
In the first screen, click on the Set button and provifde the information to connect to your SQL Server instance. Select "Northwind" as the initial catalog. When the connection string is set, click Next.

The next screen shows information about the schemas that will be used to transport the information to and from SQL. In the Target Namespace option, type "http://nwtraders". In the Port-Type, select "Send Port", since we're going to send a request to SQL Server and receive a response. In the property "Request root element name", type "InCustomer", and in the property "Response root element name", type "OutCustomer". The screen should be as in the picture below:

Click on the Next button. In the screen "Statement type information", select the option "Stored Procedure". Click Next. In the combo box for selecting the procedure TestSP.

This information will be used by the SQL adapter to generate the initial schema, they are not used later in the project. Click on the Next button, and in the next screen, click the Finish button. A new schema and a new orchestration will be created in your project.

The created schema (SQLService) contains the request and response information for the stored procedure. The orchestration contains some types (port type) used to call the SQL adapter.

Once you compelte the step-5 successfully open your mapping sheet and select Incomplete order Schema in Source side and TestSP Schema in Target Side.

Do the mapping as mentioned below.

write the below code before you use the Transform Shape.
TempXMLDocument =new System.Xml.XmlDocument();

vXMLSPDoc = new System.Xml.XmlDocument();
vXMLSPDoc = InboundMsg;InboundMsg.TestSP.XMLMsg=vXMLSPDoc.InnerXml;

once you complete the above steps you can Deploy the Orchestation. You can youse the below Sample Test File.

Sample Input Values

< ns0="http://OrderManager.IncompleteOrder"> <>1 <>2005-03-01 <>ALFKI

If you need any more details mail me @ raj.webjunky@yahoo.com