Whidbey Beta 2 Availability

We were expecting this first week of April but now this seems to be delayed 2 or 3 weeks. MS EMEA has a page quoting April 25th. http://www.microsoft.com/emea/msdn/betaexperience/ 

Beta 2 will include a Go Live license for the .NET Framework so from my understanding they are taking some extra stabilizing precautions to get a supportable runtime out there.

SQL Server 2005 "Yukon" Virtual "Hands On" Online Labs

SQL Server 2005 “Yukon” is in beta, but if you'd rather not go through the hassle of installing it (and trying to uninstall it) you can experience it through the magic of online virtual labs.

http://msdn.demoservers.com/login.aspx?group=sql2005

Not only is it a hosted environment, but guided hands on labs are provided for the following topics:

  • SQL Server 2005 Integration Services
  • SQL Server 2005 Introduction to SQL Server Management Studio
  • SQL Server 2005 Reporting Services
  • SQL Server 2005 Server Management Objects
  • SQL Server 2005 SQL CLR Integration
  • SQL Server 2005 SQL Query Tuning
  • SQL Server 2005 SQL Server and ADO.NET 
  • SQL Server 2005 T-SQL Enhancements
  • SQL Server 2005 Web Services
  • SQL Server 2005 XML Capabilites
  • Creating Your Own EDI Schema in BizTalk - Part II

    See.  I told you.

    Actually, this was an error of my own making.  The error that appeared on building the schema project was

    Error in inserting document definition: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__codset__2D27B809'. Cannot insert duplicate key in object 'codset'

    A similar error was displayed for the segcon and elmnts tables as well.

    The problem was that I had badly defined the EDI schema.  The schema I built had two N1Loops (that is a loop that contained N1, N3, N4 and G61 segments). The second loop also contained one more segment, meaning that I couldn't reuse the loop. But, in a moment of boneheadedness, I created duplicate definitions for the N1, N3, N4 and G62 segments.  The result was an error such as the one shown above.  The solution is to rename the duplicated segments (say N1_2) while (and this is important) keeping the tag_name attributed of the recordInfo tag at N1.

    One thing I will say is that the message that appears is completely useless for identifying the duplicated elements.  To see what went wrong, I modified the table definition for segcon, sgmnts, codset and elmnts to get rid of the primary key violation message.  Then a build would work to success.  Once the build was complete, I used the following statement to see the duplicated records.

    SELECT fc, envr, code, COUNT(*)  from codset GROUP BY fc, envr, code ORDER BY COUNT(*) DESC

    Any row with count greater than 1 would cause the violation exception.  The envr value contains the segment that is duplicated.

    004010204_004010_DEFAULT_X_<G6101>

    means that the G61 segment is being duplicated.  Realize that if G61 is duplicated, then you will also seen records for G6102, G6103, etc.

    Once I had solved all of my duplication issues, I needed to rebuild the database schema.  For me, I only had a couple of schemas, so it was no problem for me to rebuild.  I deleted all of the tables and rebuilt the table structure using the Edi.sql script found in c:\Program Files\Microsoft BizTalk Server 2004\EDI\Adapter\bin\config. If you have already compiled a number of schemas and don't want to go through it again, make a backup of the BizTalkEdiDb database before removing the primary keys and perform a restore when all of the problems have been addressed.

    Creating your own EDI Schema for BizTalk - Part I

    First off, you'll notice that the title of this blog includes Part 1.  I don't have any other parts...yet.  But from what I've seen so far in terms of paucity of documentation and general gotchas, I fully expect there to be.

    So I'm create an EDI schema for a 204 transaction.  Not a very large transaction, so it figures to be relatively easy to put together.  As it turns out, there are a couple of loops within the transaction.  Since the 204 transaction is a load tender, there is a header/detail relationship between some of the segments.  So after create the header segments, I define an element called DetailLoop.  Then, further down in the schema, I define DetailLoop as containing, among other segments, an S5 segment.

    Once the schema has been built, I build the project that contains it.  A build error is generated. Specifically, the error is:

    Segment name [DetailLoop] does not start with value of tag [S5]

    The problem is that the EDI schema validator gives more weight to the loop name than I originally thought.  It is expecting that the name of the loop start with the name of the first tag in the loop. In other words, when I changed the name of the loop from DetailLoop to S5Loop, the error went away.

    Authenticating senders and recievers in BizTalk

    Once more back into the EDI/BizTalk breach.

    While testing an BizTalk EDI-document orchestration that I created, I ran into a couple of problem.  Specifically, an EDI document was rejected because BizTalk was unable to authorize the sender and receiver.  The specific error message (as found in the Event Log) was “The recipient of the document is not recognized” or “The sender of the document is not recognized“. The documentation on exactly what is being done here is sparse, so let me describe what BizTalk is looking for.  To start with, consider the following fist couple of lines in the EDI document.  These lines are before the transactions contained within the document and are used by EDI subsystem authenticate and route the document.

    ISA*00*          *00*          *ZZ*CONTOSO        *12*4162164603     *050328*0852*U*00401*000004393*0*P*>
    GS*OW*9055551212*5198581234*20050331*1052*3733*X*004010

    When an EDI document is received through the pipeline, the subsystem opens the document to find the send.  In the above code, the sender is indicated by the 'ZZ' and 'CONTOSO' blocks in the first line.  As well, the sender id is found in the third field ('9055551212') in the second like.  These data are combined to form a party identifier. The party identifier looks like EDI://CONTOSO:ZZ:9055551212. Once the identifier is created, the EDI Subsystem examines the list of Parties to find the sender.  If not found, then an entry is put into the event log saying that the sender could not be authenticated.

    The second part of the authentication process is to authenticate the receiver. The identifier for the receiver is constructed in a similar manner.  For the above example, the recipient party identifier is EDI://4162164603:12:5198581234.  The information used to create this come from the first line (segments 6 and 7) and  the second line (segment 4).  Not only does the EDI Subsystem look for the sender party identifier in the Parties list, but also the recipient party identifier.

    What this means is that in order for the EDI Subsystem to accept an EDI document, both the sender and recipient need to be defined as a Party.  To do this, open up the BizTalk Explorer, expand the view to reveal the Parties node.  Right click on Parties and select Add Party. In the dialog box that appears, give a reasonable name for the party.  In the Properties list that appears, add a property called EDI, with a Qualifier of EDI and a Value of the sending party identifier (for our example, EDI://CONTOSO:ZZ:9055551212).  Save this new party.  Make sure that a second party is created using the receiving party identifier.  With this combination of parties, the incoming EDI documents can be correctly authenticated and fed in to the processing pipeline.

    XSD2EDI Failure locks the repository

    As a result of the problems that I described in my last post, I ran into another little documented (there seem to be a number of these surrounding EDI and BizTalk) problem.  When I rebooted my system after the Validate Schema process hung my system, I immediately tried to run it again (by definition, insanity is trying the same thing over and over, expecting a different result, isn't it?).  This type around I got an error message saying that my respository was locked.  Specifically (and for Google), the message is:

    Schema validation failed: Repository was locked on host [hostname] by process [xsd2edi], processid [nnn]

    Sympton: Build output:
    XSD2EDI failed to convert XSD: Can't lock repository because it is already locked

    It turns out that the correction for this is quite simple.  In the BizTalkEDIDb, there is a table called parame.  This table contains a single record (at least, that's what it has on my system).  One of the first in this table is called repolock.  In that field is the name of the host, process and process id that is updating (I assume) the repository.  It is set by XSD2EDI and, naturally, the information survives a system crash and restart.  To unlock your respository and be able to validate your schemas again, set the value of repolock to null using your favorite method. 

    BizTalk EDI and Virtual PC

    As you might have figured out (from my recent posts), I'm working on a BizTalk project.  Specifically, I've been dealing with the joys of trying to get the base EDI adapter working the way that I want/expect.  Yesterday, it was trying to get the 850 Schema validated. There are two items that I want to mention that got in my way.

    First, because of all the different software I try out, I'm a firm believer and user of Virtual PC.  So the BizTalk I'm using is installed on a VPC running 2K3.  The system on which it's running has 1GB of RAM, around 650MB are allocated to the VPC.

    So back to the story.  In the BizTalk project in VS.NET, you right click on a schema and select Validate Schema.  This basically runs the XSL2EDI.exe process to validate the schema and convert it into the BizTalkEDIDb tables.  On my system, I was finding that running this process would freeze my machine after about 10 minutes of running.  And by 'freeze', I don't just mean the VPC.  My entire system would seize up, requiring a hard reboot to get started again.  After having this happen a couple of times, I looked at what might be the cause.

    Turns out that the project in which the schema I was validating still had an orchestration running.  Once I stopped the orchestration, the freezing problem seems to be solved.  Or, to put it a slightly more pragmatic way, it hasn't happened since and I've validated more than one schema since then. It's only empirical evidence, but it's all that I have.

    VSTS Architect's Boot Camp Next Week (Mar 29/31) in Ottawa & Vancouver

    Next week I'm travelling to Ottawa (Tuesday) and then Vancouver (Thursday) to do some boot camp training on Visual Studio Team System. This 1 day hands on, gives folks a chance to play with the new modelling and testing features. I'll also be demoing the project management, process guidance, and integrated source control management features. If you are interested, there are still seats left. Click here for details and here for registration.

    BizTalk Server 2004 and Sql Server 2000 -> XML Auto and XML Explicit

    BizTalk 2004 provides support for processing XML documents returned from Sql Server
    via the Sql Adapter that ships with BizTalk Server 2004.

    For example:

    Select *
    From Customers
    For XML Auto

    will produce an XML document that can be processed by BizTalk 2004 received via the Sql Receive Adapter.

    The BizTalk documentation states that only the XML Auto clause is supported and
    not the XML Explicit clause. But in fact BizTalk can process Sql Server XML documents that
    are generated using the XML Explicit clause (this will be explained below).

    If you have not used the Sql Adapter before, try the following two examples in the BizTalk SDK:

    Using the SQL Adapter with a Stored Procedure in an Orchestration

    SQL Adapter (BizTalk Server Sample)

    For BizTalk to interact with an XML document originating from Sql Server,
    one of the first steps is to create a xsd schema that describes the
    XML returned from a Select statement.
    This xsd schema can be automatically generated in a BizTalk project by Adding a Generated Item, as outlined
    HERE  This is only if the Select statement has an XML Auto clause (and XMLData clause, that is removed after the
    schema is generated). If a Select Statement with a XML Explicit clause is used to generate the
    schema, the xsd schema generation will fail with an error such as : The required attribute 'name' is missing.
    An error occurred at , (0,0). Or : Failed to execute sql Statement. Please ensure that the supplied syntax is correct.

    This is possibly why the BizTalk documentation states that the XML Explicit is not supported.
    But again, BizTalk can use XML returned by XML Explicit (explained below).

    So why use the XML Explicit clause over the XML Auto clause? Simply because the XML Explicit clause
    gives you much more control of the XML structure that is returned by Sql Server compared to
    the XML Auto clause.

    For example, an Order is associated with a Shipper, Customer and Order Items. 
    The following Select Statement using XML Auto:

    Select [Order].OrderID,
     Shipper.ShipperID as ShipperID,  
     Shipper.CompanyName As ShipperName,
     Customer.CompanyName as CustomerName,
     OrderDetails.Quantity as Quantity,
     OrderDetails.UnitPrice as UnitPrice,
     OrderDetails.ProductID as ProductId  
    From Orders as [Order]
     Join Shippers as Shipper
     On [Order].ShipVia = Shipper.ShipperID
            Join Customers as Customer
       On [Order].CustomerID = Customer.CustomerID 
            Join [Order Details] as OrderDetails
     On [Order].OrderID = OrderDetails.OrderID 
    For Xml Auto

    Will return XML in the format of:


    <Orders>
     <Order OrderID="11077">
      <Shipper ShipperID="2" ShipperName="United Package">
       <Customer CustomerName="Rattlesnake Canyon Grocery">
          <OrderDetails Quantity="24" UnitPrice="19.0000" ProductId="2" />
          <OrderDetails Quantity="4" UnitPrice="10.0000" ProductId="3" />
       </Customer>
        </Shipper>
     </Order>
     <Order OrderID="11076">
      <Shipper ShipperID="2" ShipperName="United Package">
       <Customer CustomerName="Bon app'">
          <OrderDetails Quantity="20" UnitPrice="25.0000" ProductId="6" />
          <OrderDetails Quantity="20" UnitPrice="23.2500" ProductId="14" />
          <OrderDetails Quantity="10" UnitPrice="9.2000" ProductId="19" />
         </Customer>
      </Shipper>
     </Order>
    </Orders>
     
      Note: That the top level parent node Orders is added by the Sql Adapter

    The above format is not desired because the parent node
    of the Customer node is the Shipper node and the parent node of the
    OrderDetails is the Customer node. In this case the desired format of the
    XML message will have the Order Node being the parent node of the
    Shipper, Customer and OrderDetails nodes as below:


    <Orders>
      <Order OrderID="11077">
         <Shipper ShipperID="2" ShipperName="United Package"></Shipper>
         <Customer CustomerName="Rattlesnake Canyon Grocery"></Customer>
         <OrderDetails>
           <OrderDetail Quantity="24" UnitPrice="19.0000" ProductId="2" />
           <OrderDetail Quantity="4" UnitPrice="10.0000" ProductId="3" />
         </OrderDetails>     
      </Order>
      <Order OrderID="11076">
        <Shipper ShipperID="2" ShipperName="United Package"></Shipper>
        <Customer CustomerName="Bon app"></Customer>
        <OrderDetails>
           <OrderDetail Quantity="20" UnitPrice="25.0000" ProductId="6" />
           <OrderDetail Quantity="20" UnitPrice="23.2500" ProductId="14" />
           <OrderDetail Quantity="10" UnitPrice="9.2000" ProductId="19" />
       </OrderDetails> 
     </Order>
    </Orders>


    To return the XML in this (above) format, the below Select statement with a XML Explicit clause is used:

    SELECT  1     as Tag,
            NULL  as Parent,
     Orders.OrderID as [Order!1!OrderId],
     Null as [Shipper!2!ShipperID],
     Null as [Shipper!2!ShipperName],
     Null as [Customer!3!CustomerName],
     Null as [OrderDetails!4!OrderId!hide],
     Null as [OrderDetail!5!Quantity],
     Null as [OrderDetail!5!UnitPrice],
     Null as [OrderDetail!5!ProductId]
    From Orders
     Join #OrdersToGet
     On #OrdersToGet.OrderID = Orders.OrderID
    Union All
    Select  2,
     1,
     #OrdersToGet.OrderID,
     Shippers.ShipperID,
     Shippers.CompanyName,
     Null,
     Null,
     Null,
     Null,
     Null
    From Shippers
     Join #OrdersToGet
     On #OrdersToGet.ShipVia = Shippers.ShipperID
    Union All
    Select  3,
     1,
     #OrdersToGet.OrderID,
     Null,
     Null,
     Customers.CompanyName,
     Null,
     Null,
     Null,
     Null
    From Customers
     Join #OrdersToGet
     On #OrdersToGet.CustomerID = Customers.CustomerID
    Union All
    Select  distinct 4,
     1,
     #OrdersToGet.OrderID,
     Null,
     Null,
     Null,
     OrderDetails.OrderID,
     Null,
     Null,
     Null
    From [Order Details] as OrderDetails
     Join #OrdersToGet
     On #OrdersToGet.OrderID = OrderDetails.OrderID
    Union All
    Select 5,
     4,
     OrderDetails.OrderID,
     Null,
     Null,
     Null,
     OrderDetails.OrderID,
     OrderDetails.Quantity,
     OrderDetails.UnitPrice,
     OrderDetails.ProductId 
    From [Order Details] as OrderDetails
     Join #OrdersToGet
     On #OrdersToGet.OrderID = OrderDetails.OrderID
    Order By [Order!1!OrderId],Tag,[OrderDetails!4!OrderId!hide]
    For XML Explicit  

    Note: The above Sql Statement is part of a stored procedure that
    can be downloaded at the end of this blog.

    The XML Explicit clause is used with Unions of Select statements
    and the keywords -> Parent and Tag to generate the desired result.
    The Order By clause is also very important, when using XML Explicit.
    It's a good idea to  take off the XML Explicit clause to view the regular sql result set (especially if the
    format of your returned XML is incorrect or to help debug).
    A good description of the XML Explicit clause with some examples is as below:

    Using EXPLICIT Mode

    Below describes how to create a BizTalk xsd Schema for XML returned using a XML Explicit Sql Statement:

    1) Create and test (perhaps using Query Analyzer) a XML Explicit Sql Select statement
    that includes the Tag, Parent and Unions of Select Statements.

    2) Create a Sql Receive port that will invoke the Select Statement (this could be a stored proc)
    that contains the XML Explicit clause. This Sql Receive Port should use the PassThrough Pipeline.
    Note: in the Receive Location properties you must include the namespace of the
    generated XML, Root Node name, Select Statement or Stored Procedure. In this example it was :
    Document Target NameSpace : http://ObjectSharp/Orders
    Document Root Element Name: Orders
    Sql Command: exec BTSGetOrders 1000

    3) Create a File Send Port that subscribes to the Receive Port, for example create
    a filter on the Send Port with BTS.ReceivePortName = "Name of Receive Port Here"
    This Send Port should also use the PassThrough Pipeline.

    4) Turn on the above two ports and let a sample message be placed into the File Location
    of the Send Port.

    5) Create a new BizTalk Project or use an existing one and add a Generated Item. Use the Generate
    Schemas option and Select -> Well-Formed XML. Select the xml file produced in step 4).
    A xsd schema is now generated that can be used in a BizTalk Solution. If this BizTalk
    project is deployed, the Receive Port and Send Ports created in Step 2) and 3) can now use the
    standard XML Pipelines.
    Note : It may be necessary to modify the xsd schema to alter the attribute types
    min max properties etc.

    6) The produced xsd schema in 5) can now be used in a BizTalk Solution for Mapping, Messages etc.
    Orchestrations can subscribe to xml messages that are derived from a Select Statement that uses
    the XML Explicit Clause.

    Conclusion: Select Statements using the XML Explicit clause can generate XML documents
    that can be consumed by BizTalk Server 2004.


    Download the Sample Stored procedure and XSD file HERE

    Note : The sample uses tables found in the NorthWind Database that is optionally installed with
    Sql Server.

    Say goodbye to Whidbey's System.Data.SqlServer namespace

    Pablo Castro writes in the new Data Access Team Blog that they are unifying System.Data.SqlServer classes into System.Data.SqlClient.

    If you are asking “What is System.Data.SqlServer?” the answer is that it's a ADO.NET managed provider for accessing SQL Server 2005 - from within SQL Server 2005 (managed procs, triggers, functions, etc.). Why a different provider? Well SqlClient uses a TDS (tabular data stream) external access protocol to connect to SQL Server. Obviously if you are already in SQL Server you don't want to go outside and back into your database. The SqlServer namespace provides similar classes (SqlConnection, SqlCommand, etc. etc. that work within the current context of your connection to the database.

    In the existing betas, MS was so nice to give them the same class names so that you might have an easy chance of porting data access code from client code to server code by changing the using/Imports <namespace> at the top of your class. You have a little bit of work to tweak your connection (if you really need one), but otherwise this porting process works pretty well. If you want the most efficient code, you also need to break your SqlCommands into static SqlDefinitions and SqlExecutionContexts for the actual executions. You also may need to output differently using the SqlContext's Pipe object.

    So the product team has decided to unify, removing the SqlServer namespace and making the SqlClient components serve dual duty. This magic switch happens simply by using a connection string of “context connection=true”.

    I have mixed emotions about this move. On one hand, it can make the porting process a bit smoother. You don't have to changing your using/Imports and you don't have to remove your connection object. It also means you'll instantiate SqlCommands the same way.

    On the other hand, it's not obvious to me by reading somebody's code if they are talking in-context to the  database, or out of context externally. In fact, this could very well switch based on a configuration setting for the connection string. The differences are not just related to performance, but also affect the transactional semantics. It would be very easy to switch code that is acting in-context as part of a transaction, to an external command that is not participating in a transaction by a simple configuration change. This is a big deal. I don't think transactional semantics should be a “configurable” thing. My opinion extends to the COM+ component catalog, but that is a whole other blog entry.

    This move ends up simplifying an API so much that a programmer's intentions are lost, and that's not a good thing. Pablo says this is coming in SQL Server Beta 3, but he wants your opinions.