Tuesday 5 July 2011

Reexporting SQLCLR assembly as SQL script

Script that allows to import the Sql Server SQL CLR assembly into the database from the binary file and than export it as SQL script. Useful when you need to have a sql script for deployment rather then a dependency on the binary file. Uses tempdb as, well, temprorary storage. I use similar script as part of the Continuous Integration build. To allow that, I had to install SQL Server Express on the build server.

Resulting script will be in asmdump.sql. Remember, you will also need to explicitly add ALL procedures and functions to that SQL script, i.e.
CREATE PROCEDURE [spInsertSomething]
    @CustomerID [int],
    @InvoiceName [nvarchar](50),
    @StartDate [nvarchar](50),
    @EndDate [nvarchar](50)

AS
  EXTERNAL NAME [
MYNAME].[StoredProcedures].[spInsertSomething]
GO


REM START ======================================
@echo off
cd /D %~p0

SET DBSRV=%COMPUTERNAME%
SET DBNAME=tempdb
SET CREATORLOGIN=-E

SET ASMNAME=MYNAME

SET DLLNAME=SqlServerProject2.dll
SET LOGFILENAME=log.txt
IF EXIST %LOGFILENAME% del %LOGFILENAME%

SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -Q"IF  EXISTS (SELECT * FROM %DBNAME%.sys.assemblies asms WHERE asms.name = N'%ASMNAME%') DROP ASSEMBLY [%ASMNAME%]" >> %LOGFILENAME%
 

SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -Q"CREATE ASSEMBLY %ASMNAME% FROM '%CD%\%DLLNAME%' WITH PERMISSION_SET = SAFE" >> %LOGFILENAME%
 

bcp.exe "SELECT 'CREATE ASSEMBLY [%ASMNAME%] AUTHORIZATION [dbo] FROM ' + master.dbo.fn_varbintohexstr(saf.[content]) + ' WITH PERMISSION_SET = SAFE;' FROM %DBNAME%.sys.assemblies asms INNER JOIN %DBNAME%.sys.assembly_files saf ON saf.assembly_id = asms.assembly_id WHERE asms.name = N'%ASMNAME%' and is_user_defined = 1" queryout asmdump.sql -T -F1 -c -S "%DBSRV%" >> %LOGFILENAME% 

REM uncomment to reimport it into DB for verification
REM SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -i"%CD%\asmdump.sql" >> %LOGFILENAME%
IF %ERRORLEVEL%==0 GOTO finished

:errored
echo FINISHED WITH ERRORS!
goto done

:finished
echo FINISHED OK!
goto done

:done
pause

REM END ======================================

No comments:

Post a Comment