-- Important attributes of a transfer object -- 1. Attributes Attribute name type description
------------------------------------------------------------------------
Copyalldefaults Boolean all default values
Copyallobjects Boolean all objects
All copyallrules Boolean rules
Copyallstoredprocedures Boolean all stored procedures
Copyalltables Boolean all tables
Copyalltriggers Boolean all triggers
Copyalluserdefineddatatypes Boolean all user-defined types
Copyallviews Boolean all views
Copydata Boolean all data
Destdatabase string target object database
Destlogin string target database login Username
Destpassword string logon password of the target database
Destserver string target server
Destusetrustedconnection Boolean user-trusted connection
Dropdestobjectsfirst Boolean whether to delete the target object first
Whether includedependencies Boolean contains dependent objects
Scripttype Boolean Script Type -- 2. Important methods: method name function description
--------------------------- ------------------------
addobject adds an object
addobjectbyname adds an object by Object Name 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 /* -- Use sqldmo. Transfer in sqlserver to migrate data
The stored procedure replicates objects and data from the source database to the target database.
The source database and target database must be on the same server
If you want to implement replication between different servers, you need to add verification information
-- Producer build 2005.07 (reference please keep this information )--*/ /* -- Call example Create Database Test
Exec p_copydb @ source_db = 'northwind ', @ des_db = 'test'
Drop database Test
--*/
Create procedure p_copydb
@ Des_db sysname, -- target database
@ Obj_type nvarchar (4000) = n'', -- Copy object type, which can be a list of the following strings:
-- O all objects, D default value, R rule, P Stored Procedure
-- T table, TR trigger, DT user-defined data type
-- V view, Data, del Delete target object
@ Source_db sysname = n'', -- source database
@ Servername sysname = n'', -- server name
@ Username sysname = n'', -- user name. If this parameter is not specified, it indicates logging on as a Windows user.
@ PWD sysname = n'' -- Password
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 () -- Create an sqldmo object ·
Exec @ err = sp_oacreate 'sqldmo. sqlserver ', @ srvid out
If @ err <> 0 goto lb_err -- Connect to the server
If isnull (@ username, n'') = n'' -- Log On As a Windows User
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 -- Obtain the database set
Exec @ err = sp_oagetproperty @ srvid, 'databases', @ dbid out
If @ err <> 0 goto lb_err -- Select source database
Exec @ err = sp_oamethod @ dbid, 'item', @ s_dbid out, @ source_db
If @ err <> 0 goto lb_err -- Select the target database
Exec @ err = sp_oamethod @ dbid, 'item', @ d_dbid out, @ des_db
If @ err <> 0 goto lb_err -- Set the Copied object
Exec @ err = sp_oacreate 'sqldmo. Transfer ', @ transferid out
If @ err <> 0 goto lb_err -- Set the target server information
Exec @ err = sp_oasetproperty @ transferid, 'destserver', @ servername
If @ err <> 0 goto lb_err -- Set the connection user
If isnull (@ username, n'') = n'' -- Log On As a Windows User
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 -- Set the information of the Copied object
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' copyalluserdefinedpes ypes' 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 -- Copy an object
Exec @ err = sp_oamethod @ s_dbid, 'transfer', null, @ transferid
If @ err <> 0 goto lb_err -- End
Set @ err = 0
Goto lb_exit -- Handle errors
Lb_err:
Exec sp_oageterrorinfo null, @ SRC out, @ DESC out
Raiserror (n' error Number % # X, error source "% s", error description "% 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 |
