OLE Automation Sample Script
Applies to:
SQL Server
This article contains an example of a Transact-SQL statement batch that uses the OLE Automation stored procedures to create and use a SQL-DMO SQLServer object in the local instance of the Database Engine. Parts of the code are used as examples in the reference articles for the OLE Automation system stored procedures.
USE AdventureWorks2022;
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
Next steps
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om