In SQL Server 2005, we can write stored procs, UDFs, aggregates and types in .NET. In some scenarios, it can be interesting to call web services directly from within the database. Since we can call web services easily from within .NET, and we can call .NET from within SQL Server 2005, it seems easy to create a stored proc or scalar UDF to call a web service. However, people who tried this (or attended one of my SQL 2005 courses) know that this was not the case with the CTPs: The proxy class generated by Visual Studio tries to build classes at runtime, and this type of code is not accepted by the SQL Server .NET host. However, there were some rumors on the internet that MS would solve this problem by generating 'well behaving' proxy classes from within the templates for building .NET stored procs, UDFs etc.
Since we have the final release now, it is time to check how things go. And it turns out that the proxy classes still have the same behaviour: building a proxy class by just adding a Web reference from within Visual Studio, gives you an assembly which can be loaded into Sql Server (deployed from within Visual Studio - but make sure you have permissions for creating external reference assemblies, and your database is trustworthy), but when you try to run them, you get this error:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'WSFunction':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information.
So, in short, if you want to call a web service from within a .NET stored proc or UDF, you still have to use the approach as described in
this article.
Nico