INF: Examples of Sp_OA Procedure Use and SQLOLE.Transfer Object

• Microsoft SQL Server, version 6.5

SUMMARY

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.

• 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

-- 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,

      if @hr <> 0
      begin
         exec sp_OAGetErrorInfo @oServer
         exec sp_OADestroy @oServer
      end

   end
   else
      EXEC sp_OAGetErrorInfo @oServer


   return @hr

-- 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',

               if @hr = 0
               begin

                  if @bDebug = 1
                     print 'Setting DestServer'

                  exec @hr = sp_OASetProperty @oTransfer, 'DestServer',

                  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

-- 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

   --
   -- 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,

   if @hr = 0
   begin


      --
      -- Create a transfer object and fill in the details
      --
      exec @hr = sp_OA_TransferObject @bDebug, @oTransfer OUT, @strDestDB,

      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,

         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

            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

-- 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
'

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

    '
    oTransfer.AddObjectByName "dbo.tblTrans", SQLOLEObj_UserTable

    oServer.Databases("pubs").ScriptTransfer oTransfer, 1,

    oServer.Databases("pubs").Transfer oTransfer

    MsgBox "VerifyConnection failed"

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"