.Net not fully supported in SQL 2005!

All,

I thought you might be interested in the following. I'm working on a major project using SQL 2005 using the CLR hosted environment.

The issue in a nutshell is that SQL Server 2005 only supports a subset of the .Net Framework (one of the biggest selling points of SQL 2005) . The development community are not being told of these short falls in SQL and are inadvertently using features of the system which still contain bugs – which then MS will refuse to support. Actually, it's worse. They won;t support your SQL server until the offending c# code accessing the untested classes are removed. MS as it stands don't publicly tell you which classes are not suppotred, only the assemblies that contain the classes. In these assemblies there are supported and unsupported classes - so as a developer you have no way of telling if your SQL server is supported if you use CLR - until it goes wrong!


This is how MS sell SQL Server…

http://www.microsoft.com/sql/prodinf...erver2005.mspx

Note: You cannot take full advantage of the MS .Net Framework Class – only a subset is supported!.

CLR/.NET Framework Integration
With the release of SQL Server 2005, database programmers can now take full advantage of the Microsoft .NET Framework class library and modern programming languages to implement functionality within the server. Using CLR integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft Visual Basic .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. Additionally, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can easily use on the server side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects—aggregates and user-defined types—are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.
This integration between SQL Server and the CLR provides several major benefits:
• Enhanced programming model. Programming languages that are compatible with the .NET Framework are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously not available to SQL Server developers.
• Enhanced safety and security. Managed code runs in a CLR environment, hosted by the database engine. This allows .NET Framework database objects to be safer and more secure than the extended stored procedures available in earlier versions of SQL Server.
• User-defined types and aggregates. Two new database objects that expand the storage and querying capabilities of SQL Server are enabled by hosting the CLR.
• Common development environment. Database development is integrated into the Microsoft Visual Studio 2005 development environment. You can use the same tools for developing and debugging database objects and scripts that you use to write middle-tier or client-tier .NET Framework components and services.
• Performance and scalability. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.
By using languages such as Visual Basic .NET and C#, you can capitalize on CLR integration to write code that has more complex logic and is more suited for computation tasks. Additionally, Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can easily organize related code into classes and namespaces, which means that you can more easily organize and maintain your code investments when you are working with large amounts of code. The ability to logically and physically organize code into assemblies and namespaces is a huge benefit that allows you to better find and relate different pieces of code in a large database implementation.
Managed code is more efficient than Transact-SQL at processing numbers and managing complicated execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with the functionality that is available in the .NET Framework class library, you have full access to thousands of prebuilt classes and routines that you can access easily from any stored procedure, trigger, or user-defined function. Everything from improved string-handling functions, math functions, date operations, access to system resources, advanced encryption algorithms, file access, image processing, and XML data manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.
One of the major benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks through a process known as verification to ensure that the code is safe to run. For example, the code is checked to ensure that memory is not read that has not been written to.


A SQL engineer admitted to me that MS never got the whole Framework working, so technical support provided the following support notes on the 27th July.
http://support.microsoft.com/?id=922672

The above link outlines the .dll (assemblies) that are supported. What it does not tell you is the classes are not supported – these are needed to develop code, eg:

using System; -> Supported
using System.Data; -> Supported
using System.Data.SqlClient; -> Not supported
using System.Data.SqlTypes; -> Not supported
using Microsoft.SqlServer.Server; -> Not supported
using System.Diagnostics; -> Not supported
using System.IO; -> Not supported
using System.Transactions; -> Not supported
using System.Collections; -> Supported


Another issue is that MS are educating the developer community using MSDN articles that use unsupported functions:

http://msdn2.microsoft.com/en-us/library/ms254508.aspx


Has anyone else come across this issue?