CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
MAK
MAK

Reputation: 7260

Create assembly using stored procedure

I want to create an assembly in SQL Server 2008 R2 if it does not exist in the specific database by using a stored procedure, giving the path of the file as a parameter.

USE DBName
GO

CREATE PROCEDURE spCreateAssembly
@FilePath varchar(max)

AS

BEGIN

    IF NOT EXISTS (select 1 from sys.assembly_files f 
                full outer join  sys.assemblies a on f.assembly_id=a.assembly_id 
                full outer join  sys.assembly_modules m on a.assembly_id=m.assembly_id
                WHERE a.name = 'Lib1')
    BEGIN

        sp_configure 'show advanced options', 1
        RECONFIGURE
        GO
        sp_configure 'clr enabled', 1
        RECONFIGURE
        GO
        sp_configure 'show advanced options', 0
        RECONFIGURE
        GO

        Create Assembly Lib1 from @FilePath with Permission_set = SAFE

    END 

END

But getting an error:

Msg 102, Level 15, State 1, Procedure spCreateAssembly, Line 14 Incorrect syntax near 'sp_configure'.

Upvotes: 1

Views: 305

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

CREATE PROCEDURE spCreateAssembly
@FilePath varchar(max)

AS

BEGIN

    IF NOT EXISTS (select 1 from sys.assembly_files f 
                full outer join  sys.assemblies a on f.assembly_id=a.assembly_id 
                full outer join  sys.assembly_modules m on a.assembly_id=m.assembly_id
                WHERE a.name = 'Lib1')
    BEGIN

        EXEC sp_configure 'show advanced options', 1
        RECONFIGURE

        EXEC sp_configure 'clr enabled', 1
        RECONFIGURE

        EXEC sp_configure 'show advanced options', 0
        RECONFIGURE


        Create Assembly Lib1 from @FilePath with Permission_set = SAFE

    END 

END

You cannot use GO in stored procedure.

Upvotes: 1

Related Questions