OLE 자동화 예제 스크립트OLE Automation Sample Script

이 항목에는 OLE 자동화 저장 프로시저를 사용하여 Transact-SQLTransact-SQL 의 로컬 인스턴스에서 SQL-DMO SQLServer 개체를 만들고 사용하는 데이터베이스 엔진Database 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 데이터베이스 엔진Database Engine. 코드의 일부는 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  

Transact-SQL의 OLE 자동화 개체OLE 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)