Parse XML with XmlReader (closing empty elements correctly)

If you want to parse an XML document with the XmlReader, it might help to use this XmlReader example from MSDN. However, there's one thing missing: If you parse an XML with empty elements, such as:
<example1 />
<example2>bla</example2>

the example doesn't set the closing tags write, but like this:
<example1>
 <example2>bla</example2>
</example1>

In order to omit this problem, you have to write the end elements in case that the reader parses an empty element:
case XmlNodeType.Element:
writer.WriteStartElement(reader.Name);
writer.WriteAttributes(reader, true);
if (reader.IsEmptyElement)
 writer.WriteEndElement();
break;

VS/BTS Error "The system cannot find the file specified"

If you receive the error message "The system cannot find the file specified." with the exception code 80070002 while adding a disassembling schema in Visual Studio/BizTalk, it is because VS does not find the assembly from the referenced project with the schema. Just build the referenced project, and then you should be able to add the schema.

Error details: The Messaging Engine encountered an error during the processing of one or more inbound messages.

If you have created a flatfile schema, you might run into the following error:

A message received by adapter "FILE" on receive location "..." with URI "D:\dev\TestFlatfileDis\filedrop\*Copy.txt" is suspended.
Error details: The Messaging Engine encountered an error during the processing of one or more inbound messages.

In this case, it might be that your test file is running into an error while reading the lines:

Reason: Unexpected data found while looking for:
'\r\n'
The current definition being parsed is Root. The stream offset where the error occured is 13. The line number where the error occured is 3. The column where the error occured is 2.

To solve this, check that all the lines go until a certain column. If you can't ensure this, you could define the fields (at the end of the line) with MinOccurs=0, so that it can be omitted.

XML-Error Message "content is not allowed in prolog"

When we created a BizTalk Mapping in VS2008, the compiler returned the error "line 1 colum 1 content is not allowed in prolog". Since the compiler told us that the error was detected in the very beginning (line 1 column 1), we needed a bit of time to see that actually the error was inside the document, where we had filled an element with a value although this shouldn't contain one.
So, if you get the same error message, check which fields you filled and if one of these shouldn't be filled.

32/64-bit Installation of Oracle Adapter

After installing the Oracle Adapter, it might be that you run into errors in run-time, though you solved all the problems so that you could connect to Oracle in VS. ;-) I ran into the following error
System.IO.FileNotFoundException: Die Datei oder Assembly Oracle.DataAccess, Version=2.111.7.20, ...

Actually, the Oracle WCF adapter found the assembly 2.111.7.0, but was searching the Oracle.DataAccess.dll in the BizTalk installation folder:

LOG: Diese Bindung startet im default-Load-Kontext.
LOG: Die Anwendungskonfigurationsdatei wird verwendet: D:\Program Files (x86)\Microsoft BizTalk Server 2009\BTSNTSvc64.exe.Config
LOG: Die Computerkonfigurationsdatei von C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\config\machine.config wird verwendet.
LOG: In der Anwendungskonfigurationsdatei wurde eine Umleitung gefunden. 2.111.7.0 wird nach 2.111.7.20 umgeleitet.
LOG: Verweis nach der Richtlinie: Oracle.DataAccess, Version=2.111.7.20, Culture=neutral, PublicKeyToken=89b483f429c47342
LOG: Download von neuem URL file:///D:/Program Files (x86)/Microsoft BizTalk Server 2009/Oracle.DataAccess.DLL.

The problem was inconsistent image-typing: While VS ran in 32bit, the BizTalk host instance ran in 64bit so that it didn't look in the GAC... Using a 32-bit host instance solved the problem; however, more elegant might be to install a 64-bit version of the Oracle.DataAccess (that however could cause problems with a 32-bit VS...)

Furthermore, deploy the policies "policy.2.x.Oracle.DataAccess" into the 32- and the 64-bit GAC to because both VS and the administration console of the BizTalk are searching for the assembly.

Installing Oracle WCF-Adapter

