Archive

Archive for July, 2011

Customize Binaries Folder in TFS Team Build Part-2


In this post I will go through a different way to accomplish what I posted in Part-1. Actually this approach is way simpler and less error prone than the first one.

I will create a new configuration named TFS, change the output path for each project for that configuration to  SolutionPath\bin\Projectname, Create a team build project and update Team build template.

Create a New Configuration

We want to create a new configuration for TFS team build and we don’t want to change the Release or Debug configurations. To do so

  • Right click on the solution
  • Click configuration manager menu

  • From the “Active solution configuration” drop down select <New…>

  • Type TFS in the name textbox
  • Select the configuration to copy configuration from (e.g. Release, or debug)
  • Click OK

  • In the Solution configuration, I am changing the Active Solution Platform to Mixed Platform because the projects in the solutions are targeting different platforms

  • Click Close

Preparing the Projects in the Solution

The following steps will change the output path from bin folder to SolutionPath\bin. I my solution I had a webApplication and a windowsApplication

  • Make sure that the TFS Configuration is selected
  • Change the Output location for each project on the solution to ..\bin\ProjectName.
    • Right click on the projectà properties
    • Click the build tab
    • Fill the “output path” textbox with ..\bin\ProjectName

  • Build the solution to make sure it is working

  • Check in the solution

Create a new Team Build

  • Open Team Explorer
  • Right click on the Builds node
  • Create a new Build, I named it CustomizeBinariesFolder
  • In the Process Tab
  • Expand Items to build
  • Select the solution we configured above
  • Expand Items to build
  • Click the ellipsis next Configurations to Build

  • Type TFS under Configuration and the Platform of that configuration
  • Fill the other fields as you want

Create new Team Build Template

  • Edit your Team Build Definition
  • Go to the process tab
  • Under Build Process Template click the New… button
  • Select an existing template that you want to copy, I am selecting the DefaultTemplate
  • Name the new File
  • Click OK

  • Save the definition
  • Open Source Control from Team Explorer
  • Get latest from BuildProcessTemplates

  • Open CustomizeFolderTemplate.xaml
  • Find “Run MSBuild for Project” Activity under “Try to Compile the Project” block.
  • Clear the OutDir property

  • OutDir property over writes the default output path on each project. By Clearing the OutDir property for the MsBuild Activity, Team build will use the Output Path defined in the project. In my case, C:\Builds\{AgentNumber}\Lajak\CustomizedBinariesFolder\Sources\WindowsFormsApplication1\bin
  • Now we want to copy the contents of the bin folder from the above path (i.e. solutionPath\bin) to the binaries folder
  • Add Copy Directory Activity under Run MsBuild for Project

  • Change the Source Property to IO.Path.Combine(IO.Path.GetDirectoryName(localProject), “bin”), which means, Path of solution file + “\” + bin. By using the combine method we don’t have to worry about the Locale of the machine
  • Change the Destination property to BinariesDirectory
  • Save the Template
  • Go to Source Control Explorer and Check in the Template
  • Run the Team build definition that uses the template you have just modified. I am building the CustomizedBinariesFolder Definition


Backup/export Oracle database tsn name


Today I wanted to backup/export an Oracle database so that I can restore it to another computer. I am not a dba and most of my “dba” experience is related to SQL server. Long story short, after around 30 minutes of research I had to use the following syntax in the command prompt:

exp file=”C:\OracleBackups\Mydatabase201107221422.dmp full=yes

Make sure that the folder you are export to DOES exist. exp won’t create for.

It will then ask you to enter the username which must be in the following format username@tsnname (e.g. ahmed@XE) You need to use a username with sufficient privileges

Press enter and it will prompt you to enter your password.

Enter the password and you are set 🙂

When I restore/import the database I will update this post.

Cheers for now

Categories: Oracle Tags:

