用命令行自动化SQL Server 2000相关系统的发布

背景：

前段时间做了一套SQL Server2000的报表服务器系统。其前端显示是SQL Server 2000报表服务器；后台数据为SQL Server 2000 OLTP和SQL Server 2000 OLAP；服务器为Window 2003；ETL层面全部由DTS+Stored Proc来实现；还有小部分前台控制逻辑由VB6.0实现。同时也为其准备了一套完整的产品发布流程。一套简单实用的产品发布流程序对我们来说至关重要：

1. 为了数据安全，系统稳定和更好的控制，我们一般都会隔离产品，用户测试和系统开发这3个不同的区域；

2. 系统开发人员不能访问用户测试和产品服务器, 同样, 用户测试人员不能访问产品服务器；

3. 每次上系统的时候都必须严格遵从既定流程，在测试结果通过后，由产品控制部门的人员而不是系统开发人员将系统发布到各个不同区域。相对于产品开发人员，产品控制部门有更高一级的权限，他们会严格遵守系统开发部的方案去发布系统，但同时由于他们不是系统的开发人员，对系统本身缺乏了解，在发布过程中即使出了些小问题也无法解决。更何况我们的系统发布部门都是外包给其他公司，系统开发人员根本没机会动到产品数据。

所有这些都要求我们有一套简单实用的产品发布流程，而最通俗的做法就是把所有这些流程都整理成DOS Script存放在*.bat 文件里面。总结了一下迁移／发布的流程中用到的命令行：

1. SQL Server 2000 Reporting Service对象（报表模板*.rdl；共享数据源；目录管理）

2. SQL Server 2000 SQL Statement 程序迁移流程，其中包含新建／修改／删除　表／存储过程／视图／数据etc.

3. 使用CDONTS发Email给项目干系人

4. SQL Server 2000 Analysis Service (OLAP)对象

5. SQL Server 2000 DTS Package

6. 其他

SQL2K Reporting Service

毕竟是新出来的东东（这么说有点老土，现在SQL Server 2005都出来的...不过毕竟Reporting Service 是2K版才出来的新东西。），微软在这方面的联机帮助还是比较详细的：

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
Reporting Service 本身为我们提供了很好的页面功能去管理Reporting Service的对象，包括发布新的报表，数据源，新建和管理目录等等。第二种方法，我们也可以在.NET 2003的BI项目中通过在项目属性中指定项目target直接将报表系统的deploy到Reporting Server的URL中。不过鉴于Online操作的流程复杂性和.NET2003deployment的可行性，我们决定采用第三种方法，即把所有这些流程操作写到脚本中，让产品控制部门运行这些脚本。需要迁移的对象准备还有脚本都由开发人员准备好，得到批准之后由产品控制部门去运行。

由开发部准备以下脚本和报表模板

报表模板：\\迁移服务器\ReportTemplate\REPORT_TEST.rdl

内容略

publish2Prod.bat(由产品控制部门运行，一次开发以后都用此Script作为迁移用途)

请参考如下publish2UAT.bat

publish2UAT.bat(由产品控制部门运行，一次开发以后都用此Script作为迁移用途)

@SET Server=用户测试服务器
@SET RSSPath="\\迁移服务器\RSS"
rs -i %RSSPath%\Script_%Server%.rss -s http://%产品服务器%/reportserver/

Script_产品服务器.rss(对于每次变更，由产品开发部门每次定制)

请参考如下Script_用户测试服务器.rss

Script_用户测试服务器.rss(对于每次变更，由产品开发部门每次定制)

Public Sub Main()

    Dim name As String
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    '--------------------------------------------------------
    '在Reporting Service根目录[/]下新建一个目录 -- [TestingFolder]
    '在Reporting Service根目录[/]下新建一个目录 -- [TestingDataSourceFolder]
    '--------------------------------------------------------
    CreateReportFolder("TestingFolder", "/")
    CreateReportFolder("TestingDataSourceFolder", "/")

    '--------------------------------------------------------
    '在Reporting Service目录[TestingDataSourceFolder]下新建共享数据源[TestingDataSource]
    '--------------------------------------------------------
    CreateDataSource("TestingDataSource", "/TestingDataSourceFolder")

    '--------------------------------------------------------
    ' 从本地目录[\\迁移服务器\ReportTemplate]将报表[REPORT_TEST]
    ' 发送到Reporting Service目录[TestingFolder]
    '--------------------------------------------------------
    PublishReport("REPORT_TEST", "\\迁移服务器\ReportTemplate", "/TestingFolder")

    '--------------------------------------------------------
    ' 将已经发布的报表[/TestingFolder/REPORT_TEST]的数据源
    ' [TestingDataSource]的Reference改成[/TestingDataSourceFolder/TestingDataSource]
    ' 但不知道为什么这里总是失败．．．
    '--------------------------------------------------------
    'ReSetDataSource("TestingDataSource", "/TestingDataSourceFolder/TestingDataSource", "/TestingFolder/REPORT_TEST")