In order to use the LOB WCF-Adapter for Oracle, you have to install an oracle client. Since BizTalk requests the assembly Oracle.DataAccess of version 2.111.7.0 or above, you have to select a correct Oracle client at least in version 11.2.0.21.

Install the client according to Steef-Jan Wigger's explanation. In %oracle%\product\11.1.0\client_1\odp.net\bin\2.x, you find the Oracle.DataAccess and you can put it to the GAC using OraProvCfg.exe that also redirects all assembly invocations to the actual one. (Otherwise, VS throws an error that it can't find version 2.111.7.0).

Additionally, you can add the sqlnet.ora und tnsnames.ora into %oracle%\product\11.1.0\client_1\Network\Admin. You find samples in the "Sample" folder.

ORA-1017: invalid name/password: When you connect from Visual Studio to the Oracle server, make sure to write the username in capital letters, even if the username is not written in capital letters in Oracle (otherwise you get an ORA-1017: invalid name/password error)!

Error Message: "Fehler beim Laden der Eigenschaftenliste anhand des Namespaces, oder Eigenschaften in der Liste nicht gefunden."

On a German installation, the BizTalk server threw the following error message after parsing a message:

Fehler beim Laden der Eigenschaftenliste anhand des Namespaces, oder Eigenschaften in der Liste nicht gefunden, Überprüfen Sie, ob das Schema korrekt bereitgestellt wurde.

Actually, this error states already the problem quite good, which is that the message contains a promoted property that doesn't exist in the schema property. So, if you get this error message, go through your code and have a close look at all properties that you set and check whether you've spelled their names correctly.

Setting the mail address of Receiver in the Pipeline Component

If you want to send a message with a static or dynamic SMTP send port, you can configure the mail address of the receiver in a pipeline component. Therefore, you have to promote OutboundTransportLocation in the message context of the message:

