下列的 Transact-SQL 範例程式碼將傳輸整個資料庫。它是一個簡化的範例，說明如何使用系統預存程序，可讓 T-SQL 批次 (Sp_OA 程序) 內使用 OLE 自動化物件和 SQLOLE.Transfer (或 SQLDMO.Transfer) 的物件。對於詳細的文件的程序和物件，在 SQL Server 線上叢書 》 中查詢。
附註Microsoft SQL Server 7.0、 Microsoft SQL Server 2000 和 Microsoft SQL Server 2005，您必須併入 SQLDMO.dll] 檔案，並將 SQLOLE"的所有項目變更為 SQLDMO"，範例程式碼中。 如果您使用的 SQL Server 2005，您必須使用 SQL Server 介面區組態工具啟用 OLE 自動化選項。
下列是使用 SQLOLE.Transfer 物件傳送整個資料庫的範例。
貴為範例使用只貴
use pubs
go
--
-- Disconnect the server object
drop procedure sp_OA_ServerDisconnect
go
create procedure sp_OA_ServerDisconnect @bDebug bit, @oServer int
as
if @bDebug = 1
print 'sp_OA_ServerDisconnect starting...'
DECLARE @hr int
exec @hr = sp_OAMethod @oServer, 'Disconnect'
if @hr <> 0
EXEC sp_OAGetErrorInfo @oServer
return @hr
go
--
-- Create a stored procedure to handle the server object and make a login
drop procedure sp_OA_ServerObject
go
create procedure sp_OA_ServerObject @bDebug bit, @oServer int output,
@strServer varchar(30), @strUser varchar(30)
as
if @bDebug = 1
print 'sp_OA_ServerObject starting...'
DECLARE @hr int
exec @hr = sp_OACreate 'SQLOLE.SQLServer', @oServer OUT
if @hr = 0
begin
Select 'Attempting to connect to ' + @strServer + ' as ' + @strUser
exec @hr = sp_OAMethod @oServer, 'Connect', NULL, @strServer,
@strUser
if @hr <> 0
begin
exec sp_OAGetErrorInfo @oServer
exec sp_OADestroy @oServer
end
end
else
EXEC sp_OAGetErrorInfo @oServer
return @hr
go
--
-- Create a stored procedure to create transfer object and fill in properties
drop procedure sp_OA_TransferObject
go
create procedure sp_OA_TransferObject @bDebug bit, @oTransfer int OUT,
@strDestDB varchar(30), @strToServer varchar(30), @strToUser varchar(30)
as
if @bDebug = 1
print 'sp_OA_TransferObject starting...'
DECLARE @hr int
DECLARE @oLogin int
exec @hr = sp_OACreate 'SQLOLE.Transfer', @oTransfer OUT
if @hr = 0
begin
print 'Setting transfer properties...'
exec @hr = sp_OASetProperty @oTransfer, 'CopyAllObjects', 1
if @hr = 0
begin
exec @hr = sp_OASetProperty @oTransfer, 'CopyData', 1
if @hr = 0
begin
exec @hr = sp_OASetProperty @oTransfer, 'CopySchema', 1
if @hr = 0
begin
exec @hr = sp_OASetProperty @oTransfer, 'DestDatabase', @strDestDB
if @hr = 0
begin
if @bDebug = 1
print 'Setting DestServer'
exec @hr = sp_OASetProperty @oTransfer, 'DestServer', @strToServer
if @hr = 0
begin
exec @hr = sp_OASetProperty @oTransfer, 'DropDestObjectsFirst', 1
if @hr = 0
begin
exec @hr = sp_OASetProperty @oTransfer, 'DestLogin', @strToUser
if @hr <> 0
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
begin
exec sp_OAGetErrorInfo @oTransfer
exec sp_OADestroy @oTransfer
end
end
else
exec sp_OAGetErrorInfo @oTransfer
return @hr
go
--
-- Create a stored procedure to drive the transfer of the pubs database
drop procedure sp_OA_TransferDB
go
create procedure sp_OA_TransferDB @bDebug bit, @strFromDB varchar(30),
@strFromServer varchar(30), @strFromUser varchar(30), @strDestDB
varchar(30), @strToServer varchar(30), @strToUser varchar(30),
@strScriptsDir varchar(255)
as
if @bDebug = 1
print 'sp_OA_TransferDB starting...'
select 'Preparing to transfer from ' + @strFromServer + '.' + @strFromDB
+ ' to ' + @strToServer + '.' + @strDestDB
--
-- Variable declarations
--
DECLARE @oServer int
DECLARE @oTransfer int
DECLARE @hr int
DECLARE @strResult varchar(255)
DECLARE @strCommand varchar(255)
--
-- Create the server object and get logged on
--
exec @hr = sp_OA_ServerObject @bDebug, @oServer OUT, @strFromServer, @strFromUser
if @hr = 0
begin
--
-- Create a transfer object and fill in the details
--
exec @hr = sp_OA_TransferObject @bDebug, @oTransfer OUT, @strDestDB,
@strToServer, @strToUser
if @hr = 0
begin
print "Scripting the transfer..."
--
-- Script the transfer
--
-- SQLOLEXfrFile_SummaryFiles = 0x0001
--
select @strCommand = 'Databases("' + @strFromDB + '").ScriptTransfer'
if @bDebug = 1
select @strCommand
exec @hr = sp_OAMethod @oServer, @strCommand, @strResult OUT,
@oTransfer, 1, @strScriptsDir
if @hr = 0
begin
if @bDebug = 1
select 'Result' = @strResult
print "Performing the transfer..."
select @strCommand = 'Databases("' + @strFromDB + '").Transfer'
if @bDebug = 1
select @strCommand
exec @hr = sp_OAMethod @oServer, @strCommand, NULL, @oTransfer
if @hr <> 0
begin
exec sp_OAGetErrorInfo @oServer
print '***The ::Transfer method failed. Check your script
directory (.log) files for more details.'
end
begin
print 'Transfer complete successfully!!!'
end
end
else
begin
exec sp_OAGetErrorInfo @oServer
end
--
-- Clean up the transfer object
--
exec sp_OADestroy @oTransfer
end
--
-- Clean up the server object
--
exec sp_OA_ServerDisconnect @bDebug, @oServer
exec sp_OADestroy @oServer
end
go
--
-- Execute a transfer
set nocount on
go
exec sp_OA_TransferDB 0, "pubs", "MyServer", "sa", "pubs2", "MyServer",
"sa", "c:\temp\scripts"
go
Dim oServer As Object
Dim oTransfer As Object
'
' Create the Server object and connect
'
' To obtain the correct defs for constants you need to
' include the SQLOLE65.TLB. Same is true if you want to
' DIM things as SQLOLE.SQLServer and not as Object
'
Set oServer = CreateObject("SQLOLE.SQLServer")
oServer.Connect "MyServer", "sa"
If oServer.VerifyConnection = True Then
Set oTransfer = CreateObject("SQLOLE.Transfer")
oTransfer.CopyAllDefaults = False
oTransfer.CopyAllObjects = False
oTransfer.CopyAllRules = False
oTransfer.CopyAllStoredProcedures = False
oTransfer.CopyAllTables = False
oTransfer.CopyAllTriggers = False
oTransfer.CopyAllUserDefinedDatatypes = False
oTransfer.CopyAllViews = False
oTransfer.CopyData = SQLOLECopyData_Replace
oTransfer.CopySchema = True
oTransfer.IncludeDependencies = False
oTransfer.IncludeGroups = False
oTransfer.IncludeLogins = False
oTransfer.IncludeUsers = False
oTransfer.DropDestObjectsFirst = True
oTransfer.DestDatabase = "pubs2"
oTransfer.DestServer = "MyServer"
oTransfer.DestLogin = "sa"
'
' Note: That when used AddObjectByName you must qualify the object
' "Owner.Object". If this is not done you the schema (.TAB) file will
' remain empty and the drop file (.DP1, .DP2) will contain a ".Object".
'
oTransfer.AddObjectByName "dbo.tblTrans", SQLOLEObj_UserTable
oServer.Databases("pubs").ScriptTransfer oTransfer, 1, "c:\temp\scripts"
oServer.Databases("pubs").Transfer oTransfer
Else
MsgBox "VerifyConnection failed"
End If
MsgBox "Done"
重要：本文是以 Microsoft 機器翻譯軟體翻譯而成，而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章，讓使用者可以依其使用語言使用知識庫中的所有文章。但是，機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤，就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
機器翻譯
回此頁最上方
