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

Article translations Article translations
Article ID: 152801 - View products that this article applies to.
This article was previously published under Q152801
Expand all | Collapse all

On This Page

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"
				

Properties

Article ID: 152801 - Last Review: February 22, 2007 - Revision: 4.3
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
Keywords: 
kbnetwork KB152801

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com