Thursday, March 17, 2011

Deploying View... ERROR: The BAM deployment failed. Internal error: The operation terminated unsuccessfully.

I wrote a blog post recently on how to overcome the issue of not having the BAMStarSchema database when attempting to deploy the BAM ESB exceptions definition files as part of a Microsoft BizTalk 2010 ESB Toolkit 2.1 installation. This post can be found here: http://www.simonrhart.com/2011/02/bam-star-schema-database-has-not-been.html

After you get past that and you have a BAMStarSchema database you might then see the following error:

Deploying Activity... Done.
Deploying View... ERROR: The BAM deployment failed.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of 'bam_ExcByApplication', Name of 'bam_ExcB
yApplication'.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of 'ExcByApplication_ExcDatetime', Name of 'ExcByApplication_ExcDatetime'
was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Month' attribute
of the 'ExcByApplication_ExcDatetime' dimension from the 'BAMAnalysis' database
was being processed.
Server: The operation has been cancelled.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of 'bam_ExcByApplication', Name of 'bam_ExcB
yApplication'.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of 'ExcByApplication_Application', Name of 'ExcByApplication_Application'
was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Application' att
ribute of the 'ExcByApplication_Application' dimension from the 'BAMAnalysis' da
tabase was being processed.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of 'bam_ExcByApplication', Name of 'bam_ExcB
yApplication'.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of 'ExcByApplication_ExcFaultDescription', Name of 'ExcByApplication_ExcFa
ultDescription' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'FaultDescription
' attribute of the 'ExcByApplication_ExcFaultDescription' dimension from the 'BA
MAnalysis' database was being processed.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of 'bam_ExcByApplication', Name of 'bam_ExcB
yApplication'.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of 'ExcByApplication_ExcDatetime', Name of 'ExcByApplication_ExcDatetime'
was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Year' attribute
of the 'ExcByApplication_ExcDatetime' dimension from the 'BAMAnalysis' database
was being processed.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of 'bam_ExcByApplication', Name of 'bam_ExcB
yApplication'.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of 'ExcByApplication_ExcDatetime', Name of 'ExcByApplication_ExcDatetime'
was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Day' attribute o
f the 'ExcByApplication_ExcDatetime' dimension from the 'BAMAnalysis' database w
as being processed.

This essentially means that the SQL Server user account is not added to the 'db_datareader' role in order to read the BAMStarSchema database.

Simple soultion to fix this is to add the SQL Server service user to that role for the BAMStarSchema database. I this is a development machine, I am running SQL Server under the NT AUTHORITY\NETWORK SERVICE account, so the SQL to fix this could look like the following:

use BAMStarSchema

EXEC sp_addrolemember 'db_datareader', 'NT AUTHORITY\NETWORK SERVICE'

Of course you could do this via SQL Management Studio if you wanted too.

Happy BizTalking!

5 comments:

Anonymous said...

I tried the same on biztalk server 2010 and sql server 2008 R2. It gives me the same error and again

NicoCipo said...

This didn't work for me though changing "SQL Server Analysis Services" logon setting to "Local System Account" did the job.

NicoCipo said...

It didn't work for me either.

Changing "SQL Server Analysis Services" logon setting to "Local System Account" fixed the problem

Kent Weare said...

Same here -> This didn't work for me though changing "SQL Server Analysis Services" logon setting to "Local System Account" did the job

Ratnakar said...

I faced similar error in BTS2013, SQL2012. I managed to resolve this error by making SQL server service account as DB Owner on BamSTarSchema instead of Data Reader.