You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q152801
SUMMARY
The following Transact-SQL sample code transfers an entire database. It isa 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 theSQLOLE.Transfer (or SQLDMO.Transfer) object. For detailed documentation of the procedures andobjects, 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 pubsgo---- Disconnect the server objectdrop procedure sp_OA_ServerDisconnectgocreate procedure sp_OA_ServerDisconnect @bDebug bit, @oServer intas   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 @hrgo---- Create a stored procedure to handle the server object and make a logindrop procedure sp_OA_ServerObjectgocreate 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 @hrgo---- Create a stored procedure to create transfer object and fill in propertiesdrop procedure sp_OA_TransferObjectgocreate 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 @hrgo---- Create a stored procedure to drive the transfer of the pubs databasedrop procedure sp_OA_TransferDBgocreate procedure sp_OA_TransferDB @bDebug bit, @strFromDB varchar(30),@strFromServer varchar(30), @strFromUser varchar(30), @strDestDBvarchar(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   endgo---- Execute a transferset nocount ongoexec 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 ObjectDim 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 oTransferElse    MsgBox "VerifyConnection failed"End IfMsgBox "Done"				

BCP Operation

Dim oServer As ObjectDim oBCP As ObjectSet oServer = CreateObject("SQLOLE.SQLServer")Set oBCP = CreateObject("SQLOLE.BulkCopy")oServer.Connect "MyServer", "sa"oBCP.DataFileType = SQLOLEDataFile_SpecialDelimitedCharoBCP.ColumnDelimiter = "~~~"oBCP.RowDelimiter = Chr(10) + Chr(13)oBCP.DataFilePath = "c:\temp\scripts\authors.bcp"oServer.Databases("Pubs").Tables("authors").ExportData oBCPMsgBox "Done"				
SQL-DMO SQL Database Management Objects ole automation
Properties

Article ID: 152801 - Last Review: 02/22/2007 04:26:22 - Revision: 4.3

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

  • kbnetwork KB152801
Feedback
">amp;did=1&t=">tml>