Поделиться через

Cannot reopen an Analysis Services Tabular Project the second time– error database already exists in the detached state

  • Статья

Using SQL Server 2012 Analysis Services running in Tabular mode, we get this error every time we open an existing Tabular Project solution. Even creating a new Analysis Services Tabular Project, closing it, and opening a second time again causes the same error.

An error occurred while opening the model. Click Details for more information.

The operation cannot be executed since the database with the name of <name>, ID of <ID> already exists in the detached state in folder '\\?\D:\MSAS11.InstanceName\OLAP\Data\Datababase_c7f3679b-1d5f-44e8-b6d0-25d2de10b7fc.0.db'. Either attach the database or delete the folder and retry the operation.

To resolve this error, view the properties of Analysis Services, and ensure that your default Data Directory folder is browsable.

image

On the Analysis Server Properties page, General tab, check the box to Show Advanced (All) Properties.

image

 

Note your DataDir – this the folder where the tabular databases files live.

My default directory is C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Data

 

Now note your AllowedBrowsingFolders. This is a pipe | character delimited list of all the folders that the service is allowed to browse and show files in. Without this, Analysis Services will just not enumerate the directories.

C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Backup\|C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Log\|C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Data\

 

The problem we found was that the DataDir value was not in the AllowedBrowsingFolders list, so we added it to the string - |C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data\

This could easily happen if the Default DataDir was changed, but the AllowedBrowsingFolders was not updated to reflect the change.

Internals

To figure this out I ran a profiler trace to watch the Command Begin and Command End events in particular.

 

I observed this pattern when it works:

1. Observe the temporary database creation

When I make a new Tabular project project in SSDT, the Analysis Services service creates a temporary tabular database, which consists of a folder and an XML pointer file to the folder.  This database is not visible in Management Studio, but is visible in the storage underneath the data folder itself.

Under the DataDir folder, ie.  C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Data, I see two indicators of the new tabular project being staged.

  • /TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082.0.db/ (folder)
  • /TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082.1.db.xml (file to point to the folder)

image

image

 

2. Observe the detach XMLA

When I close the Visual Studio project, it issues a detach command to remove the temporary database. The files in the folder should remain.

<Detach xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082</DatabaseID> </Object> </Detach>

At this time of the detach, the Analysis Services deletes the .xml file pointer to the database, but the database .db folder and its contents remains in tact.

  • /TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082.1.db.xml (gets deleted)
  • /TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082.0.db/ (folder remains)

 

3. Observe the attach XMLA

When I open the same project again from the recently used list, it checks for existing folders, and attaches the same existing database using XMLA for attach.

A. MDX query check for the existing database by running an internal stored procedure

 SystemGetSubdirs 'C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data'

image

B.  XMLA is issued to Attach the existing database from the listing of folder detected

<Attach xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <Folder>C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data\TabularProject6_jason_9f18f33a-4391-4ed7-9c39-95b18307d082.0.db</Folder> <ReadWriteMode xmlns="https://schemas.microsoft.com/analysisservices/2008/engine/100">ReadWrite</ReadWriteMode> </Attach>

C. Runs query to see the tabular data.

SELECT TOP 1000 [Model].[$DimEmployee].[EmployeeKey], [Model].[$DimEmployee].[ParentEmployeeKey], [Model].[$DimEmployee].[EmployeeNationalIDAlternateKey], [Model].[$DimEmployee].[ParentEmployeeNationalIDAlternateKey], [Model].[$DimEmployee].[SalesTerritoryKey], [Model].[$DimEmployee].[FirstName], [Model].[$DimEmployee].[LastName], [Model].[$DimEmployee].[MiddleName], [Model].[$DimEmployee].[NameStyle], [Model].[$DimEmployee].[Title], [Model].[$DimEmployee].[HireDate], [Model].[$DimEmployee].[BirthDate], [Model].[$DimEmployee].[LoginID], [Model].[$DimEmployee].[EmailAddress], [Model].[$DimEmployee].[Phone], [Model].[$DimEmployee].[MaritalStatus], [Model].[$DimEmployee].[EmergencyContactName], [Model].[$DimEmployee].[EmergencyContactPhone], [Model].[$DimEmployee].[SalariedFlag], [Model].[$DimEmployee].[Gender], [Model].[$DimEmployee].[PayFrequency], [Model].[$DimEmployee].[BaseRate], [Model].[$DimEmployee].[VacationHours], [Model].[$DimEmployee].[SickLeaveHours], [Model].[$DimEmployee].[CurrentFlag], [Model].[$DimEmployee].[SalesPersonFlag], [Model].[$DimEmployee].[DepartmentName], [Model].[$DimEmployee].[StartDate], [Model].[$DimEmployee].[EndDate], [Model].[$DimEmployee].[Status], [Model].[$DimEmployee].[EmployeePhoto] FROM [Model].[$DimEmployee]

 

4. Contrast to the observations on the failing broken instance

On the broken computer, when we get the error, I notice the same query is run, but instead of ATTACH XMLA statement, instead a XMLA ALTER/Create  statement is used to generate a new database, and we see the error message.

The real cause is that when the list of browsable folders cannot see the default data folder, then this query runs without any error, but returns no results

Made a new MDX connection to the tabular Analysis Service instance and ran it:

 SystemGetSubdirs 'C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data'

<no result!>

 

Therefore on the broken instance, Analysis Services perceives there is no data folder matching the tabular project name. It tries to use the databasename_username_GUID to create a fresh database to stage the tabular model. The .db folder already exists and was detached earlier, so this causes a name collision and the error. The error is not lying, but the confusing thing is that we don’t see it create/detach/attach sequence because its hidden behind the scenes in the magic implementation.

The operation cannot be executed since the database with the name of <name>, ID of <ID> already exists in the detached state in folder '\\?\C:\MSAS11.InstanceName\OLAP\Data\Datababase_c7f3679b-1d5f-44e8-b6d0-25d2de10b7fc.0.db'. Either attach the database or delete the folder and retry the operation.

Comments

  • Anonymous
    February 22, 2017
    very helpful, thanks