SUMMARY
The following Transact-SQL sample code transfers an entire database. It is
a simplified example of how to use system stored procedures that allow OLE automation objects to be used within a Transact-SQL batch (Sp_OA procedures) and the
SQLOLE.Transfer (or SQLDMO.Transfer) object. For detailed documentation of the procedures and
objects, query in SQL Server Books Online.
Note For Microsoft SQL Server 7.0, Microsoft SQL Server 2000, and Microsoft SQL Server 2005, you must include the SQLDMO.dll file, and change all occurrences of "SQLOLE" to "SQLDMO" in the sample code.
If you are using SQL Server 2005, you must enable the OLE Automation option by using the SQL Server Surface Area Configuration tool.
- The following is an example that uses the SQLOLE.Transfer Object to
transfer an entire database.
** For sample use only **
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
- The following two examples use the Visual Basic 4.0 (VB) environment to
perform a single object transfer and a character mode BCP operation with
a special delimiter.
** Make sure you add appropriate error checking. **
Transfer a Single Table Structure and Data
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"
BCP Operation
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"