我正在使用VS2010进行CLR存储过程。我需要生成独立的部署脚本才能在客户服务器上安装此过程。现在,我使用Visual Studio,当我按F5键并尝试在DB服务器上调试SP时会生成此类脚本。该脚本位于bin\Debug\MyStoredProcedure.sql文件中。看起来像这样:

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

我想知道,是否可以在没有Visual Studio的情况下生成此类脚本?例如,如果我使用MSBuild构建解决方案,然后使用某些工具生成此脚本,该怎么办?我相信,如果我将程序集读取为字节数组,然后将其序列化为十六进制字符串并插入到脚本模板中,则可以工作,但是也许有一些更简单的标准解决方案?

谢谢。

最佳答案

或者,假设您已将程序集直接从Visual Studio部署到某个测试SQL服务器;通过右键单击SSMS(管理工作室)中的程序集来创建独立的部署脚本,然后选择:



这将在可用于单个文件部署的SQL脚本中为您编写代表DLL的十六进制字符串。

10-06 05:36
查看更多