DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @return_int int
DECLARE @objResults int
DECLARE @col int, @row int, @num_cols int, @num_rows int

-- Create an object.
-- For SQL Server 6.0/6.5, change SQLDMO.SQLServer to SQLOLE.SQLServer
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
-- Call a method.
EXEC @hr = sp_OAMethod @object, 'Connect', NULL, '(local)'
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
EXEC @hr = sp_OAMethod @object, 'ExecuteWithResults("select * From pubs..authors")', @objResults OUT

EXEC @hr = sp_OAGetProperty @objResults, 'Columns' , @num_cols OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
EXEC @hr = sp_OAGetProperty @objResults, 'Rows' , @num_rows OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

SELECT @num_cols AS Num_Columns, @num_rows AS Num_Rows
PRINT 'Second Row & Three Columns Alone...'
EXEC @hr = sp_OAMethod @objResults, 'GetRangeString(2, 1, 2, 3)'
/* Set number of characters to return to >255 to see more of result set. */
EXEC @hr = sp_OAMethod @objResults, 'GetRangeString'

EXEC sp_OADestroy @object

This page was last updated on May 01, 2006 04:28 PM.