New Information - SQL Server 2000 SP3.

This is an example of a Transact-SQL statement batch that uses the OLE Automation stored procedures to create and use an SQL-DMO SQLServer object. Portions of the code are used as examples in the stored procedure references.

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)

-- Create a SQLServer object.
SET NOCOUNT ON

-- First, create the object.
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
   -- Report the error.
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
   GOTO END_ROUTINE
ELSE
   -- An object is successfully created.
   BEGIN
      -- Set a property.
      EXEC @hr = sp_OASetProperty @object, 'HostName', 'Gizmo'
      IF @hr <> 0 GOTO CLEANUP
      
      -- Get a property using an output parameter.
      EXEC @hr = sp_OAGetProperty @object, 'HostName', @property OUT
      IF @hr <> 0 
         GOTO CLEANUP
      ELSE
         PRINT @property
      
      -- Get a property using a result set.
      EXEC @hr = sp_OAGetProperty @object, 'HostName'
      IF @hr <> 0 GOTO CLEANUP

      -- Get a property by calling the method.
      EXEC @hr = sp_OAMethod @object, 'HostName', @property OUT
      IF @hr <> 0 
         GOTO CLEANUP
      ELSE
         PRINT @property

      -- Call a method.
      -- SECURITY NOTE - When possible, use Windows Authentication.
      EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server', 'my_login', 'my_password'
      IF @hr <> 0 GOTO CLEANUP
      
      -- Call a method that returns a value.
      EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
      IF @hr <> 0
         GOTO CLEANUP
      ELSE
         PRINT @return
   END

CLEANUP:
   -- Check whether an error occurred.
   IF @hr <> 0
   BEGIN
      -- Report the error.
      EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
      SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
   END

   -- Destroy the object.
   BEGIN
      EXEC @hr = sp_OADestroy @object
      -- Check if an error occurred.
      IF @hr <> 0 
      BEGIN
         -- Report the error.
         EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
         SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
      END
   END

END_ROUTINE:
RETURN

See Also