Biztalk Configure WCF-SQL Adapter for Outbound Operation using Typed Stored Procedure


  • Follow the steps in section Consume Adapter Service
  • Select sqlBinding from the dropdown
  • Click the Configure button

  • In the secure tab, select Windows from the “client credential type” drop down
  • In the URI Properties tab, enter the Server name, InstanceName and IntialCatalog. In the following figure the server is “LocalHost”, we are using the default instance, and IntialCatalog the name of the adventureworks2008R2 database

  • Click Binding Properties

  • Click Ok
  • Click the Connect button
  • Select Client (Outbound operations)
  • Click Strongly-Typed Procedures from the “Select a category” list
  • Select a stored procedure from the “Available categories and operations” list
  • Click the Add button. Notice that the stored procedure is now list in the “Added categories and operations”
  • You can click the properties button to view the schema that will be generated
  • You can add multiple stored procedures if you want to have them all under one schema

  • Click OK
  • Notice in the solution explorer window that two files are add; one is the xsd schema and the other is the binding xml file

  • IMPORTANT:
    • You can add the schema to the Schmas or any other folder. Make sure you modify the name space
    • You can change the Type Name and File Name

    • Open the XSD file by double clicking on it
    • Select the Schema Node

    • Make sure that you DO NOT change the target Namespace
    • Make sure you DO NOT modify the elements’ names

  • Import port binding by following these steps
    • Open Biztalk admin console
    • Expand Biztalk server administration
    • Expand Biztalk groups
    • Expand Applications
    • Right click on the application you want to import the port to
    • Select Import à bindings
    • Select the generated xml file from step 16
    • Select the send ports node

    • Double click on the send port

    • Click Configure
    • If you only have one operation, Copy the value of the Action element, clear the textbox and past the copied value into the textbox

    • Click Ok

Consume Adapter Service in Biztalk Application

  • Right click on the Biztalk project
  • Click AddàAdd Generated Items

  • Click Consume Adapter Service

  • Click Add
Categories: Biztalk, Biztalk 2010, WCF Tags: , ,

Configure WCF-SQL Adapter for Inbound Operation with FOR XML Clause


Add Generated Item option in visual studio does NOT generate proper schema when using FOR XML EXPLICIT clause. This post shows how to configure a receive port to consume WCF service. Make sure that your application has been deployed before following the next steps

The following steps are to configure the WCF-SQL adapter to poll data:

  • Open Biztalk server administrator console
  • Expand your application
  • Select Receive ports node

  • Right click Receive Ports node
  • Select NewàOne-way Receive Port

  • Name the port
  • Click Receive Locations on the left panel
  • Click new to add a new Receive Location

  • Name the new receive location

  • Set Type dropdown to WCF-SQL
  • Set Receive pipeline to XMLReceive or any other custom pipeline
  • Click Configure button. You will get the following dialog

  • In the General tab, enter the SQL Server Address URI or click Configure. Clicking Configure shows the following dialog

  • Fill the following properties
    • Server name: for this walkthrough, I am using localhost because the SQL server is on the same machine as the dev box
    • InstanceName: leave blank if the instance name is the default instance
    • InitialCatalog: name of the database that contains the stored procedure
    • InboundId: use the stored procedure name. InboundId is used to make the URI unique. Biztalk requires the URI to be unique in an application. For more info read http://msdn.microsoft.com/en-us/library/dd787908(v=BTS.70).aspx
  • Click OK
  • Go to the Binding tab and fill the following properties

    • XmlStoredProcedureRootNodeName : The adapter will insert a root element to the received schema with this name and the name space of XmlStoredProcedureRootNodeNamespace. Make sure that the name matches the name in the xml document
    • XmlStoredProcedureRootNodeNamespace: The namespace for the RootNode in the previous step to be injected by the adapter. Namespace must match the namespace in the xml document
    • InboundOperationType: Because our stored procedure contains FOR XML EXPLICIT, we must use XmlPolling
    • PolledDataAvailableStatement: The sql statement is executed by adapter to check if data is available for polling
    • PollingIntervalInSeconds: How often, in seconds, do we want to poll data from the database. The lower the number, the more frequent the adapter hits the database and the busier Biztalk application will be in case the data is available
    • PollingStatement: the sql statement to be executed to poll data from the database
    • pollWhileDataFound: Set to false. For more info read http://msdn.microsoft.com/en-us/library/dd788416(v=BTS.70).aspx
    • UseAmbientTransaction: true to enable transactional behaviour
  • Configure the other tabs if needed
  • Click OK to finish configuring the receive location
  • Click OK to finish configuring the receive port
  • Now your port is ready for usage
