How to create a SQL CLR script stored procedure installation without Visual Studio

Question

I am working on a CLR stored procedure using VS2010. I need to create a standalone deployment script to install this procedure on client servers. Now I use Visual Studio, which generate such a script when I press F5 and try to debug the SP on the database server. This script is placed in a file bin\Debug\MyStoredProcedure.sql. 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

, script Visual Studio? , , MSBuild, script - ? , , script - , , , ?

.

Answer 1

, , - , script . - :

            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

, .

Answer 2

, , , visual studio - SQL-; script, SSMS ( ) :

Script assembly as → ...

, DLL SQL script, .

Answer 3

, , , CLR, , Dll.

, Transact-SQL

.NET Framework.

Microsoft Visual #:

csc/target: C:\helloworld.cs

Microsoft Visual Basic :

vbc/target: C:\helloworld.vb

Visual # Visual Basic /target, , DLL.

.

SQL Server Management Studio . , (, AdventureWorks2008R2).

Transact-SQL .

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

, , , SQL . HelloWorld HelloWorld "", Transact-SQL , hello in SQL Server.

CREATE PROCEDURE hello

As

EXTERNAL NAME HelloWorld.Procedures.HelloWorld

Answer 4

You can get the binary assembly file from sys.assembly_files. Perhaps you can do something about it.

select *
from sys.assembly_files

Answer 5

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