Could not load file or assembly error while using TDP makes a come back

Couple of month’s back I had posted about an error while trying to backup the database using TDP. The same issue occurred again but this time on a different server. The error message was same as earlier.

Unlike last time I did not have do any guess work as to why this error started all of the sudden. Couple of days back the Application Support team had tried re-installing SQL Server client components and also applied some updates to Visual Studio components. From the Event Logs it looked like the installation did not go well and they “somehow” got their application working. The Event Log was filled with messages like this.

Event Source:	.NET Runtime Optimization Service
Event Category:	None
Event ID:	1101
Date:		12/16/2010
Time:		8:30:25 AM
User:		N/A
Computer:	ServerName
Description:
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_64)
- Failed to compile: Microsoft.SqlServer.SQLTaskConnectionsWrap,
Version=9.0.242.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002

But the changes made in the SMO components did not go well with the TDP application. To fix this issue I did not have to take the longer route like I did last time. Since the database backups had failed for two consecutive days, it was a High Severity Incident and I could do *anything* to fix it. The option was to either reinstall TDP for SQL Server or Install the SMO components which the TDP application was referring to. Since the former is out of scope for the DBA team, I chose the latter.

All I had to do was to download and install Microsoft SQL Server System CLR Types and Microsoft SQL Server 2008 Management Objects from here. As soon as these were installed Microsoft.SqlServer.Smo Version 10.0.0.0 assembly was available in C:\WINDOWS\ASSEMBLY folder. As expected TDP started working fine and I got the license to spend my Saturday on something other than SQL Server related issues 😀

11 thoughts on “Could not load file or assembly error while using TDP makes a come back

  1. Maha

    Pradeep, does the error in the event viewer on CLR optimization, was thrown at the time of launching TDP or during prior installation.

    Reply
  2. Ravi

    Hi Pradeep,

    I got the same issue while performing backup/restoration activity through my custom built application. I thought of trying to re-refer the dll`s for SQL 2008 as the previous one`s are of 2005. I have done and it resolved my issue and my client felt happy for making it work.

    Apart from this, I have one more issue. Can you help me out in this?

    Issue was: I am restoring an older backup of SQL . As soon as I restore, and try to open the application, it checks for the connection and pops a msg saying, Invalid Database or login credentials. On checking the log, I found Error:18456, State:4, Severity:38.

    I tried Changing permissions, restarting services, etc., on sql as the machine already had built in sql management studio. But, My setup only creates an instance of SQL and generally, it does not install ssms. This time when i started the application, it runs perfectly with the restored database.

    I was not sure what made the application run. I would like to know exactly what need to be done in such scenario.

    Reply
    1. PradeepAdiga Post author

      Hi Ravi,
      State 38 means the user does not have access on the specific database. Did you check if there was any orphaned users?

      Reply
  3. Raja

    dear pradip,

    while creating new data base in sql server 2008 i am getting below error.

    Could not load file or assembly ‘Microsoft.SqlServer.Sqm, Version=10.0.0.0,
    Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies.
    The system cannot find the file specified. (SqlMgmt)

    first i installed but it is not installed properly. so uninstalled this package then reinstalled the same(sql server2008)

    please help me how to resolve this

    Reply
  4. Umar

    Solution:

    Copy the file Microsoft.SqlServer.Sqm.dll located on C:\Program Files\Microsoft SQL Server\100\Shared to folder C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE and C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE. The file size inside these 2 folders should be 0 bytes.

    Reply

Leave a Reply