How to generate SQL CLR stored procedure installation script w/o Visual Studio

Question

I am working on CLR stored procedure using VS2010. I need to generate standalone deployment script to install this procedure at customer servers. Now I am using Visual Studio which generate such script when I press F5 and try to debug SP on DB server. This script is placed at bin\Debug\MyStoredProcedure.sql file. It looks like this:

USE [$(DatabaseName)]

GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping [dbo].[spMyStoredProcedure]...';


GO
DROP PROCEDURE [dbo].[spMyStoredProcedure];


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Dropping [MyStoredProcedure]...';


GO
DROP ASSEMBLY [MyStoredProcedure];


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Creating [MyStoredProcedure]...';


GO
CREATE ASSEMBLY [MyStoredProcedure]
    AUTHORIZATION [dbo]
-- here should be long hex string with assembly binary
    FROM 0x4D5A90000300000004000000FFFCD21546869732070726F6772616D...000000000000000000 
    WITH PERMISSION_SET = SAFE;


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
PRINT N'Creating [dbo].[spMyStoredProcedure]...';


GO
CREATE PROCEDURE [dbo].[spMyStoredProcedure]
@reference UNIQUEIDENTIFIER, @results INT OUTPUT, @errormessage NVARCHAR (4000) OUTPUT
AS EXTERNAL NAME [MyStoredProcedure].[MyCompany.MyProduct.MyStoredProcedureClass].[MyStoredProcedureMethod]


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END


GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'The transacted portion of the database update succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT N'The transacted portion of the database update failed.'
GO
DROP TABLE #tmpErrors
GO

I am wondering, is it possible to generate such script without Visual Studio? For example, what if I build solution with MSBuild and then generate this script with some tool? I believe, that if I read assembly as byte array and then serialize it to hex string and insert into script template - it could work, but maybe there is some easier standard solution?

Thanks.

Answer 1

Alternately, assuming you have the assembly deployed direct from visual studio to some test SQL server; create a standalone deployment script by right clicking the assembly in SSMS (management studio) and select:

Script assembly as -> Create To...

This will write the hex string representing the DLL for you in an SQL script you can use for a single file deployment.

Answer 2

Well, it seems that the only way is to read assembly as binary file and then generate script using template above. Something like that:

            using (var str = File.OpenRead(pathToAssembly))
            {
                int count = 0;
                do
                {
                    var buffer = new byte[1024];
                    count = str.Read(buffer, 0, 1024);

                    for (int i = 0; i < count; i++)
                    {
                        hexStringBuilder.Append((buffer[i] >> 4).ToString("X"));
                        hexStringBuilder.Append((buffer[i] & 0xF).ToString("X"));
                    }
                } while (count > 0);
            }
            // generate script using template from initial question

I've checked this approach and it works.

Answer 3

What you've described should work fine but as Deploying CLR Database Objects describes it seems easier to just to reference the compiled Dll.

To deploy the assembly using Transact-SQL

Compile the assembly from the source file using the command line compilers included with the .NET Framework.

For Microsoft Visual C# source files:

csc /target:library C:\helloworld.cs

For Microsoft Visual Basic source files:

vbc /target:library C:\helloworld.vb

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

Resolve all build errors and warnings before deploying the assembly to the test server.

Open SQL Server Management Studio on the test server. Create a new query, connected to a suitable test database (such as AdventureWorks2008R2).

Create the assembly in the server by adding the following Transact-SQL to the query.

CREATE ASSEMBLY HelloWorld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

The procedure, function, aggregate, user-defined type, or trigger must then be created in the instance of SQL Server. If the HelloWorld assembly contains a method named HelloWorld in the Procedures class, the following Transact-SQL can be added to the query to create a procedure called hello in SQL Server.

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorld.Procedures.HelloWorld

Answer 4

Read this post and instead of use /Action:Publish use /Action:Script /OutputPath:D:\deploymentScript.sql