Tuesday, October 21, 2014

Delete a SQL Server database schema with all its objects

Recently as part of R&D I had to delete all database schemas in a SQL Server Database. The major pain I foresee on identifying objects associated with it and deleting those in order. I was confident that somebody might have faced the same earlier and the script will be available as its, That's correct. I got a good link in first google itself. Its given below

http://ranjithk.com/2010/01/31/script-to-drop-all-objects-of-a-schema/#comment-428

Really thanks to this guy. But when I tried deleting the schema in my database using this SP, I got an error saying that the schema cannot be dropped as there are some user defined table types inside it. The technique which this guy used is to get the objects of schema is to query the sys.objects and that never gives the User Defined Table Types inside the schema.

SELECT *
FROM sys.objects SO
WHERE SO.schema_id = schema_id(@SchemaName) order by name

This might also be faced by some other people so read some comments but no luck. So had to spend sometime on the query and added the code to delete UDTT too.

--Add DROP TYPE statements into table
INSERT INTO #dropcode
SELECT 'DROP TYPE '+ @SchemaName + '.'+name
FROM   sys.types
WHERE  is_table_type = 1 and schema_id=schema_id(@SchemaName)

File can be downloaded from here
 
Once again thanks to Ranjith the author of original post and hope he wont mind me changing his work and redistributing

Tuesday, October 7, 2014

SQL Server internals - How to see where my data record stored

As I mentioned in many of my previous posts, its very difficult for me to learn something without seeing how its done internally. For example you can see how I explored .Net GC working in one my previous post. This time I am trying to learn how SQL Server stores that data internally.

Where SQL Server stores our tables & records?

As everybody knows, its in the disk only. But which file? Where its located. There are at least 2 files required for each database and we can see the file paths in the properties tab of SQL Server Database or query the details.

How the data records, tables are organized

We could see that the data is stored in normal files with extension .mdf,.ldf and .ndf. Does that mean we can open that in notepad and see it? Is the SQL Server just open the file and writing into it just like how we did in C/C++ labs in college?

Absolutely no. As SQL Server is a production ready software so it cannot do like academic code. It has more levels which optimize the storage techniques for maximum performance. One level is the file groups where we can specify more than one file for a group and associate with partition. Another level is the page. SQL Server considers a page as the atomic unit of storage. The page size is 8KB. It  does the IO operations such as reads / caches at page level only. Even if we need one record from a page, it reads the entire page.

Lets get into how the records are stored. As we know the physical storage order of records in SQL Server database is based on the clustered index and normally the primary key will be clustered index. We cannot have more than one physical storage order for data records. That's is why there is only one clustered index allowed.

How to inspect SQL Server pages

But there is something called non-clustered indexes. If the records cannot be physically stored in more than one order how they help us? Those are different data structures which tells the order of rows in a different way. Before going to "how the non-clustered indexes works" lets get full understanding about how the clustered index works and how to see the data inside page.

I am glad to say that people before me already thought in the same way and done enough hard work to explain the storage with good pictures. So why I need to do the task again? I just read their blogs and see understood how it works. So sharing the same via my blog.

Below is the blog post where I could see the storage is explained with undocumented SQL Server functions called DBCC IND & DBCC PAGE
http://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

References

http://www.practicalsqldba.com/2012/04/sql-server-index-fragmentation.html
https://www.simple-talk.com/sql/database-administration/sql-server-storage-internals-101/

My interest was about index fragmentation. So I did some more research on it and preparing my own post where we can see how fragmentation can be created and solved.

Tuesday, September 23, 2014

Leveraging PerfMon to monitor application / server health

Here I would like to discuss about a business scenario and solution we provided. Sorry to say that this post contains less code. 

I am not saying that this is the ultimate solution. Anybody can criticize so that I can improve the solution. 

Background

