CLR Assembly redeploy problem.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Amber
    #1

    CLR Assembly redeploy problem.

    I have created a C# library containing CLR stored procedures and user-
    define functions using Visual Studio 2005, and I have used Visual
    Studio to deploy the assembly to a SQL Server 2005 instance
    successfully, of course these sps and udfs are used by other T-SQL sps
    and udfs. Now the assembly has a new version, and I want to use it to
    replace the original one, but when I use Visual Studio 2005 to deploy
    the new assembly, the following error occurs:

    Error 1 Cannot drop the function 'TranslationStr ingLike', because it
    does not exist or you do not have permission.
    Cannot drop the function 'TranslateEngSt ring', because it does not
    exist or you do not have permission.
    Cannot drop the function 'TranslateEngSt ringReverse', because it does
    not exist or you do not have permission.
    DROP ASSEMBLY failed because 'FirmBankCLR' is referenced by object
    'TranslationStr ingLike'. FirmBankCLR

    That is the assembly is dependent by other objects, it can't be
    dropped until it is not dependent by other objects. How can I resolve
    this problem using Visual Studio 2005 or something else?

    Tags: None
    • Erland Sommarskog
      #2
      Re: CLR Assembly redeploy problem.

      Amber (guxiaobo1982@g mail.com) writes:
      I have created a C# library containing CLR stored procedures and user-
      define functions using Visual Studio 2005, and I have used Visual
      Studio to deploy the assembly to a SQL Server 2005 instance
      successfully, of course these sps and udfs are used by other T-SQL sps
      and udfs. Now the assembly has a new version, and I want to use it to
      replace the original one, but when I use Visual Studio 2005 to deploy
      the new assembly, the following error occurs:
      >
      Error 1 Cannot drop the function 'TranslationStr ingLike', because
      it does not exist or you do not have permission. Cannot drop the
      function 'TranslateEngSt ring', because it does not exist or you do not
      have permission. Cannot drop the function 'TranslateEngSt ringReverse',
      because it does not exist or you do not have permission. DROP ASSEMBLY
      failed because 'FirmBankCLR' is referenced by object
      'TranslationStr ingLike'. FirmBankCLR
      >
      That is the assembly is dependent by other objects, it can't be
      dropped until it is not dependent by other objects. How can I resolve
      this problem using Visual Studio 2005 or something else?
      As long as the interface of the assembly does not change, you can just
      do ALTER ASSEMBLY. Either specify directly, which is simple if the DLL
      is accessible from SQL Server. Or specify the DLL as a hexstring directly
      in the ALTER ASSEMBLY statement.

      If the interface has changed, you will need to drop all functions
      created from it, as well as dependent assemblies.

      I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
      of Visual Studio. I don't use Visual Studio to work with assemblies
      but stick to the command line. And all impression I get is that
      using Visual Studio just makes things harder.



      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Amber
          #3
          Re: CLR Assembly redeploy problem.

          I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
          of Visual Studio. I don't use Visual Studio to work with assemblies
          but stick to the command line. And all impression I get is that
          using Visual Studio just makes things harder.
          It seems Microsoft hasn't done the job well, but if we have a lot of
          objects in the assembly, the manual process of dropping and creating
          them is horrible.

            Comment

            • Erland Sommarskog
              #4
              Re: CLR Assembly redeploy problem.

              Amber (guxiaobo1982@g mail.com) writes:
              >I learnt just the other day that ALTER ASSEMBLY is not in the repetoir
              >of Visual Studio. I don't use Visual Studio to work with assemblies
              >but stick to the command line. And all impression I get is that
              >using Visual Studio just makes things harder.
              >
              It seems Microsoft hasn't done the job well, but if we have a lot of
              objects in the assembly, the manual process of dropping and creating
              them is horrible.
              Yes, it's horrible if you have to do it each time you change the
              implementation of some single method, and it's amazing that VS cannot
              handle this situation.

              If you change the interface, it's still a lot of work of course of dropping
              and recreating objects. But it is or more less inevitable.

              I don't know what's in your assemblies, but I woudl suggest that if you
              have a suite of functions and procedures that are independent of each other,
              that it's best to have a separate assembly for each function/procedure. Of
              course, if they use a lot of common code, you still need to put that common
              code in a single assembly in which case you are back to the same situation.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...