Microsoft KB Archive/152801

= Examples of how to use Sp_OA pocedures and the SQLOLE.Transfer (or SQLDMO.Transfer) object =

Article ID: 152801

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q152801



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"

Additional query words: SQL-DMO SQL Database Management Objects ole automation

Keywords: kbnetwork KB152801

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.