none
SQL CLR

    Question

  • Hi,

     

    I am trying to invoke WCF service from SQL and regarding that i found we could do that by using SQL CLR. So i have created a small WCF Service and got it's reference in SQL project and compile it in .dll; now i am writing a SQL script to create the T-SQL wrapper function around your WCF call of the compiled SQL project dll and for that i am writing the following script

    use master;
    go

    sp_configure 'clr enabled', 1
    go
    reconfigure
    go

    use [TestDB];
    go

    alter database [TestDB]
    set trustworthy on;
    go

    ---------------------------------------------------------------------

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'SMDiagnostics')
        DROP ASSEMBLY [SMDiagnostics]
    GO

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'System.Web')
        DROP ASSEMBLY [System.Web]
    GO

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'System.Messaging')
        DROP ASSEMBLY [System.Messaging]
    GO

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'System.ServiceModel')
        DROP ASSEMBLY [System.ServiceModel]
    GO

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'WcfServiceSQLCLR')
        DROP ASSEMBLY [WcfServiceSQLCLR]
    GO

    IF  EXISTS (SELECT * FROM sys.assemblies asms
    WHERE asms.name = N'SqlServerProject.XmlSerializers')
        DROP ASSEMBLY [SqlServerProject.XmlSerializers]
    GO

    ---------------------------------------------------------------------

    create assembly SMDiagnostics
    from  'C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'
    with permission_set = unsafe
    go

    create assembly [System.Web]
    from  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.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

    create assembly [System.ServiceModel]
    from 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll'
    with permission_set = unsafe
    go


    --now when you have the necessary assembles in the database
    --it is time to load my user assembly


    create assembly WcfServiceSQLCLR
    from 'D:\Projects\SQLCLR\SqlServerProject\SqlServerProject\bin\Debug\SqlServerProject.dll'
    with permission_set = unsafe
    go



    CREATE ASSEMBLY [SqlServerProject.XmlSerializers]
    FROM 'D:\Projects\SQLCLR\SqlServerProject\SqlServerProject\bin\Debug\SqlServerProject.XmlSerializers.dll'
    WITH PERMISSION_SET = SAFE;
    GO

    --check to see you have the assembly in the db
    select * from sys.assemblies;
    go

    --now create the T-SQL wrapper function around your WCF call
    create function WcfaddData(@x int, @y int)
    returns int
    external name SqlServerProject.[SqlServerProject].WcfClient  --Assembley.Namespace.Class
    go


    now when i run this script i am getting a following error:

    Msg 6528, Level 16, State 1, Procedure WcfaddData, Line 8
    Assembly 'SqlServerProject' was not found in the SQL catalog of database 'TestDB'.

     

    i don't know why i am getting this error eventhough i checked the external name (Assembley.Namespace.Class) that is SqlServerProject.[SqlServerProject].WcfClient.

    Can anyone help me out in this ? Please,let me know what i am doing wrong. I tried my best but couldn't make it up.

     

     

    --JP


    Jai P Sharma
    • Changed type JP S Sunday, September 05, 2010 7:59 PM should be in different group
    • Moved by Pawel Marciniak Tuesday, September 07, 2010 4:53 PM (From:SQL Service Broker)
    Sunday, September 05, 2010 5:33 AM

Answers

  • Hi Jai,

    You named your assembly WcfServiceSQLCLR here in the create assembly statement:

    create assembly WcfServiceSQLCLR
    from 'D:\Projects\SQLCLR\SqlServerProject\SqlServerProject\bin\Debug\SqlServerProject.dll'
    with permission_set = unsafe

    So your create function statement needs to reference the assembly name (WcfServiceSQLCLR) rather than the DDL name (SqlServerProject.dll) here:

    --now create the T-SQL wrapper function around your WCF call
    create function WcfaddData(@x int, @y int)
    returns int
    external name WcfServiceSQLCLR.[SqlServerProject].WcfClient  --Assembley.Namespace.Class  -- first part of external name is SQL Server assembly name
    go

    BTW, in future you'll likely get a faster response posting to the SQLCLR forum rather than the Service Broker forum.

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by JP S Monday, September 06, 2010 1:46 AM
    Sunday, September 05, 2010 4:35 PM
    Moderator
  • Hi Jai,

    Except its not "Assembly.Namespace.Class", but "Assembly.ClassName.Method" where the ClassName includes the Namespace (if any). Otherwise, how would the engine know which method to call if you had more than one? So:

    create function WcfaddData(@x int, @y int)
    returns int
    external name WcfServiceSQLCLR .[SqlServerProject.WcfClient].WcfAdderClient  -- Assembly, Class, Method

    For spocs, UDFs, and triggers you are cataloging a method in a class, so you need a 3-part name with a method as the last part. For UDTs and UDAggs its a two-part name because you are cataloging the class.

    Cheers, Bob

     

     

    • Marked as answer by JP S Monday, September 06, 2010 1:45 AM
    Sunday, September 05, 2010 11:21 PM
    Moderator

