INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer ObjectID: Q152801
|
The following Transact-SQL sample code transfers an entire database. It is
a simplified example of how to use the Sp_OA procedures and the
SQLOLE.Transfer object. For detailed documentation of the procedures and
objects, query in SQL Server 6.50 Books Online.
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"
Dim oServer As Object
Dim oBCP As Object
Set oServer = CreateObject("SQLOLE.SQLServer")
Set oBCP = CreateObject("SQLOLE.BulkCopy")
oServer.Connect "MyServer", "sa"
oBCP.DataFileType = SQLOLEDataFile_SpecialDelimitedChar
oBCP.ColumnDelimiter = "~~~"
oBCP.RowDelimiter = Chr(10) + Chr(13)
oBCP.DataFilePath = "c:\temp\scripts\authors.bcp"
oServer.Databases("Pubs").Tables("authors").ExportData oBCP
MsgBox "Done" Additional query words: SQL-DMO SQL Database Management Objects
Keywords : kbnetwork SSrvTran_SQL
Version : 6.5
Platform : WINDOWS
Issue type : Last Reviewed: March 27, 1999