SQL Server Performance

Taking a CLR stored procedure to other server

Discussion in 'SQL Server 2005 CLR' started by EMoscosoCam, Jul 31, 2008.

  1. EMoscosoCam Member

    I have a developer server where I have deployed an CLR stored procedure using VS2008. I would like to take this updated CLR stored procedure to an already deployed database, which is located in other server and I do no have VS2008 in that office. How can I deploy this SP?
    Thanks a lot.
  2. Dolson New Member

    You can perform the deployment manually by "Creating CLR Database Objects". To do this, you essentially need to move the compiled DLL to a directory or file share where it can be accessed by SQL Server. Then run the CREATE ASSEMBLY statement to register the DLL and copy it into the database.

    create assembly usp_ImportFile
    from 'C:tempusp_ImportFile.dll'

    The CREATE ASSEMBLY statement copies the contents of the usp_ImportFile.dll file in the c:temp directory into the SQL Server database. The WITH PERMISSION SET clause is used to specify that this assembly can access resources that are external to the SQL Server database. That's needed here because the stored procedure reads an external file.

    CREATE PROCEDURE usp_ImportFile
    @filename nvarchar(1024),
    @columnname nvarchar(1024) OUT
    EXTERNAL NAME usp_ImportFile.[usp_ImportFile.StoredProcedures].usp_ImportFile

    The CREATE PROCEDURE statement is used to create a new SQL Server stored procedure that uses the CLR assembly. This CLR stored procedure uses two parameters. The first is an input parameter, and the second is an output parameter. The EXTERNAL NAME clause uses a three-part name to identify the target method in the DLL. The first part of the name refers to the assembly name. The second part refers to the class. If the class is part of a namespace, as is the case here, then the namespace must preface the class name and both should be enclosed in brackets. Finally, the third part of the name identifies the method that will be executed.

Share This Page