We have a generic queuing system in our project to off load long running process. The web layer which includes web site and web services just put the request in the queue so that it can return the response in 2 seconds by allowing the processing machines to process at a later time. The processing is done in a pull fashion instead of push fashion. Each processing machine (Windows 2008 virtual machines) knows what is its processing capacity and how many queue messages are currently running on it. Based on the current available processing capacity it de-queue requests / messages from the queue and process it. There is a proprietary algorithm which we developed for de-queuing messages based on other factors too such as priority of individual message or message type etc...

There is a web portal to monitor this environment. It tells how many messages waiting in the queue, what are the messages in progress, how many failed etc...

I would say its a cloud setup where we can add more processing servers to scale out based on the processing power requirement. But I don't know why nobody else in our project call it cloud :( . May be those poor guys don't know what is cloud? Or they didn't get any chance to experiment with commercial cloud and see how it scales :)

The problem here is to give a solution to visualize this queuing system in correlation with system performance. In other terms, the admins of this environment needs to see what is the processor utilization in a each processing machine when that is processing it's maximum number of messages. Based on that they can decide whether currently configured "maximum number of messages which can be processing concurrently" is really utilizing full system resources. If the concurrent requests configured is high than it's capacity ie over utilized, we can expect more failures (due to timeouts or memory problems ) in the processing servers. Else the system will be in underutilized state.

The solution

Approach 1

As I mentioned earlier, there is a web portal which displays details about queue system. Initial suggestions were to have a capturing mechanism to capture processor and memory snapshots of all the processing servers and display in the web portal using third party graph libraries.

Pros

  • Full control over data collection and display

Cons

  • Need license for third party. We could use open source but the client don't allow that
  • More development effort
Is this the best solution?

Approach 2

If we think about alternatives, we can end up in 3-4 alternatives. The one which got major support is "Using perfmon to show the queue details".

The solution is simple. Microsoft already invested enough in creating beautiful graph based UI for monitoring system performance and its called Perfmon, bundled along with Windows operating system. We can create our own performance counters and get it displayed there as well. We have such a nice tool, then why can't we leverage that to show queue details there and correlate with the system performance such as the processor utilization, memory etc...

Pros

  • No need of third party for visualization.
  • Simple to implement

Cons

  • Tomorrow if Microsoft discontinue perfmon, need to find alternative
  • Needs special permission if we are creating new perfmon category.

Code snippet

Without code, its very difficult for me to stop the post. So just sharing the links towards working with PerformanceCounters using .Net.


Registry permission for creating perfmon
http://bytes.com/topic/net/answers/501945-requested-registry-access-not-allowed-performance-counte

Tuesday, September 16, 2014

Overriding without virtual and override keywords in .Net

This is about a particular scenario where we can achieve overriding without virtual or override keywords in C#. Below is the scenario of an inheritance hierarchy.
Here we can see that both the base and derived class implemented the IDisposable interface. In this case we don't need to mention the keyword overrides but the Dispose() of DerivedDisposable class will be called when we invoke the Dispose() method on the IDisposable type variable, if it contains the DerivedDisposable class object.

            IDisposable id = new DerivedDisposable();
            id.Dispose();

This will invoke the method written inside the DerivedDisposable class.

Calling base class Dispose method

Normally in overriding scenarios, we may call the base class implementation by using base.OverriddenMethodName(). But in this case, base object will not have the Dispose method, if the IDisposable is implemented explicitly. Means the code below won't compile.


    class DerivedDisposable : Disposable,IDisposable
    {
        void IDisposable.Dispose()
        {
            base.Dispose();//This wont compile if the base class / class Derived in this example implements the IDisposable explicitly
        }
    }


Next idea what we can do is to cast the base to IDisposable and call the Dispose method. But that too won't compile.


    class DerivedDisposable : Disposable,IDisposable
    {
        void IDisposable.Dispose()
        {
            //DoWork();
            (base as IDisposable).Dispose();//Won't compile
        }
    }

Implicit interface implementation