End Sub

Public Sub CreateDataSource(ByVal strPDataSourceName As String, ByVal strPRptPath As String)

    Dim definition As New DataSourceDefinition()
    definition.CredentialRetrieval = CredentialRetrievalEnum.Store
    definition.ConnectString = "DATA SOURCE= 数据库服务器名;INITIAL CATALOG=数据库名"
    definition.Enabled = True
    definition.EnabledSpecified = True
    definition.Extension = "SQL"
    definition.ImpersonateUser = False
    definition.ImpersonateUserSpecified = True
    definition.UserName = 登录名字
    definition.Password = 登录密码
    definition.Prompt = Nothing
    definition.WindowsCredentials = False

Try
            rs.CreateDataSource(strPDataSourceName, strPRptPath, False, definition, Nothing)
            Console.WriteLine("===========================================================================")
            Console.WriteLine("== Reporting DS: {0} has been created under {1} ", strPDataSourceName, strPRptPath)
            Console.WriteLine("===========================================================================")
            Catch e As Exception
            Console.WriteLine("################################################################")
            Console.WriteLine("##                            ERROR")
            Console.WriteLine("## Create DataSource Error!!!")
            Console.WriteLine("## DataSource    : {0}", strPDataSourceName)
            Console.WriteLine("## Folder        : {0}", strPRptPath)
            Console.WriteLine("## Error Message : {0}", e.Message)
            Console.WriteLine("################################################################")
End Try

End Sub

Public Sub ResetDataSource(ByVal strPDataSourceName As String, ByVal strPDSReferenceWholePath As String, ByVal strPRptWholePath As String)

Dim reference as New DataSourceReference()
reference.Reference = strPDSReferenceWholePath
Console.WriteLine("$$reference.Reference: {0}", reference.Reference.ToString())

Dim dataSources(1) As DataSource
Dim ds As New DataSource()
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = strPDataSourceName
Console.WriteLine("$$ds.Name: {0}", ds.Name)
dataSources(0) = ds

if isnull(dataSources)=true then
Console.WriteLine("$$dataSources is null")
end if

Try
            Console.WriteLine("$$strPRptWholePath: {0}", strPRptWholePath)
            rs.SetReportDataSources(strPRptWholePath, dataSources)
            Console.WriteLine(".")
            Console.WriteLine("===========================================================================")
            Console.WriteLine("== Report : {0}", strPRptWholePath)
            Console.WriteLine("== New reference of data source {0} has been reset to : {1}", strPDataSourceName, strPDSReferenceWholePath)
            Console.WriteLine("===========================================================================")

Catch e As SoapException
            Console.WriteLine("################################################################")
            Console.WriteLine("##                            ERROR")
            Console.WriteLine("## Reset Data Source Error!!!")
            Console.WriteLine("## Report         : {0}", strPRptWholePath)
            Console.WriteLine("## Data Source    : {0}", strPDataSourceName)
            Console.WriteLine("## Reference Path : {0}", strPDSReferenceWholePath)
            Console.WriteLine("################################################################")
            Console.WriteLine(e.Detail.InnerXml.ToString())
End Try

End Sub

Public Sub CreateReportFolder(ByVal strPFolderName As String, ByVal strPRptPath As String)

    Try
            rs.CreateFolder(strPFolderName, strPRptPath, Nothing)
            Console.WriteLine("===========================================================================")
            Console.WriteLine("== Reporting Folder: {0} has been created under Path {1} ", strPFolderName, strPRptPath)
            Console.WriteLine("===========================================================================")
    Catch e As Exception
            Console.WriteLine("################################################################")
            Console.WriteLine("##                            ERROR")
            Console.WriteLine("## Create Reporing Folder Error!!!")
            Console.WriteLine("## Path          : {0}", strPRptPath)
            Console.WriteLine("## Folder        : {0}", strPFolderName)
            Console.WriteLine("## Error Message : {0}", e.Message)
            Console.WriteLine("################################################################")
    End Try

