konhon

-- Transfer對象的重要屬性

-- 1. 屬性

屬性名                            類型                描述
--------------------------------- ------------------- --------------------
CopyAllDefaults                Boolean    所有默認(rèn)值
CopyAllObjects                 Boolean    所有對象
CopyAllRules                   Boolean    所有規(guī)則
CopyAllStoredProcedures        Boolean    所有存儲過程
CopyAllTables                  Boolean    所有表
CopyAllTriggers                Boolean    所有觸發(fā)器
CopyAllUserDefinedDatatypes    Boolean    所有用戶自定義類型
CopyAllViews                   Boolean    所有視圖
CopyData                       Boolean    所有數(shù)據(jù)
DestDatabase                   String     目標(biāo)對象數(shù)據(jù)庫
DestLogin                      String     目標(biāo)數(shù)據(jù)庫登陸用戶名
DestPassword                   String     目標(biāo)數(shù)據(jù)庫登陸密碼
DestServer                     String     目標(biāo)服務(wù)器
DestUseTrustedConnection       Boolean    用戶信任連接
DropDestObjectsFirst           Boolean    是否先刪除目標(biāo)對象
IncludeDependencies            Boolean    是否包含依靠對象
ScriptType                     Boolean    腳本類型

-- 2. 重要方法: 

方法名稱                    功能描述
--------------------------- --------------------------
AddObject                   增加對象
AddObjectByName             通過對象名稱增加對象

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_CopyDB]
GO

/*-- 在 SQLServer 中使用SQLDMO.Transfer 實現(xiàn)數(shù)據(jù)遷移
    存儲過程實現(xiàn)源數(shù)據(jù)庫到目標(biāo)數(shù)據(jù)庫的對象和數(shù)據(jù)的復(fù)制
    要求源數(shù)據(jù)庫和目標(biāo)數(shù)據(jù)庫在同一服務(wù)器
    如果是要實現(xiàn)不同服務(wù)器之間的復(fù)制，則需要增加驗證信息
--鄒建 2005.07(引用請保留此信息)--*/

/*--調(diào)用示例

    CREATE DATABASE test
    EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
    DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB     
@Des_DB      sysname,           --目標(biāo)數(shù)據(jù)庫
@Obj_Type    nvarchar(4000)=N'',--復(fù)制的對象類型，可以是下列字符串列表：
                                -- O 所有對象，D 默認(rèn)值，R 規(guī)則，P 存儲過程
                                -- T 表，TR 觸發(fā)器，DT 用戶定義數(shù)據(jù)類型
                                -- V 視圖，DATA 數(shù)據(jù)，DEL 刪除目標(biāo)對象
@Source_DB   sysname=N'',       --源數(shù)據(jù)庫
@ServerName  sysname=N'',       --服務(wù)器名
@UserName    sysname=N'',       --用戶名，不指定則表示使用 Windows 身份登錄
@pwd         sysname=N''        --密碼 
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
    @err int,@src varchar(255), @desc varchar(255)

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME()

--創(chuàng)建sqldmo對象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--連接服務(wù)器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登錄
BEGIN
    EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
    IF @err<>0 GOTO lb_Err

    EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
    EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--獲取數(shù)據(jù)庫集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err

--選擇源數(shù)據(jù)庫    
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err

--選擇目標(biāo)數(shù)據(jù)庫    
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err

--設(shè)置復(fù)制的對象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err

--設(shè)置目標(biāo)服務(wù)器信息
EXEC @err=sp_oasetproperty  @TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err

  --設(shè)置連接用戶
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登錄
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
    IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
    IF @err<>0 GOTO lb_Err

    EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
    IF @err<>0 GOTO lb_Err
END

  --設(shè)置復(fù)制對象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err

DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
    SELECT KeyWord=N',D,',   Name=N'CopyAllDefaults' UNION ALL
    SELECT KeyWord=N',O,',   Name=N'CopyAllObjects' UNION ALL
    SELECT KeyWord=N',R,',   Name=N'CopyAllRules' UNION ALL
    SELECT KeyWord=N',P,',   Name=N'CopyAllStoredProcedures' UNION ALL
    SELECT KeyWord=N',T,',   Name=N'CopyAllTables' UNION ALL
    SELECT KeyWord=N',TR,',  Name=N'CopyAllTriggers' UNION ALL
    SELECT KeyWord=N',DT,',  Name=N'CopyAllUserDefinedDatatypes' UNION ALL
    SELECT KeyWord=N',V,',   Name=N'CopyAllViews' UNION ALL
    SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
    SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
        CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
    EXEC @err=sp_oasetproperty @TransferID,@src,1
    IF @err<>0 GOTO lb_Err
    FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb

--復(fù)制對象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err

--結(jié)束
SET @err=0
GOTO lb_Exit

--錯誤處理
lb_Err:
    EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT 
    RAISERROR(N'錯誤編號 %#x, 錯誤源 "%s", 錯誤描述 "%s"',16,1,@err,@src,@desc)
    RETURN -1

lb_Exit:
    EXEC sp_OADestroy @Dbid  
    EXEC sp_OADestroy @srvid 
    EXEC sp_OADestroy @TransferID 
    RETURN @err
GO