--**************************************
-- Name: ExecuteWithResults & GetRangeString methods
-- Description:How to use ExecuteWithResults & GetRangeString methods
-- By: Umachandar
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=224&lngWId=5--for details.--**************************************
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
|