End Sub

Public Sub PublishReport(ByVal strPReportFileName As String, ByVal strPReportFromPath As String, ByVal strPReportToPath As String)
    Try
        Dim stream As FileStream = File.OpenRead(strPReportFromPath + strPReportFileName + ".rdl")
        definition = New [Byte](stream.Length) {}
        stream.Read(definition, 0, CInt(stream.Length))
        stream.Close()
    Catch e As IOException
        Console.WriteLine(e.Message)
    End Try

Try
warnings = rs.CreateReport(strPReportFileName, strPReportToPath, False, definition, Nothing)

        If Not (warnings Is Nothing) Then
            Dim warning As Warning
            Console.WriteLine("################################################################")
            Console.WriteLine("##                          Warning")
            Console.WriteLine("## From Path     : {0}", strPReportFromPath)
            Console.WriteLine("## To Folder     : {0}", strPReportToPath)
            Console.WriteLine("## Report Name   : {0}", strPReportFileName)
            For Each warning In warnings
            Console.WriteLine("## Report Name   : {0}", warning.Message)
            Next warning
            Console.WriteLine("################################################################")

            Console.WriteLine("===========================================================================")
            Console.WriteLine("== Report: {0} published with warnings", strPReportFileName)
            Console.WriteLine("===========================================================================")
        Else
            Console.WriteLine("===========================================================================")
            Console.WriteLine("== Report: {0} published successfully with no warnings", strPReportFileName)
            Console.WriteLine("===========================================================================")
        End If

    Catch e As Exception
            Console.WriteLine("################################################################")
            Console.WriteLine("##                            ERROR")
            Console.WriteLine("## Publish Report Error!!!")
            Console.WriteLine("## From Path     : {0}", strPReportFromPath)
            Console.WriteLine("## To Folder     : {0}", strPReportToPath)
            Console.WriteLine("## Report Name   : {0}", strPReportFileName)
            Console.WriteLine("## Error Message : {0}", e.Message)
            Console.WriteLine("################################################################")
    End Try
End Sub

为了简单起见我们光介绍报表开发完后迁移到用户测试环境的步骤：

1. 开发人员在.NET2003 BI项目中开发完报表模板REPORT_TEST后将REPORT_TEST.rdl文件保存到\\迁移服务器\ReportTemplate\REPORT_TEST.rdl.

2. 准备Script文件\\迁移服务器\RSS\Script_用户测试服务器.rss

o 新建\TestingFolder目录

o 新建\TestingDataSourceFolder目录

o 新建数据源TestingDataSource在\TestingDataSourceFolder目录下面

o 发布报表\\迁移服务器\ReportTemplate\REPORT_TEST.rdl到\TestingFolder目录

o 用Sub ResetDataSource重新设定报表的DataSource Reference,　但这个步骤老是执行出错，也没空去寻找答案，为了快速解决问题我们用了另外一种方案。

§ *.rdl文件其实是个ＸＭＬ格式的文件，可以直接以文本的方式打开文件\\迁移服务器\ReportTemplate\REPORT_TEST.rdl

§ 查找DataSources

§ 直接修改DataSource 的Reference成自己想要的DataSource Reference.

3. 通知产品控制人员运行bat文件 publish2UAT.bat 开始发布

相关连接：

Reporting Service部署指南

http://www.microsoft.com/china/technet/prodtechnol/sql/2000/deploy/rsdepgd.asp

SQL Server 2000 SQL Statement迁移

SQL Statement的处理相对简单点，其间只涉及到两个批处理文件：

BatchRunMaster.bat(一次准备，以后产品控制部门都用此script运行需要发布的SQL语句)

rem ###################################################################
rem ## Main Start
rem ###################################################################

rem ##################################################
rem ## 1) Get Time
rem ##################################################
for /f "tokens=2,3,4 delims=/ " %%i IN ('date/t') DO @set a=%%k%%i%%j
for /f "tokens=1,2 delims=: " %%i IN ('time/t') DO @set b=%%i%%j

...

变量初始化

检查逻辑

出错处理

...

call %BatchRun_Path%\%BatchRun_Date%\SQLBatch%BatchRun_Number%.bat
type %LOG_ISQL_WHOLE_PATH% >>%LOG_WHOLE_PATH%
echo End of %BatchRun_Path%\%BatchRun_Date%\SQLBatch%BatchRun_Number%.bat >>%LOG_WHOLE_PATH%

if not [%errorlevel%] == [0] goto ERROR_HANDLING_SQL_ERROR