Only way to call the base class Dispose method is to make sure the base implements the interface implicitly.Then we can call base.Dispose method().


    class Disposable : IDisposable
    {
        public void Dispose()
        {
            Console.WriteLine("Disposable.Dispose");
        }
    }
    class DerivedDisposable : Disposable,IDisposable
    {
        void IDisposable.Dispose()
        {
            //DoWork();
            base.Dispose();
        }
    }


If we are concerned about having additional public function, another workaround is to have separate protected function in base class (eg:DisposeWorker) which is called from the Dispose() of base class to carry out the task. So from the derived class.Dispose() method we can call that protected function.


    class Disposable : IDisposable
    {
        void IDisposable.Dispose()
        {
            DisposeWorker();
        }
        protected void DisposeWorker()
        {
            Console.WriteLine("Disposable.Dispose");
        }
    }
    class DerivedDisposable : Disposable,IDisposable
    {
        void IDisposable.Dispose()
        {
            //DoWork();
            base.DisposeWorker();
        }
    }

Real scenario - Overriding the ClientBase<TChannel>.Dispose

This is useful when overriding the Dispose method of ClientBase<TChannel> class related to WCF to have our own behavior when disposing. In our project as I mentioned in earlier posts, we are not adding service reference to call WCF service. Instead we create our own proxy class by implementing ClientBase<TChannel>. We have proxy pooling technique built into the system and it needs "return  of proxy to the pool" when Dispose is called on proxy object.


    class Client : ClientBase<IService1>, IService1IDisposable
    {
        void IDisposable.Dispose()
        {
            Console.WriteLine("Disposable.Dispose");
        }
 
        string IService1.GetData(int value)
        {
            throw new NotImplementedException();
        }
    }


I am really not sure whether its overriding or 'feels like' overriding. Anyway I am open to discussions. This is one of the scenario I could see which can only be solved by using implicit interface implementation.We will face this same issue if we implement interfaces explicitly in VB.Net.

Happy coding

Tuesday, September 9, 2014

Why WCF Service cannot be activated when calling from client side?

Last week I came to see one interesting issue related to WPF calling WCF services. One of my colleague asked me to look into his machine as he is not able to call WCF service from WPF application. When I looked into the issue, I could see that the error message at client side is 

The requested service, 'http://<host>/<Service>.svc' could not be activated. See the server's diagnostic trace logs for more information

Diagnose step 1

When ever we see this exception we could understand that the service instance creation got error. If we browse the service url, it internally creates the service object and fail with more error details. So the next step was to browse the WCF service in browser by right click on IIS and browse or give the URL directly, if its known.

That turned out to be more informative

Could not load file or assembly '<assembly name>' or one of its dependencies. An attempt was made to load a program with an incorrect format

If we are not seeing the details we can enable the exception flag.

<serviceDebug includeExceptionDetailInFaults="true"/>

Diagnose step 2

Reset IIS and clear the temporary Asp.Net files. Sometime the temporary Asp.net files will be holding a wring dll.

Location of temporary ASP.Net files for 64 bit .Net 4.0 applications is - %Windir%\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files

Location of temporary ASP.Net files for 32 bit .Net 4.0 applications is - %Windir%\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files

Tried this step but no luck.

Diagnose step 3

This leads to the situation where one assembly file present in the bin folder is not getting loaded due to some exception. There are chances that it depends on another dll and that is causing issue. Also chances are that it might be loading the dll from some other location such as GAC. 

So better idea is to see the assembly binding fusion log using the fuslogvw tool .Tried that and got more detail. Basically enabled the registry key, then we can see the fusion log in the browser itself. It says

===
LOG: This bind starts in default load context.
LOG: Using application configuration file: D:\TFS\TestDotNet\WcfService1\web.config
LOG: Using host configuration file: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet.config
LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config.
LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).
LOG: Attempting download of new URL file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/Temporary ASP.NET Files/wcfservice1/63540997/f07fffe5/ClassLibrary20.DLL.
LOG: Attempting download of new URL file:///C:/Windows/Microsoft.NET/Framework64/v4.0.30319/Temporary ASP.NET Files/wcfservice1/63540997/f07fffe5/ClassLibrary20/ClassLibrary20.DLL.
LOG: Attempting download of new URL file:///D:/TFS/TestDotNet/WcfService1/bin/ClassLibrary20.DLL.
ERR: Failed to complete setup of assembly (hr = 0x8007000b). Probing terminated