msgContext.Promote("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "mailto:" + mailAddress);

How to Filter in Orchestrations on Promoted Properties

If you want to do direct-bounding into an orchestration (for example, because you're implementing an ESB where send ports and orchestrations subcribe directly to the message box), you would define filter expressions in the receive shape.

Basically, you can filter on schema properties that are referenced in the BizTalk project of your orchestration. However, to make them visible (and selectable) here, you have to publish them. You can do this in (at least) two ways:

• First, you could promote a field in the incoming message and link the field to a promoted property. This option helps you if you filter on a field that is inside the message.
  
  
• Second - if the data is not part of the message but a header information - you have to set the "property schema base" of the schema property to MessageContextPropertyBase. This way, it gets visible and you can filter on it.

Delete all the Data of a Partition

If you want to delete blocks of data, you usually would like to delete blocks instead of deleting rows. If you at the same time use partitions to store your data, and all the data that you want to delete (e.g. all the data from 2010 and before), you can use the partition commands to realize this. Instead of deleting row by row with the delete-Command
delete * from table where timestamp < '2011-01-01'

you would then truncate the data of the partition(s) that contains this data (from 2010 and before). To do this, you have to create a staging table and then use the SWITCH TO command to move the data to this table. Both steps could be done using the SQL Server wizards. If you have created partitions on a table, you could right-click on the table name, choose "Storage", then "Manage partition":



In the wizard, you choose "Manage partitioned data in a sliding window scenario" and then enable "Switch Out":


The wizard composes now SQL commands to create a staging table with the same columns and indexes as the original table. However, to delete a partition's data you wouldn't need to create all the indexes, it's enough to create the columns. Then you have to invoce the SWITCH TO command to move the data to the staging table:

ALTER TABLE table SWITCH PARTITION 1 TO stagingTable

This is a meta data-operation, no data is moved physically, and thus the data movement doesn't cost a lot. To delete the data, you can then drop the table or truncate it. Both is a block operation and, thus, won't harm the database.

PS: In case, that other tables have foreign keys to this table, you would have to delete this data beforehand (since it might not make sense to have it without its references anyway). Most usually, you would have these referenced tables also partitioned so that you should think about deleting the partitions of referenced tables in the same transaction.

Doing Partition Elimination

Let us assume, that we want to move old data to secondary filegroups. In this case, we would then partition on the timestamp of a row. However, our applications querys a lot on the primary key column "ID", so we prefer to make the ID instead of the timestamp the partition column.

The table therefore partitioned like this:

Partition 1: ID < 99 (before 01-February-2011)
Partition 2: ID 100..199 (February 2011)
Partition 3: ID 200..299 (March 2011)
Partition 4: ID 300..399 (April 2011)
Partition 5: ID > 400 (after 01-May-2011)

At the same time, there are a lot of queries with timely conditions such as
select * from table where timestamp > '2011-04-01' and error is not null

The SQL Server query optimzer would now search the whole table for entries that are younger then the 1st of April 2011 (and have the error column filled). Since the timestamp is not part of the partition column, the query optimizer doesn't know that all data from April 2011 and younger is in partition 4 and 5 and, hence, it can't do partition elimination but has to go through all partitions.

How can we help the query optimizer that he only has to look up the concrete partition.

Until now, I found three answers:

• You can add a condition to the partition to the query:
  select * from table where timestamp > '2011-04-01' and error is not null and $partition.function(ID)>=4
  This way, the SQL Server goes through all rows with an clustered index seek and returns rows from partition 4 and greater. It's fast, but you have to change the query.
  
  
• You can add a condition to the ID to the query:
  You can also limit the query to the boundary value of the partition - adding and ID>=300, and thus, the SQL Server uses a clustered index scan, which is faster than a clustered index seek (see Scans vs. Seeks from Freedman), and furthermore, only reads through the partitions 4 and 5.
  
  
• You could add a non-clustered index on the timestamp:
  Thus, the ID is automatically included because it's in the clustered index. Then, you don't have to change your queries, and the SQL Server can use the index to find the IDs quite fast. This is the most convenient option because you don't change the query; however, the SQL Server needs more steps to find the result.
  

If you have more ideas, please let me know in a comment. :-)

Move a Table to another Filegroup

In case you want to move a table to another filegroup, you would have to change the primary key of the table... and also all the foreign keys to that primary key.

This script allows you to do all this, it drops the constraints of the foreign keys, then it moves the primary key and then it creates again the constraints of the foreign keys.

You can just run the script - as a result you receive the needed SQL statements to move the table (the script itself doesn't make any changes!!).

declare @tablename varchar(50)
set @tablename = --'TableA
declare @newFilegroup varchar(50)
set @newFilegroup = --'FilegroupB'

declare @tableID int
select @tableID = object_id from sys.tables where name=@tablename

select 'BEGIN TRANSACTION'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] DROP CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] DROP CONSTRAINT ' + kc.name + ' WITH (MOVE TO ' + @newFilegroup + ')' from sys.key_constraints kc where kc.parent_object_id = @tableID and kc.type='PK'

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + @tablename + '] ADD CONSTRAINT ' + kc.name + ' PRIMARY KEY(' + c.name + ') ' from sys.key_constraints kc
inner join sys.columns c on kc.parent_object_id = c.object_id
where kc.parent_object_id = @tableID and c.is_identity = 1 and kc.type='PK'

UNION ALL select'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] WITH CHECK ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY([' + cMove.name + ']) REFERENCES [dbo].[' + @tablename + '] ([' + c.name + '])' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.foreign_keys fkMove on fkMove.object_id=fkc.constraint_object_id
inner join sys.columns cMove on cMove.column_id=fkc.parent_column_id
where tMove.name = @tablename and c.is_identity = 1
and fkc.referenced_object_id=@tableID and fkc.parent_object_id=cMove.object_id

UNION ALL select 'GO'

UNION ALL

select 'ALTER TABLE [dbo].[' + t.name + '] CHECK CONSTRAINT [' + fk.name + ']' from sys.tables t
inner join sys.foreign_keys fk on t.object_id = fk.parent_object_id
inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id
inner join sys.tables tMove on fkc.referenced_object_id = tMove.object_id
where tMove.name = @tablename

UNION ALL

select '--COMMIT'

The result of the script looks like this:

Have fun!

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.