Categories: Biztalk, Biztalk 2010, WCF Tags:

Generate XSD Schema from XML using Visual Studio


This article uses Biztalk Schema Editor update some attributes. You can also update them directly in the XSD.

  • Open an XML document in Visual Studio
  • Click Create Schema from the XML menu

  • We need to validate that the created schema matches xml output received from SQL.
  • Save the Schema XSD file into a folder using FileàSave As
  • Close the XSD file
  • Reopen the XSD file using the Biztalk Schema Editor
    • Click File menu
    • Click Open àFile
    • Select the XSD file
    • Click Open with

    • Select Biztalk Editor
    • Use the editor to update properties of elements and attributes
  • Using the Create Schema tool to generate XSD from XML may not generate valid information about elements and attributes. We need to take a look at each element and attribute and make sure that the following properties are correct:
    • maxOccurs: unbound means that there isn’t max number (i.e. unlimited)
    • type: make sure that the type matches the column type in the database. For exmple, in the persons schema, the Generate Schema tool assigned the type for addressId attribute to be unsignedShort while the correct type should Integer
    • use: Optional, Required, or Prohibited
    • nilable: true if the element can be null, false othewise
Categories: Uncategorized

Weakly type vs strongly type stored procedures in Biztalk


The difference between Weakly Type vs strongly type Stored procedures in Biztalk is the way schema is generated. For a procedure under the Procedure node in Biztalk, the schema is weakly typed. However, for a procedure under the Strongly-Typed Procedure node, the schema is strongly typed.

Strongly-typed schema is useful if you want to map schema of one operation to another operation using BizTalk mapper because the schema is available to you in design-time while creating the BizTalk project. For weakly-typed procedures, the schema for the procedure is received at run time as part of the response message.

To learn more visit the following article at msdn: http://msdn.microsoft.com/en-us/library/dd788445(v=BTS.70).aspx

The following figures shows the difference between the schemas generated when picking weakly type vs strongly type stored produces

Categories: Biztalk Tags:

Create stored procedure to output structured XML using FOR XML EXPLICIT


This article gives the steps to create a stored procedure to output structured XML using For XML Explicit. I am assuming you already know what xml explicit is and you want to know the steps to create it. The article uses SQL Server 2008 R2 and AdventureWorks2008R2 database which can be downloaded from: http://msftdbprodsamples.codeplex.com/

To output data in a customized XML structure, we need to use FOR XML EXPLICIT clause. The recommendation is to use the following steps:

  • Write a select statement per table

use AdventureWorks2008R2;
go
SELECT [BusinessEntityID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Person].[Person]

SELECT [BusinessEntityID]
,[AddressID]
,[AddressTypeID]
FROM [Person].[BusinessEntityAddress]

SELECT [AddressTypeID]
,[Name]
FROM [Person].[AddressType]

SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
FROM [Person].[Address]

SELECT [StateProvinceID]
,[Name]
FROM [Person].[StateProvince]

SELECT [BusinessEntityID]
,[PhoneNumber]
,[PhoneNumberTypeID]
FROM [Person].[PersonPhone]

SELECT [PhoneNumberTypeID]
,[Name]
FROM [Person].[PhoneNumberType]
GO

  • Now try to join the statements to group the columns that need to be displayed in one element in your final XML document. For example, we want to display the columns of BusinessEntityAddress, AddressType, and Address in one element.

use AdventureWorks2008R2;
go

SELECT [BusinessEntityID]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Person].[Person]
SELECT E.[BusinessEntityID]
,A.[AddressID]
,T.[Name]
,[AddressLine1]
,[AddressLine2]
,[City]
,S.[Name]
,[PostalCode]
FROM [Person].[Address] A
INNER JOIN [Person].[BusinessEntityAddress] E
ON A.AddressID = E.AddressID
INNER JOIN [Person].[AddressType] T
ON E.AddressTypeID = T.AddressTypeID
INNER JOIN [Person].[StateProvince] S
ON S.StateProvinceID = A.StateProvinceID

SELECT [BusinessEntityID]
,[PhoneNumber]
,T.[Name]
FROM [Person].[PersonPhone] P
INNER JOIN [Person].[PhoneNumberType] T
ON P.PhoneNumberTypeID = T.PhoneNumberTypeID

