Developing Managed Code and Executing Within Microsoft SQL Server 2000 - How to call .NET component from within SQL Server 2000
(Page 4 of 5 )
After completing all of the above steps, open up your query analyzer and type the following Transact-SQL code, to call the component:
DECLARE @ExecutionResult INT, @errorSource VARCHAR(100), @errorDescription VARCHAR(100), @result VARCHAR(200),@hnd INT
EXEC @ExecutionResult = sp_OACreate 'MySample.CSample', @hnd OUTPUT
EXEC @ExecutionResult = sp_OAMethod @hnd, 'getSum', @result OUTPUT, 10, 20
EXEC @ExecutionResult = sp_OADestroy @hnd
SELECT @result
Just press F5 to execute the script and it should give you a result with a value of 30. You can modify the above code with better error handling as follows:
DECLARE @ExecutionResult INT, @errorSource VARCHAR(100), @errorDescription VARCHAR(100), @result VARCHAR(200),@hnd INT
EXEC @ExecutionResult = sp_OACreate 'MySample.CSample', @hnd OUTPUT
IF (@ExecutionResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT @errorSource, @errorDescription
END
EXEC @ExecutionResult = sp_OAMethod @hnd, 'getSum', @result OUTPUT, 10, 20
IF (@ExecutionResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT @errorSource, @errorDescription
END
EXEC @ExecutionResult = sp_OADestroy @hnd
IF (@ExecutionResult <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @hnd, @errorSource OUTPUT, @errorDescription OUTPUT
SELECT @errorSource, @errorDescription
END
SELECT @result
Next: Understanding the concept >>
More MS SQL Server Articles
More By Jagadish Chaterjee
