Eat Frozen Yogurt!

My first post in this blog was about motivating software developers with chocolate.

No, I have to advertise a frozen yogurt cafe of friends in Hamburg-Winterhude, which offers delicious frozen yogurt - ice cream mixed with fruits or chocolates - and good gepa coffee (which since all times has motivated developers). If you can't go there by foot (Dorotheenstraße 99), you should at least visit them online: FrozenYogurtWelt bei Facebook and the FrozenYogurtWelt homepage.

Good articels on PARTITION

Here are some articels about PARTITION in SQL Server 2008 that helped me:

• Alan Granfield talks about Myths and Truths of partitioning.
• Microsoft published a good white paper about partitioning and index strategies in SQL Server 2008.
• A good explanation on how to move a partition into another filegroup comes from Dan Guzman.
• Feodor Georgiev writes about how to risk losing data and how to have most down time.

And, a bit of propaganda for myself:

• I wrote a guest post on Feodor's blog sqlconcept.com about how to generally handle new and old data with partitioning (using a Louis Vuitton and an army bag as an example).

Query a SQL Server Database while it is still restoring

PARTITION is an SQL Server Enterprise Edition feature that can be used to spread data over different partitions, which can be put into different filegroups.
When you think about Backup and Restore strategies, a neat thing is that secondary filegroups can be set read-only and then don't have to be backuped permanently (simply because the data keeps unchanged). Thus, your backup runs faster, because you only backup readwrite filegroups, and thus the backup doesn't effect your database so much. Furthermore, you can do piecemeal restore after a disaster, which means that you can make the database available already after restoring the primary filegroup. So the users can query the restored part of the database until you restored all the filegroups of the database.

The following example shows how queries to the database work, if one filegroup isn't restored.

In the example, there are two tables with ID and CreateDate. The second table has a foreign key to the ID of the first table. The tables are partitioned with column ID at value 3, so that ID 1 and 2 of each table reside in partition 1, and ID 3 and 4 in partition 2. The first partition is on filegroup 1, the other on filegroup 2. A join query an the two tables returns this result:









In case of a crash, we would restore first the primary filegroup. If we run the same query against all the data in the tables, we get an error because not all data is available:
Msg 679, Level 16, State 1, Line 8
One of the partitions of index 'PK_Transfile' for table 'dbo.Transfile'(partition ID 72057594039238656) resides on a filegroup ("SECONDARY") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

In order to make your query run, you have to restrict it to the restored partitions. In this case, you have to restrict the query so that the ID columns of the tables are smaller or equal to 2. Since the partition column is known, the SQL processor knows that it only has to query the first partition and, thus, only enters the first, restored filegroup. The result then is not complete, but it doesn't return an error:







This way, you can use PARTITION to get your system run much faster, you can shorten your backup time and you can ease restore.

Error creating Flat File IDOC from XML IDOC

We receive an SAP IDOC using the WCF adapter; afterwards, the disassembler uses that input file to create an XML document. When we later want to re-create an IDOC flat file from that XML document, the assembler does not create blanks for fields that had been empty in the input file - though they are marked as mandatory fields in the schema.

In this example, you can see the missing blanks in front of the "INVOIC" segment in the output file.

Input file - 2 blank segments in front of "INVOIC"
EDI_DC40 3000000000000526082700 3012 INVOIC01

XML - no tags for the 2 blank segments, which seems okay:
<Receive xmlns="http://Microsoft.LobServices.Sap/2007/03/Idoc/3/INVOIC01//40B/Receive">
<idocData>
<EDI_DC40 xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/3/INVOIC01//40B">
<TABNAM xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">EDI_DC40</TABNAM>
<MANDT xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">300</MANDT>
<DOCNUM xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">0000000000526082</DOCNUM>
<DOCREL xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">700</DOCREL>
<STATUS xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">30</STATUS>
<DIRECT xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">1</DIRECT>
<OUTMOD xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">2</OUTMOD>
<IDOCTYP xmlns="http://Microsoft.LobServices.Sap/2007/03/Types/Idoc/Common/">INVOIC01</IDOCTYP>

