Exemplos de como usar Sp_OA pocedures e o objeto SQLOLE.Transfer (ou SQLDMO.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"
				

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"