This means the dll is loaded from proper location. So need to go next step.

Diagnose step 4

Need to have closer look at the exceptions and their call stack to understand how far its reaching and where its failing. The exception call stack is given below which was present in the browser when service is browsed.


[BadImageFormatException: Could not load file or assembly '<assembly name>' or one of its dependencies. An attempt was made to load a program with an incorrect format.]
   System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0
   System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +210
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) +242
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +17
   System.Reflection.Assembly.Load(String assemblyString) +35
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +122

[ConfigurationErrorsException: Could not load file or assembly '<assembly name>' or one of its dependencies. An attempt was made to load a program with an incorrect format.]
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +12761078
   System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +503
   System.Web.Configuration.AssemblyInfo.get_AssemblyInternal() +142
   System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +334
   System.Web.Compilation.BuildManager.CallPreStartInitMethods(String preStartInitListPath) +203
   System.Web.Compilation.BuildManager.ExecutePreAppStart() +152
   System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +1151

[HttpException (0x80004005): Could not load file or assembly '<assembly name>' or one of its dependencies. An attempt was made to load a program with an incorrect format.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +12881540
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +159
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +12722601


There were 3 exception one after another. Configuration exception and HttpExceptions are the outer ones and the real problem is stated in the very inner exception which is nothing but BadImageException.

BadImageException usually happens when we try to load 32 bit assembly in 64-bit applications or when the assembly is corrupted

Diagnose step 5

We can check whether the assembly is corrupted by opening it in reflector or any other decompilation software. If reflector is able to load the dll, there is no issue. In this case, I was able to open the file in reflector.

Diagnose step 6

Next we need to make sure all the assemblies are marked as 64 bit / AnyCPU when compiled. The ASP.Net hosting mechanism loads all the dlls when its started. Based on the application pool setting "Enable 32-Bit Applications" flag, it decide the processor architecture of web application. If there is any dll which is not matching with bit size, it will throw this bad image exception. We could see that the assembly which was causing, is built using x86 mode where the app pool is exclusive for 64bit applications.

Recompiled using 'AnyCPU' setting. Reset IIS and cleared the Temporary ASP.Net Files. Everything worked.

Tuesday, September 2, 2014

Hosting WCF service in IIS and System.Web.WebPages.Deployment

As per MSDN the System.Web.WebPages,Deployment namespace contains "the classes that are used to confirm the deployment of web applications". It make sense for web applications which contains web pages created using .aspx or Razor. But what is the relation between this System.Web.WebPages.Deployment.dll and WCF service application which hosted inside IIS which is exposed via netNamedPipes ?

As we have seem in my previous post about SSMS and System.Transactions.dll, I got a machine crash and the machine started behaving in its's own way which resulted in problems. Now when I host WCF services inside IIS there are problems. The services, I was able to browse before the crash stopped working with below error message.

Could not load file or assembly 'System.Web.WebPages.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The module was expected to contain an assembly manifest

The call stack looks as follows

[BadImageFormatException: Could not load file or assembly 'System.Web.WebPages.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The module was expected to contain an assembly manifest.]
   System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +0
   System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +34
   System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) +152
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) +77
   System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +16
   System.Reflection.Assembly.Load(String assemblyString) +28
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +38

[ConfigurationErrorsException: Could not load file or assembly 'System.Web.WebPages.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The module was expected to contain an assembly manifest.]
   System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +752
   System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +57
   System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +170
   System.Web.Compilation.BuildManager.GetPreStartInitMethodsFromReferencedAssemblies() +91
   System.Web.Compilation.BuildManager.CallPreStartInitMethods(String preStartInitListPath) +258
   System.Web.Compilation.BuildManager.ExecutePreAppStart() +135
   System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters, PolicyLevel policyLevel, Exception appDomainCreationException) +516

