Thursday, February 22, 2007

BAM Deployment Error



I received following error while deploying BAM project in Biztalk 2006. Error read “BAM Deployment failed.
Encountered error while executing command on SQL server “server name”.
Incorrect Syntax near ‘@@Error’.
Must declare scalar variable “@@UniqueID”.”

Screenshot of the error is below:



I opened the tracer log at SQL server level. I extracted the stored procedure that was giving me an error and ran the stored procedure directly on the server. I got following error:

Msg 102, Level 15, State 1, Procedure bam_Test1_UpsertInstance, Line 15
Incorrect syntax near '@@Error'.
Msg 137, Level 15, State 2, Procedure bam_Test1_UpsertInstance, Line 68
Must declare the scalar variable "@@UniqueID".
Msg 137, Level 15, State 2, Procedure bam_Test1_UpsertInstance, Line 111
Must declare the scalar variable "@@UniqueID".
Msg 137, Level 15, State 2, Procedure bam_Test1_UpsertInstance, Line 140
Must declare the scalar variable "@@UniqueID".

I changed the two variables representing the name of BAM activity “Error” and “Unique ID” to “Exception” and “TransactionID” respectively. Ran the command line for deployment and Bingo it worked!!

It seems that Biztalk uses these 2 variables (Error and UniqueID) internally and will not accept them as user defined variables. There may well be more than two of these variables that will cause similar errors. Well, if we get similar error again then it is very likely that we stepped on some more of these variables.

MOM Architecture

MOM is another great tool to monitor distributed Biztalk environment. It can easily configure alerts to trigger some activity. This activity can be like sending an email to a person or a group. It can be running some batch file or a script. This event/error based triggering is very helpful while administering a widespread distributive environment. It enables people to take necessary action at right times.

The basic components that constitute MOM are captured below.