Output file - missing blank segments in front of the "INVOIC":
EDI_DC40 3000000000000526082700 3012INVOIC01

If anyone knows a solution, please comment. If we find one, we let you know this here.

Misleading error message part2: "Not enough storage is available to complete this operation" after changes settings with the BizTalk Console

Recently (actually since we use our new cluster environment) we receive the this message from time to time after changing some settings with the console:


It finally turns out that restart of the EntSSO service solves the problem although teh eventlog messages lead into another direction (reinstall etc.).

Guest post: Misleading error message with SAP WCF custom adapter

In an IDOC receive scenario using the WCF custom adapter with SAP binding we received the following error messages:

The adapter "WCF-Custom" raised an error message. Details "Microsoft.Adapters.SAP.RFCException: Details: ErrorCode=RFC_EXCEPTION. ErrorGroup=RFC_ERROR_APPLICATION_EXCEPTION. SapErrorMessage=OBJECT_UNKNOWN. AdapterErrorMessage=Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..

The solution is quite simple as it resides in the SAP setting for the RFC connection, transaction SM59. Switching the communication type to Unicode solved the problem.




(This post is brougth to you by Franziska Kirschner, our great new team member)

Sabbatical

I'm taking a sabbatical next year at Estrellas en la calle in Cochabamba, Bolivia, thus I won't post anything here in 2010.
- Christoph

One year later, I'm back. Thanks for continue posting!

Directly Create an EDIFACT Interchange

If you want to create the EDIFACT interchange directly instead of using the built-in functionality of BizTalk, you can create your own schema and send it the EdiSendPipeline. When you use BizTalk's built-in batching functionality, you can see which schema BizTalk uses to prepare the interchange. As far as I see, you can build your custom interchange schema this way:

Import...

• Microsoft.BizTalk.Edi.BaseArtefacts.EdifactServiceSchema.dll (with namespace "http://schemas.microsoft.com/Edi/EdifactServiceSchema")
• the EDI schema, e.g. EFACT_D06A_INVOIC_2.0

Create...

• root node "EdifactInterchangeXml"
• Below root, Create attribut @DelimiterSetSerializedData and set fixed value to 39:-1:-1:43:58:63:42:44:-1 (if this is how UNA should look like).
• Below root, create groups UNA, UNB, UNZ and reference the types from the EdifactServiceSchema
• Below root, create group "TransactionSetGroup" and subgroup "TransactionSet"
• Below "TransactionSet", create a group that references the EDIFACT schema
• Below "TransactionSet", create attribute @TransactionSetActivityId
• Below "TransactionSet", create attribute @DocType and set fixed value to http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006#EFACT_D06A_INVOIC_2.0

The result looks like this:

Weired Error "This is often an indication that other memory is corrupt." executing pipeline component

After migrating our biztalk app to 2009 we received a confusing error message everytime we dropped a file into a file receive location with a pipeline containing a bunch of pipeline components:

"There was a failure executing the receive pipeline:
...
Reason: Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

After along irritating searching we finally debugged the pipeline component and find the root cause:
The call of

message.Context.Read(propertyName, propertyNamespace);

within an pipeline component must not be done with a "Null" value of "propertyNamespace". Obviously there is no internal check for this and the following call within the unsafe code leads into a Null reference call and the error message above.

Problems getting the current assembly in VS2008/BTS2009

BizTalk 2009 seems to copy self-developed BizTalk assemblies into hidden system folders. If you run into the problem that your assemblies are not updated (although you deploy them in Biztalk, add them to the GAC, or save them to the pipeline components folder), it might be that BizTalk still references an old version of the assembly that is in a system folder.

In that case, find and delete the old version of the assembly and try again.