Script di automazione OLE di esempioOLE Automation Sample Script

Questo argomento include un esempio di un batch di istruzioni Transact-SQLTransact-SQL che usa le stored procedure dell'automazione OLE per creare e utilizzare un oggetto SQL-DMO di SQLServer nell'istanza locale di Motore di databaseDatabase Engine.This topic contains an example of a Transact-SQLTransact-SQL statement batch that uses the OLE Automation stored procedures to create and use an SQL-DMO SQLServer object in the local instance of the Motore di databaseDatabase Engine. Parti del codice vengono utilizzate come esempi negli argomenti di riferimento per le stored procedure di sistema dell'automazione OLE.Parts of the code are used as examples in the reference topics for the OLE Automation system stored procedures.

USE AdventureWorks2012;  
GO  
DECLARE @Object int;  
DECLARE @HR int;  
DECLARE @Property nvarchar(255);  
DECLARE @Return nvarchar(255);  
DECLARE @Source nvarchar(255), @Desc nvarchar(255);  

-- Create a SQLServer object.  
SET NOCOUNT ON;  

-- First, create the object.  
EXEC @HR = sp_OACreate N'SQLDMO.SQLServer',  
    @Object OUT;  
IF @HR <> 0  
BEGIN  
    -- Report the error.  
    EXEC sp_OAGetErrorInfo @Object,  
        @Source OUT,  
        @Desc OUT;  
    SELECT HR = convert(varbinary(4),@HR),  
        Source=@Source,  
        Description=@Desc;  
    GOTO END_ROUTINE  
END  
ELSE  
-- A DMO.SQLServer object has been successfully created.  
BEGIN  
    -- Specify Windows Authentication for connections.  
    EXEC @HR = sp_OASetProperty @Object,  
        N'LoginSecure',  
        N'TRUE';  
    IF @HR <> 0 GOTO CLEANUP  

    -- Set a property.  
    EXEC @HR = sp_OASetProperty @Object,  
        N'HostName',  
        N'SampleScript';  
    IF @HR <> 0 GOTO CLEANUP  

    -- Get a property using an output parameter.  
    EXEC @HR = sp_OAGetProperty @Object, N'HostName', @Property OUT;  
    IF @HR <> 0   
        GOTO CLEANUP  
    ELSE  
        PRINT @Property;  

    -- Get a property using a result set.  
    EXEC @HR = sp_OAGetProperty @Object,  
        N'HostName';  
    IF @HR <> 0 GOTO CLEANUP  

    -- Get a property by calling the method.  
    EXEC @HR = sp_OAMethod @Object,  
        N'HostName',  
        @Property OUT;  
    IF @HR <> 0   
        GOTO CLEANUP  
    ELSE  
        PRINT @Property;  

    -- Call the connect method.  
    -- SECURITY NOTE - When possible, use Windows Authentication.  
    EXEC @HR = sp_OAMethod @Object,  
        N'Connect',  
        NULL,  
        N'localhost',  
        NULL,  
        NULL;  
    IF @HR <> 0 GOTO CLEANUP  

    -- Call a method that returns a value.  
    EXEC @HR = sp_OAMethod @Object,  
        N'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,  
        @Source OUT,  
        @Desc OUT;  
    SELECT HR = convert(varbinary(4),@HR),  
        Source=@Source,  
        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,  
        @Source OUT,  
        @Desc OUT;  
        SELECT HR = convert(varbinary(4),@HR),  
        Source=@Source,  
        Description=@Desc;  
    END  
END  

END_ROUTINE:  
RETURN;  
GO  

Oggetti di automazione OLE in Transact-SQLOLE Automation Objects in Transact-SQL

sp_OACreate (Transact-SQL)sp_OACreate (Transact-SQL)

sp_OAGetProperty (Transact-SQL)sp_OAGetProperty (Transact-SQL)

sp_OASetProperty (Transact-SQL)sp_OASetProperty (Transact-SQL)

sp_OAMethod (Transact-SQL)sp_OAMethod (Transact-SQL)

sp_OADestroy (Transact-SQL)sp_OADestroy (Transact-SQL)