none
Calling .Net Assembly or Dll from SQL Server 2005 at config Level 80

    Question

  • Hi,
    I create a dll assembly with the strong name in VB.Net environment.
    Created assembly is registered also.
    SQL Server 2005 configuraton level is set at "80"
    I want to call that assembly from stored procedure with the database config level at "80"
    But when i execute the stored proecure i get the following error
    Error Source: "ODSOLE Extended Procedure"
    Description: "Invalid Class String"

    My Code in VB.Net is given below:

    Imports System
    Imports System.Reflection
    Imports System.Globalization
    Imports System.IO
    Imports System.data
    Imports System.Data.SqlClient
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.VisualBasic
    Imports System.Diagnostics
    Imports GreatDataAccess

    Namespace
    Test
    Public Class clsTest

            Public Shared Sub GenTest()
                  ''''............ some code is here
           End Sub
    End Class
    End
    Namespace

    Stored Procedure
    CREATE PROCEDURE [dbo].[PPGenerateFile]
    AS
    BEGIN
    Declare @retVal INT
    Declare @comHandler INT
    declare @errorSource nvarchar(500)
    declare @errorDescription nvarchar(500)
    declare @retString nvarchar(100)

    -- Intialize the COM component

    EXEC @retVal = sp_OACreate Test.clsTest, @comHandler OUTPUT
          
    IF(@retVal <> 0)
         
     BEGIN
              --Trap errors if any
              EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT 
             SELECT
    [error source] = @errorsource, [Description] = @errordescription
             Return
          
    END

    -- Call a method into the component

          EXEC @retVal = sp_OAMethod @comHandler,'GenTest()',@retString

    IF (@retVal <>0 )
    BEGIN
    EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
    SELECT
    [error source] = @errorsource, [Description] = @errordescription
    Return
    END
    select
    @retString

    END


    Please Help me to solve this problem.

    Wednesday, July 26, 2006 7:45 AM

All replies

  • Hi Jawad,

    here is how you do it, though to be honest I am not calling sp_OAMethod correctly (getting a parameter error) but here is the general solution...

    1. Create your class (see below for my sample), use a class library template
    2. Register the class via the "Register for COM Interop" setting in the project's properties (see http://support.microsoft.com/?kbid=817248 for more info)
    3. Now create your stored procedure to consume the new COM object and test it. Your done!

    Public Class Math

    Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)

    Return (iFirstNum + iSecondNum)

    End Function

    End Class

     

     

    ALTER PROCEDURE [dbo].[PPGenerateFile]

    As

    Begin

    Declare @retVal INT

    Declare @comHandler INT

    Declare @errorSource nvarchar(500)

    Declare @errorDescription nvarchar(500)

    Declare @Result INT

    -- Intialize the COM component

    EXEC sp_OACreate 'myCOMObject.Math', @comHandler OUTPUT

    IF(@retVal <> 0)

    BEGIN

    --Trap errors if any

    EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT

    SELECT [error source] = @errorsource, [Description] = @errordescription

    Return

    END

    -- Call a method into the component

    EXEC @retVal = sp_OAMethod @comHandler,'Add', @iFirstNum = '1',@iSecondNum = '1'

    IF (@retVal <>0 )

    BEGIN

    EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT

    SELECT [error source] = @errorsource, [Description] = @errordescription

    Return

    END

    END

     

    Thursday, July 27, 2006 4:42 AM
  • Hi,

    Thanks to response me.
    Now i am getting following error when access the method of class

    Error Source: ODSOLE Extended Procedure
    Error Description: sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    Looking forward for your quick response.

    Best Regards,
    Jawad Naeem

    Friday, July 28, 2006 9:23 AM
  • Hi,

    I am curious to understand why you are using sp_OA* and not CLR integration? I recommend that you use CLR integration instead.

    Thanks,

    -Vineet.

    Friday, July 28, 2006 7:31 PM
  • Error 1 Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. TestConfigLevel80

    YOU CANNOT USE CLR INTEGRATION WITH NON 2005 DB COMPATABILITY LEVELS. THIS IS WHY HE NEEDS TO USE sp_OA.

    Friday, July 28, 2006 8:08 PM
  • Actually, you can use CLR features fine, you just can't create them in dbcmptlevel < 90.

    Rather than messing around with sp_OA, I think it would be easier to do the following:

    sp_dbcmptlevel 'db', 90
    go
    create assembly GenTest from ....
    go
    create proc GenTest as external name ...
    go
    sp_dbcmptlevel 'db', 80
    go
    exec GenTest

    :-)

    Friday, July 28, 2006 11:55 PM
  • STEVE wins this round :) Hey dude...why do I have "god mode". I can mark an answer to any thread I want lol
    Saturday, July 29, 2006 12:22 AM
  • In April, it was announced that all users with more than 5 answers would be given the ability to mark other replies as answers, even if they did not ask the question.  The announcement about that is here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=339712&SiteID=1

    I don't see your name on that list, however it is possible that another pass was made and you were added more recently.

    PS You think being able to mark a reply as an answer amounts to god mode? Pssht, I'll show you god mode... ;-)

    Saturday, July 29, 2006 12:50 AM
  • "to mark other replies as answers"

    so explain why i just marked MY reply as an answe...THAT IS GOD MODE DUDE! :) I get SQLMVP quick this way lol

    Saturday, July 29, 2006 1:04 AM
  • Jawad, if you do what steve said it should work. I never thought about "playing" with the compatability levels between phases.

    My marked answers are only temporarily I assure you, I hate cheaters :)

    Saturday, July 29, 2006 3:18 PM
  • Hi Derek,
    I am already told you that my database was developed in SQL Server 2000. Then we switch to SQL Server 2005 with the new .net framework 2.0. But we keep our database on configuration level 80 to maintane the compatibility. Currently we cant switch to 90 level due to some some constraints.

    Please send me solution of my problem which i reported in my post.
    looking forward for quick response.
    one thing more Steve you did not win because i am still looking for the solution of error which i mentioned in my last post.

    Regards,
    Jawad Naeem
    Tuesday, August 01, 2006 5:00 AM
  • You need to supply a return value after the method name:

    EXEC @retVal = sp_OAMethod @comHandler,'Add', @iFirstNum = '1',@iSecondNum = '1'

    should be:

    declare @sum int

    EXEC @retVal = sp_OAMethod @comHandler,'Add', @sum OUT, @iFirstNum = '1',@iSecondNum = '1'

    Let me know if it works for you.

    Ping

     

    Tuesday, August 01, 2006 6:32 PM
  • Hi,

    Thank for the reply, but it still shows the same following error.
    sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    I need this solution badly. Please do something.

    Thank again
    Jawad Naeem

    Wednesday, August 02, 2006 6:12 AM
  • I will assist this evening if you still need it.

    D

    Wednesday, August 02, 2006 6:38 PM
  • HI,
    I find an extended stored procedure "xp_cmdshell" to create and append the text file from stored procedure. whose example is as given below:

    xp_cmdshell "@ECHO test message >> C:\file.txt"

    By using this method currently it seems that my goal can achieve such that i fectch the record and write in the file, but I just want to know how much this extended method is secure to use if i call it from my own DB's stored procedure.

    Looking forward for quick response.

    Best Regards,
    Jawad Naeem
    Thursday, August 03, 2006 5:50 AM
  • Could you post the complete script you use and complete error message you got? Also the prototype of the COM object you created?

    This error message offen is caused by unmatching caller and callee in parameter passing.

     

    Thanks!

    Ping

    Thursday, August 03, 2006 2:35 PM
  • Yes i post the complete script before but i post it again for your clear understanding:

    VB.Net code

    Namespace mathProject
        Public Class MathProject
            
    Public Shared Function Add(ByVal n1 As Integer, ByVal n2 As Integer) As Integer
                 
    Dim num3 As Integer
                  
    num3 = n1 + n2
                 
    Return num3
            
    End Function
        
    End Class
    End
    Namespace

    Stored Procedure
    set ANSI_NULLS ON
    set
    QUOTED_IDENTIFIER ON
    go

    CREATE PROCEDURE [dbo].[PPGenerateFile]
    As
    Begin
    Declare @retVal Int
    Declare @comHandler INT
    Declare @errorSource nvarchar(500)
    Declare @errorDescription nvarchar(500)
    Declare @Result INT

     -- Intialize the COM component
    EXEC sp_OACreate 'myComProject.MathProject', @comHandler OUTPUT
    IF(@retVal <> 0)
    BEGIN
    --Trap errors if any
    EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
    SELECT [error source] = @errorsource, [Description] = @errordescription
    Return
    END

    declare @sum int

    -- Call a method into the component
    EXEC @retVal = sp_OAMethod @comHandler,'Add',@sum OUT,@n1=2,@n2=2
    IF (@retVal <>0 )
    BEGIN
    EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
    SELECT [error source] = @errorsource, [Description] = @errordescription
    Return
    END
    END

    Error Description: sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

    Still looking for solution

    Best Regards,
    Jawad Naeem

     


    Friday, August 04, 2006 5:22 AM
  • Jawad,

    According to this KB article via the other thead I don't believe you can call CLR COM Interop from sp_OA procs.

     

    COM Interoperability

    This section specifically addresses the use of OLE Automation in SQL Server and it applies to both in-process and out-of-process COM objects. Assembly meta data for function interfaces implements a strongly-typed mechanism for any invocations.

    As part of this design, the COM Callable wrapper for an assembly must use an external mechanism of mapping a ClassID to a member of a managed class. Because of this explicit mapping, there is no ability from an unmanaged perspective to establish a root list of available interfaces.

    The extended stored procedure sp_oaCreate uses the IUnknown::QueryInterface interface to determine the object's support for a particular interface. The interoperability between CLR and unmanaged code relies on the IDispatch interface for implementing interfaces. Because there is no equivalent to a QueryInterface method to a CLR-based assembly, you cannot create an instance of the object.

    Derek

    Monday, August 07, 2006 4:04 AM
  • Jawad,

    In summary, if you need to use .Net assemblies from within SQL Server 2005 running at comp. level 80 (and you cannot change comp levels) then I believe you are STUCK.

    please reply and let me know your thougths on the matter.

    Derek

    Wednesday, August 09, 2006 12:32 PM
  • Hi,
    Yes i am struck on my architecture for my solution. At one hand Microsoft claims that Ms Visual studio.net has backward compatibility but i think it is incorrect.

    Thursday, August 10, 2006 4:42 AM
  • If the function you are trying to create is generic (e.g. a RegEx function) then you could put it in tempdb or another DB that you create in 2005.  Therefore it will be at level 90 and will work.

     

    Friday, January 04, 2008 3:57 PM