echo ------------------------------------------------------------- >>%LOG_WHOLE_PATH%

echo -- %BatchRun_Path%\%BatchRun_Date%\SQLBatch%BatchRun_Number%.bat successed >>%LOG_WHOLE_PATH%

echo ------------------------------------------------------------- >>%LOG_WHOLE_PATH%

rem ##################################################

rem ## 11) Add time stamp to SQL Batch to prevent from rerun

rem ##################################################

move "%BatchRun_Path%\%BatchRun_Date%\SQLBatch%BatchRun_Number%.bat" "%BatchRun_Path%\%BatchRun_Date%\SQLBatch%BatchRun_Number%_%a%%b%.bat" >>%LOG_WHOLE_PATH%

...

出错处理

...

BatchXXXXX.bat(每次发布时由开发部门准备)

isql -E -Phptscsp -i \\路径\SQL.txt -o BatchSQL.log

SQL.txt(每次发布时由开发部门准备)

Update table1 set a=b

首先，每次需要发布系统更新的时候由开发部门准备好BatchXXXXX.bat和SQL.txt这两个文件，放在相应的%BatchRun_Path%\%BatchRun_Date%\下面。

通知产品控制部门运行BatchRunMaster.bat开始执行SQL，并检查log看是否有错误。

注：isql可以用osql代替；如果你的windows上装了Sybase，请确定你使用的isql是SQL Server2000或者SQL Server 7.0的isql而非Sybase的isql；对于完全自动化的发布，有必要的话其实可以通过windows schedule job或者SQL Server Agent里面设定schedule job来定时扫描每天的BatchXXXXX.bat来实现。

SQL Server 2000 通过CDONTS发送Email

创建发送 CDONTS 电子邮件的存储过程

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]

@From varchar(100),

@To varchar(100),

@Subject varchar(100),

@Body varchar(4000),

@CC varchar(100) = null

Declare @MailID int

Declare @hr int

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT

EXEC @hr = sp_OASetProperty @MailID, 'From',@From

EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body

EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC

EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @MailID, 'To', @To

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

EXEC @hr = sp_OADestroy @MailID

前提：

· 1. 安装 IIS 并在运行 SQL Server 的计算机上运行它。

· 2. 将您的 SMTP 邮件服务器指定为您的“智能主机”，以便 IIS SMTP 服务自动将发送到本地服务器的任何 SMTP 电子邮件路由到您的 SMTP 邮件服务器上进行传送。

· 3. 在 SQL Server 中创建一个可用来发送电子邮件的存储过程。

详情请参照：http://support.microsoft.com/kb/312839/zh-cn

发送Email的script

osql -S%SQLServer% -d%SQLDatabase% -E -n -b -Q"sp_send_cdosysmail %fromAddress%,%toAddressList%,%ccAddressList%,%Emailsubject%,%Emailbody%"

if %errorlevel% neq 0 (set flag=1) else (set flag=0)

SQL Server 2000 Analysis对象的迁移

在SQL Server 2000的版本，微软提供了对Analysis各个对象级别包括Cube，Dimension等的copy & paste的功能，可以从测试的机器copy某个对象，然后再在analysis manager中paste到产品服务器。但由于我们的特殊原因，开发部门的测试服务器也不想给任何权限给产品控制部门。在OLAP这块上没有权限重叠的区域，可以访问OLAP产品服务器的人不能访问OLAP开发服器，可以访问OLAP开发服务器的不能访问OLAP产品服务器。因此，只能采用另外一种做法，使用微软提供的命令行工具整个restore 测试OLAP服务器的归档.CAB文件到产品OLAP服务器。当然，归档的文件.CAB全权负责。

归档：

["command-path]msmdarch["] /a Server "OLAPDataPath" "DatabaseName" "BackupFileName" ["LogFileName" ["TempDirectory"]]

还原：

["command-path]msmdarch["] switch Server "OLAPDataPath" "BackupFileName" ["LogFileName" ["TempDirectory"]]

存档示例

下列命令可以存档 AnalysisServices 中包含示例 FoodMart 2000 数据库。

"\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a myserver

"\Program Files\Microsoft Analysis Services\Data\" "FoodMart 2000"

"\My archives\server myserver\FoodMart 2000.cab"

还原示例

将以下命令还原 AnalysisServices 中包含示例 FoodMart 2000 数据库。

"\Program Files\Microsoft Analysis Services\Bin\msmdarch" /r myserver