我正在使用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的十六进制字符串。