Sample Script

This is an example of a Transact-SQL statement batch that uses the OLE Automation stored procedures to create and use a 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)
  
-- Create an object
EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
-- Set a property
EXEC @hr = sp_OASetProperty @object, 'HostName', 'Gizmo'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
-- Get a property using an output parameter
EXEC @hr = sp_OAGetProperty @object, 'HostName', @property OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @property
  
-- Get a property using a results set
EXEC @hr = sp_OAGetProperty @object, 'HostName'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
-- Get a property by calling the method
EXEC @hr = sp_OAMethod @object, 'HostName', @property OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @property
  
-- Call a method
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server', 'my_login', 'my_password'
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
-- Call a method that returns a value
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
PRINT @return
  
-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END