All replies

  • Hi Jai,

    You named your assembly WcfServiceSQLCLR here in the create assembly statement:

    create assembly WcfServiceSQLCLR
    from 'D:\Projects\SQLCLR\SqlServerProject\SqlServerProject\bin\Debug\SqlServerProject.dll'
    with permission_set = unsafe

    So your create function statement needs to reference the assembly name (WcfServiceSQLCLR) rather than the DDL name (SqlServerProject.dll) here:

    --now create the T-SQL wrapper function around your WCF call
    create function WcfaddData(@x int, @y int)
    returns int
    external name WcfServiceSQLCLR.[SqlServerProject].WcfClient  --Assembley.Namespace.Class  -- first part of external name is SQL Server assembly name
    go

    BTW, in future you'll likely get a faster response posting to the SQLCLR forum rather than the Service Broker forum.

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by JP S Monday, September 06, 2010 1:46 AM
    Sunday, September 05, 2010 4:35 PM
    Moderator
  • hi Bob,

    No luck. Still facing the same error. Following is my SQL project code which compelling as SqlServerProject.dll, Now can you please have a look into my code and tell me what i am doing wrong to create t-sql wrapper function, i mean what is still missing in this create function.

    create function WcfaddData(@x int, @y int)
    returns int
    external name WcfServiceSQLCLR .[SqlServerProject].WcfClient   --Assembley.Namespace.Class  -- first part of external name is SQL Server assembly name
    go

     

    --------Error-------------------------------------
    Msg 6505, Level 16, State 1, Procedure WcfAdderClient, Line 3
    Could not find Type 'SqlServerProject' in assembly 'SqlServerProject'.

     

    ------------------------------------------------------

    Following is my SQL project code class where i am getting reference of WCF Service and exposing the method

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.ServiceModel;
    using SqlServerProject.ServiceReference;

    namespace SqlServerProject
    {
        class WcfClient
        {
            public static int WcfAdderClient(int x, int y)
            {
                EndpointAddress ep = new EndpointAddress("http://localhost/WcfServiceServer/Service1.svc");
                Service1Client proxy = new Service1Client(new WSHttpBinding(), ep);
                int ret = proxy.WcfAdder(x, y);
                return ret;
            }
        }
    }

    --Jai


    Jai P Sharma
    • Marked as answer by JP S Monday, September 06, 2010 1:45 AM
    • Unmarked as answer by JP S Monday, September 06, 2010 1:46 AM
    Sunday, September 05, 2010 5:20 PM
  • Hi Jai,

    Except its not "Assembly.Namespace.Class", but "Assembly.ClassName.Method" where the ClassName includes the Namespace (if any). Otherwise, how would the engine know which method to call if you had more than one? So:

    create function WcfaddData(@x int, @y int)
    returns int
    external name WcfServiceSQLCLR .[SqlServerProject.WcfClient].WcfAdderClient  -- Assembly, Class, Method

    For spocs, UDFs, and triggers you are cataloging a method in a class, so you need a 3-part name with a method as the last part. For UDTs and UDAggs its a two-part name because you are cataloging the class.

    Cheers, Bob

     

     

    • Marked as answer by JP S Monday, September 06, 2010 1:45 AM
    Sunday, September 05, 2010 11:21 PM
    Moderator
  • Hi Bob,

     

    First of all thanks. Yes i found this today myself what was the issue just after asking you and whatever you are saying the issue was exactly same. With class i had to define the namespace and i did it and my problem resolve.

    Once again thanks for quick reply with answer.

     

    --Jai


    Jai P Sharma
    Monday, September 06, 2010 1:41 AM