Steps to Create SQL CLR component

1. Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2010, create a new Database Project:

2. First, you must change the version of the.NET Framework used to 3.5 at the top of this screen or your project will not deploy. Then change the project name to MyExtendedDBFunctions

Add a reference to the Your (AdventureWorks) database by clicking Add New Reference.

    Fill out the form and click OK.

3. Select AdventureWorks and click OK. Then right click the solution in Solution Explorer and select New -> Stored Procedure

Change the name to TestUserDefinedFunction.

Change the code to look like this:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class TestUserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

   public static SqlString TestUDF(int number)

   {

    BasicHttpBinding basicHttpBinding = new BasicHttpBinding();

   EndpointAddress endpoint2 = new EndpointAddress(new Uri("http://localhost:51610/TestService2.svc"));

    TestService2Client client2 = new TestService2Client(basicHttpBinding, endpoint2);

       return new SqlString(client2.GetData(internalID));    

   }

};

Create a WCF service with the default .net Framework 4.0 and expose that service and use the existing GetData().

4. Before we build our assembly there are a couple of project settings that need modification.

Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

5. Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence UNSAFE.

6. Server and Database Settings

USE custDB

EXEC sp_configure 'clr enabled', '1';

RECONFIGURE;

ALTER DATABASE custdb SET TRUSTWORTHY ON;

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY  [System.SMdiagnostics]

FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'

WITH PERMISSION_SET = UNSAFE

GO

-- Adding required assemblieds for the WCF service.

CREATE ASSEMBLY  [System.IdentityModel]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.IdentityModel.Selectors]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'     

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.Messaging]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

--Attempted to perform an operation that was forbidden by the CLR host.

CREATE ASSEMBLY  [Microsoft.Transactions.Bridge]

FROM  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

7. Click Build -> Deploy Solution and you should receive the following output:

If deploy failed, please follow below steps to add your CLR component and procedure/function to the sql server

Register our SQL CLR component

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyExtendedDBFunctions')

BEGIN

     DROP ASSEMBLY [MyExtendedDBFunctions]

ALTER ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE ;

END

ELSE

BEGIN

CREATE ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE;

END

Once our assemblies are created we have to create Procedure/Function in the below way.

CREATE PROCEDURE WeatherSP

    @zipCode nvarchar(10)

AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)

AS EXTERNAL NAME MyExtendedDBFunctions.TestUserDefinedFunctions.TestUDF;

Below are the possible errors and solutions 

1. Attempted to perform an operation that was forbidden by the CLR host.

    Sol: Set PERMISSION_SET to UNSAFE Because EXTERNAL_ACCESS did not work for us.

2. The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 233)

    Sol: Note that everything is configured properly and WCF client inside stored procedure should initialize just fine. The problem is somehow connected to machine.config file or better, to some initialization of types defined there.

          Looks like the exception is linked to WCF debugging feature, usually installed on development machines - this debugging service has problems with WCF client running under SQL Server.

         

        Workaround: (Temporary) disable WCF debugging by invoking this command line utility:

        [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u

        Now the WCF client inside SQL Server runs just fine. After you've done you can re-enable WCF debugging 

services by calling same exe with different parameter:

       [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -i

Note that administrator privileges are required to disable/enable it.