Thursday, September 12, 2013

Could not load file or assembly 'Microsoft.AnalysisServices.SharePoint.Integration

A lot of articles have been written about this particular issue. It seems like a really big issue with all the errors popping up everywhere, but in all honesty, it's bark is worse than its bite. I'll update this post later with a few online resources which I've found very useful while trying to solve this error.

The error usually goes something like this...

Error: Could not load file or assembly 'Microsoft.AnalysisServices.SharePoint.Integration, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Option 1

Open up command prompt with elevated permissions and run this command

RD /S C:\Windows\Assembly\GAC_MSIL\Microsoft.AnalysisServices.SharePoint.Integration

In most cases you will get an Access Denied error. That is because w3wp.exe is holding onto the file. You will need to stop IIS and run the command again.

Option 2


1. After the installer fails disable the assembly cache viewer.
2. Go into the assembly and find the file Microsoft.AnalysisServices.SharePoint.Integration.dll
3. Copy this dll to the bootstrap location: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64
4. Remove the Powerpivot instance from the Program and Features .. SQL Server 2008 R2 Uninstall
5. Enable the assembly cache viewer and copy the Microsoft.AnalysisServices.SharePoint.Integration.dll into the GAC.
6. Run the installation process again to install the PowerPivot instance.

OLEDB vs ADO.Net error in SSAS Tabular Models

OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

So I come into the office pretty early, with a spring in my step, definitely amped up for the day ahead. I mean, who wouldn't be, especially if you are working with Tabular Models is SSAS. That aside...because ofttimes it can be short lived as was my experienced today. I try to load my table into the designer so I can start performing some DAX magic. Let's agree, DAX is magic!!! Comprendo? Good.

When I go into table properties and click "OK". I get an OLEDB or ODBC error.

The error reads as such:
OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
A connection could not be made to the data source with the DataSourceID of 'c11c7644-9785-4e7f-89bb-e227848edd62', Name of '<Data Source Name>'.

An error occurred while processing the partition 'PARTITION_NAME_b799baff-060f-4e30-8dcc-bd8aff962b25' in table 'PARTITION_NAME_b799baff-060f-4e30-8dcc-bd8aff962b25'.
The current operation was cancelled because another operation in the transaction failed.




This error is caused by ADO.Net, so I had to change my provider from SQL Server Native Client to OLEDB Provider for SQL Server.

I don't know why this happens, but it does. If at first the change doesn't seem to work. Just close and reopen the project because connections are cached. You already know how to change providers, but here goes anyway,...




Wednesday, June 5, 2013

SQL Server: Attach MDF without LDF

Yes, it happens. Sometimes, not very often I hope, you find yourself without a corrupt LDF file or for some odd reason you were handed an MDF file and you need to make this database file available  for use. Desperate times call for desperate measures right? I hope you never have to deal with this in a production environment, because being the good DBA that you are, you have implemented a sound Disaster Recovery plan in your SQL Server environment. Without further ado, here is how you'd attach an MDF without an LDF. 

1. Create a new database with the same name and same MDF and LDF files 

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.