GO

  • Now we are ready to output the above statements as one XML file. We will convert the above three result set s to three XML elements with the following hierarchy
<Person personId="1" fname="Ken" mname="J" lname="Sánchez">
<Address AddressId="249" type="Home" AddressLine1="4350 Minute Dr." city="Newport Hills" province="Washington" postalCode="98006" />
<Phone type="Cell" pnumber="697-555-0142" />
</Person>
  • To use FOR XML EXCIPLICT, each result set must have a unit Tag number as a column. For instance, the first result set Tag = 1, the second result set Tag = 2 and the third = 3. The tag number doesn’t appear in the final XML document but it is used as an ID for each result set so that we can relate them to each other. Each result set must have a Parent field as well. The parent field reference the Tag number of the result set’s parent. For example, the second result set’s parent field must be set to 1 which is the tag number of the first result set. The third result set should have the same values for the Tag and Parent fields as the second result set because they are both on the same level and have the same super node

Figure 3 FOR XML EXPLICIT syntax

From the above figure notice the following:

  • We are using Union All clause to Union the three result sets
  • Each result set must have the same number of fields in the select statement
  • First result set’s Parent is set to 0 (can be set to NULL as well) to indicate that it is the Root node while the send and third result sets’ parent is set to 1 to indicate that they are child nodes of Person.
  • Aliases are used for each field.
  • Alias format is [ElementName!TagNumber!AttribteName] and the valid of that attribute is the value of the field. For example, Element Name = Person, TagNumber =1, AttributeName=personId and the value of personId = 1.
  • Fields from other result sets are set to NULL
  • Tag number is not in the output XML file
  • Result sets two and three have a reference to their parent (i.e. person) using the BusinessEntityID (i.e Primary Key)
  • We must use Order By in the last result set otherwise the XML will not be formatted as desired
  • FOR XML EXPLICIT is only referenced in the last result set

use AdventureWorks2008R2;
go

SELECT 1 as Tag,
0 as Parent,
[BusinessEntityID] as [Person!1!personId],
[Title] as [Person!1!title],
[FirstName] as [Person!1!fname],
[MiddleName] as [Person!1!mname],
[LastName] as [Person!1!lname],
NULL as [Address!2!AddressId],
NULL  as [Address!2!type],
NULL  as [Address!2!AddressLine1],
NULL as [Address!2!AddressLine2],
NULL as [Address!2!city],
NULL as [Address!2!province],
NULL as [Address!2!postalCode],
NULL as [Phone!3!type],
NULL as [Phone!3!pnumber]
FROM [Person].[Person]

UNION ALL

SELECT 2 as Tag,
1 as Parent,
E.[BusinessEntityID],
NULL,
NULL,
NULL,
NULL,
A.[AddressID],
T.[Name],
[AddressLine1],
[AddressLine2],
[City],
S.[Name],
[PostalCode],
NULL,
NULL
FROM [Person].[Address] A
INNER JOIN [Person].[BusinessEntityAddress] E
ON A.AddressID = E.AddressID
INNER JOIN [Person].[AddressType] T
ON E.AddressTypeID = T.AddressTypeID
INNER JOIN [Person].[StateProvince] S
ON S.StateProvinceID = A.StateProvinceID

UNION ALL

SELECT 3 as Tag,
1 as Parent,
[BusinessEntityID],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
T.[Name],
[PhoneNumber]
FROM [Person].[PersonPhone] P
INNER JOIN [Person].[PhoneNumberType] T
ON P.PhoneNumberTypeID = T.PhoneNumberTypeID
ORDER BY [Person!1!personId], [Address!2!AddressId]
FOR XML EXPLICIT

  • Run the statement by hitting F5.
  • Click on the output field

Figure 4 Output of statement with FOR XML EXPLICIT clause

  • Validate the structure of the XML Document
  • Save the XML file in a directory with a friendly name

Figure 5 Saving XML document

  • Create a new stored procedure

Figure 6 Creating new stored procedure

  • Copy the select statement and paste it into the body of the stored procedure
  • Execute the script.
  • Validate the stored procedure exists under the stored procedures folder
Categories: Sql Server, Xml Tags: ,