BAMAlerts – The activation state cache could not be updated.

If you have log shipped or detached/attached the BAMAlerts databases, you will have issues starting the notification service on the BizTalk server for BAM alerts. The reason for this, when these databases are restored, the the ‘Cross-Database Ownership Chaining’ option on the BAMAlertsApplication and BAMAlertsNSMain databases is disabled and greyed out. Greyed out because it can only be enabled via a script.

cross_chaining

To enable these options, run the following command (you need to be sys-admin):

–set chaining for a database

alter database BAMAlertsApplication set DB_CHAINING ON

GO

alter database BAMAlertsNSMain set DB_CHAINING ON

GO

—-

Once done, you should be able to start the BAM alerts notification service on the BizTalk Server.

Advertisements

WCF-SQL Adapter and permissions

If you are using a WCF SQL adapter in your solution, it is imperitive to set permissions on the stored procedures you call from within BizTalk. However, if you havent set permissions, the error message can be a little misleading.

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_MyDB” with URL “mssql://MYSRVSQ01//MyDB?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[uspInsertRecords] of type StoredProcedure does not exist

The above message can be quite confusing especially when the stored procedure does exist in your database. The above basically means the BT host account does not have execute permissions on the stored procedure being called. You need to restart the BT host instance after setting permissions.

Now, lets see what happens when I have a working solution (my permissions are set) and I revoke the execute permission on the same stored proc (remember, I havent restarted the BT host after revoking the permission). I now see this error message:

The adapter failed to transmit message going to send port “WcfSendPort_SqlAdapterBinding_TypedProcedures_MyDB” with URL “mssql://MYSRVSQ01//MyDB?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘uspInsertRecords’, database ‘MyDB’, schema ‘dbo’.

This seems to be a much more clear message, isint it? However, if you restart the BT host and try processing your file again, you will see the first error.