[HttpException (0x80004005): Could not load file or assembly 'System.Web.WebPages.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The module was expected to contain an assembly manifest.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9873912
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +254


One possibility for BadImageFormatException is the mismatch of runtime. ie if we try to load x86 assembly in x64 application or other way we may get this exception. I tried enabling 32 bit support in the application pool by setting the "Enable 32-Bit Applications" flag in IIS. 

It did't help.

Then looked at all the config files possible such as aspnet.config and machine.config. Nowhere I was able to find any entry to the problematic dll. Then I decided to find out the assembly binding information which is as follows

=== Pre-bind state information ===
LOG: User = NT AUTHORITY\NETWORK SERVICE
LOG: DisplayName = System.Web.WebPages.Deployment, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
 (Fully-specified)
LOG: Appbase = file:///<drive>:/TFS/TestDotNet/WcfService1/
LOG: Initial PrivatePath = <drive>:\TFS\TestDotNet\WcfService1\bin
Calling assembly : (Unknown).
===


Here its clear that the calling assembly is unknown which I assume any framework dll. May be it was earlier getting loaded from GAC and after the crash GAC registration might be broken. So I figured out the location of he dll and registered into GAC.

Location of System.Web.WebPages.Deployment.dll - <Install drive>:\Program Files (x86)\Microsoft ASP.NET\ASP.NET Web Pages\v2.0\Assemblies

But the GACUtil failed with a message as follows.

The module 'System.Web.WebPages.Deployment.dll' was expected to contain an assembly manifest.

Then I tried open the dll in reflector and could see that, it was not able to open as its no more a .net dll and it displayed more detailed error message


File is not a portable executable. DOS header does not contain 'MZ' signature.


This leads to a conclusion that the System.Web.Webpages.Deployment.dll is corrupt. So next option is repair or re-install. So went to appwiz.cpl and re-installed "Microsoft ASP,Net Webpages 2".

It worked!!!

But why WCF hosted in IIS needs System.Web.WebPages.Deployment.dll which is for web page related :-(

Tuesday, August 26, 2014

SSMS 2008 and System.Transactions.dll 2.0.0.0

Recently my machine got some hang and I had to restart my machine by "pressing down power button for long time and start again". As usual the chkdsk popup came and run complete disk scan. After the check disk scan, I was able to login and reached till desktop. But from that time onwards I started facing some strange issues. One of issue is mentioned below.

I opened SSMS 2008 R2 (SQL Server Management Studio) and tried connecting to an SQL Server instance.It resulted in the below error message

"Could not load file or assembly 'System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The module was expected to contain an assembly manifest. (System.Data)"

What is the relation between SSMS and this dll. I know this dll is used for .Net to achieve transactions in our applications. May be this is used by SSMS to execute our queries. But why its needed when we are connecting to database? No idea. 

Attempt 1 - Re-register System.Transactions.dll into GAC

Googled and found couple of links and everywhere it is telling that the dll is not registered in the GAC. Normally this dll will be registered in to the GAC but some operations like installation of other software might have unregistered it. Below are the main paths we can see this particular dll. But unfortunately in my case I don't have that dll in place or the dll size is 0 KB.
  • C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Transactions.dll - File size 0 KB
  • C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin - I dont have VS 2005 Installed
  • C:\Windows\Microsoft.NET\Framework64\v2.0.50727 - The file is present. But SSMS.exe is a 32bit process.
  • C:\Program Files (x86)\Microsoft Visual Studio 9.0\SDK\v3.5\Bin - This folder is empty
  • C:\Program Files (x86)\Microsoft Visual Studio 10.0\SDK\v3.5\Bin - Folder empty
  • C:\Program Files (x86)\Microsoft Visual Studio 11.0\SDK\v3.5\Bin - Folder empty

Attempt 2 - Reinstall .Net framework from control panel

This turns out that my .Net 2.0 installation is corrupted. What is the easiest way? Go to control panel -> Programs and features -> Turn windows features turn on or off and uninstall and install the .Net framework. But in my machine I was not even able to get the "windows features on off" grid. Even I don't think, I can do it as .Net 2.0 is part of Windows 7 OS.

Attempt 3 - Repair .Net using tool

Downloaded the Microsoft.Net Framework Repair Tool and run it. I tried the same and no chance. Still the file size is 0KB.

Attempt 4 - Install .Net framework using msi

Then downloaded the.Net 2.0 installer and tried installing it. But it says "The product is already installed as part of the operating system"

Attempt 5 - Uninstall from installer cache

One link from Microsoft KB says we need to goto the installer folder located at <OS Drive>:\Windows\Installer and find the msi package by its comment "Microsoft .NET Framework 2.0 RTL x86 enu" and right click to repair. But I was not able to see the entry with the .Net 2.0 comment.

Attempt 6 - Reinstall using command

Anther link says we can directly run the installer using the msiexe command as follows

msiexec /fpecmsu {C09FB3CD-3D0C-3F2D-899A-6A1D67F2073F} REINSTALL=ALL /l*v %temp%\netfx20sp2_repair_log.txt /qn

After waiting for some time I could see the netfx20sp2_repair_log.txt in the temp folder with and entry as below

MSI (s) (7C:30) [01:16:50:534]: Running installation inside multi-package transaction {C09FB3CD-3D0C-3F2D-899A-6A1D67F2073F}

But still the issue is not resolved.

Attempt 7 - using sfc.exe

Since the .Net 2.0 is part of Windows OS, there is another way to verify the installation. Its using sfc command. Full command is as follows which we can give in command window which is running in admin mode.

sfc.exe /scannow

Result

"Windows Resource Protection found corrupt files but was unable to fix some of th
em.
Details are included in the CBS.Log windir\Logs\CBS\CBS.log. ...."

There are issues which were not resolved and simply pointing to a log file which is around 3 MB text.

What is there in the log

Just searched through the CBS.log for System.Transaction.dll and found the entry very easily.

2014-08-26 01:33:57, Info                  CSI    00000389 Hashes for file member \SystemRoot\WinSxS\x86_system.transactions_b77a5c561934e089_6.1.7601.18410_none_e7e76cdf2530f04d\System.Transactions.dll do not match actual file [l:46{23}]"System.Transactions.dll" :
  Found: {l:20 b:2jmj7l5rSw0yVb/vlWAYkK/YBwk=} Expected: {l:20 b:hs4esk9VeIeBIBB9tDCOqo2DioA=}
  Found: {l:32 b:47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=} Expected: {l:32 b:hZSyvIr53ZRpAoP4vZD8O+tE48T6dPGXmr6Eo8MKkWU=}
2014-08-26 01:33:57, Info                  CSI    0000038a [SR] Cannot repair member file [l:46{23}]"System.Transactions.dll" of System.Transactions, Version = 6.1.7601.18410, pA = PROCESSOR_ARCHITECTURE_INTEL (0), Culture neutral, VersionScope neutral, PublicKeyToken = {l:8 b:b77a5c561934e089}, Type neutral, TypeName neutral, PublicKey neutral in the store, hash mismatch
2014-08-26 01:33:58, Info                  CSI    0000038b Hashes for file member \SystemRoot\WinSxS\x86_system.transactions_b77a5c561934e089_6.1.7601.18410_none_e7e76cdf2530f04d\System.Transactions.dll do not match actual file [l:46{23}]"System.Transactions.dll" :
  Found: {l:20 b:2jmj7l5rSw0yVb/vlWAYkK/YBwk=} Expected: {l:20 b:hs4esk9VeIeBIBB9tDCOqo2DioA=}
  Found: {l:32 b:47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=} Expected: {l:32 b:hZSyvIr53ZRpAoP4vZD8O+tE48T6dPGXmr6Eo8MKkWU=}
2014-08-26 01:33:58, Info                  CSI    0000038c [SR] Cannot repair member file [l:46{23}]"System.Transactions.dll" of System.Transactions, Version = 6.1.7601.18410, pA = PROCESSOR_ARCHITECTURE_INTEL (0), Culture neutral, VersionScope neutral, PublicKeyToken = {l:8 b:b77a5c561934e089}, Type neutral, TypeName neutral, PublicKey neutral in the store, hash mismatch
2014-08-26 01:33:58, Info                  CSI    0000038d [SR] This component was referenced by [l:156{78}]"Package_3_for_KB2931356~31bf3856ad364e35~amd64~~6.1.1.0.2931356-78_neutral_GDR"
2014-08-26 01:33:58, Info                  CSI    0000038e Hashes for file member \??\C:\windows\Microsoft.NET\Framework\v2.0.50727\System.Transactions.dll do not match actual file [l:46{23}]"System.Transactions.dll" :
  Found: {l:20 b:2jmj7l5rSw0yVb/vlWAYkK/YBwk=} Expected: {l:20 b:hs4esk9VeIeBIBB9tDCOqo2DioA=}
  Found: {l:32 b:47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=} Expected: {l:32 b:hZSyvIr53ZRpAoP4vZD8O+tE48T6dPGXmr6Eo8MKkWU=}
2014-08-26 01:33:58, Info                  CSI    0000038f Hashes for file member \SystemRoot\WinSxS\x86_system.transactions_b77a5c561934e089_6.1.7601.18410_none_e7e76cdf2530f04d\System.Transactions.dll do not match actual file [l:46{23}]"System.Transactions.dll" :
  Found: {l:20 b:2jmj7l5rSw0yVb/vlWAYkK/YBwk=} Expected: {l:20 b:hs4esk9VeIeBIBB9tDCOqo2DioA=}
  Found: {l:32 b:47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=} Expected: {l:32 b:hZSyvIr53ZRpAoP4vZD8O+tE48T6dPGXmr6Eo8MKkWU=}
2014-08-26 01:33:58, Info                  CSI    00000390 [SR] Could not reproject corrupted file [ml:520{260},l:98{49}]"\??\C:\windows\Microsoft.NET\Framework\v2.0.50727"\[l:46{23}]"System.Transactions.dll"; source file in store is also corrupted
2014-08-26 01:33:59, Info                  CSI    00000391 Repair results created:

It says source file store is also corrupted. Even I checked sfc.exe documentation .It says the same cannot be repaired.

Attempt 8 - Remove .Net 2.0 using third party tool

There is something called .Net framework cleanup tool which can be used to remove .Net framework version. But in the documentation it says, it cannot remove .Net 2.0 from Windows 7.

Attempt 9 - Use SQL Server 2012 and connect to SQL 2008 R2 instance

Now its time to step back and think. My actual problem is to connect to SQL Server instance. Why don't I try using SQL 2012 SSMS. Installed SQL Server 2012 and tried to connect to SQL 2008 instance. The connection succeeded. I was able to expand the databases node. But when I try to do any operation such as  opening the query window or restore database backup I gets new error as follows.

Could not load file or assembly 'Microsoft.NetEnterpriseServers.ExceptionMessageBox, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was expected to contain an assembly manifest. (Microsoft.SqlServer.Management.RelationalEngineTasks)

Entering into more issues

What's left

  1. Copy the dll from another machine which is having same .Net version and paste into my folder and register into GAC
  2. Reinstall .Net framework 2.0 manually by following the below link
  3. Format and re-install everything / re-image my machine as everyone in my company uses same OS image.
Planning to go with option 3 is option 1 doesn't work.

Update 1

I tried option 1 and it started working. Followed the steps mentioned in a link to replace the System.Transactions.dll as its protected by Trusted Installer