June 8, 2006 at 1:41 pm
Error trying to execute a stored procedure from SSIS Execute SQL Task:
"Exception from HRESULT: 0xC0015005".
So far, googling, SQLTeam, SQLServerCentral, SQL-Server-Performance and BOL searches have turned up .... 0.
What I have is the following SP:
----sp code---
Use
DataStore
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'usp_EXAMPLE_ProductAvailability_perTPNB'
)
DROP PROCEDURE dbo.usp_EXAMPLE_ProductAvailability_perTPNB
GO
CREATE PROCEDURE dbo.usp_EXAMPLE_ProductAvailability_perTPNB
@TPNB int, @TPNBAvailable varchar(11) OUTPUT
AS
--Created By: Regan
--Created On: 2006-05-25
--Version: 0.1 - initial creation
--SQL Version: 2005 SP0. This should be compatiable back to at least 7.0 since it is only a table create,
-- but since it is for an SSIS package, that is not advisable
--Overview:
--This script is a very basic 1st stab at a SP retrieving a value, based on a TPNB found.
--ToDo:
-- flesh this out into a properly thought-out, detailed SP.
--Sample Usage:
--DECLARE
--@TPNBint,
--@Availablevarchar(11),
--@RetrunCodeint
--
--EXEC @ReturnCode = usp_EXAMPLE_ProductAvailability_perTPNB @TPNB=@TPNB, @TPNBAvailable=@Available OUTPUT
--IF @ReturnCode <> 0
--PRINT 'An Error occurred : @ReturnCode = ['+str(@ReturnCode)+']'
--ELSE
--PRINT 'SUCCESS : @Available = ['+(@Available)+']'
DECLARE
@ErrorDescriptionNVARCHAR(250),
@ErrorINT,
@RowCountINT
--Check Pre-Contract - we must receive a TPNB number
IF (@TPNB = 0) or (@TPNB is null)
BEGIN
SET @ErrorDescription = '### ERROR - Pre-CONTRACT FAILURE : Stored Procedure [usp_EXAMPLE_ProductAvailability_perTPNB] received an invalid input parametre ['+isnull(cast(@TPNB as varchar(16)),'NULL')+'] and is FAILING - ERROR ###'
SET @Error = 1 --Custom value for Contract Failure
RAISERROR (@ErrorDescription,10,1,@Error)
RETURN @Error
END
--Contract Held - get availability
SELECT @TPNBAvailable =
CASE
WHEN NetQuantityAvailable <= 0 THEN 'Unavailable'
WHEN NetQuantityAvailable > 0 THEN 'Available'
END
FROM dbo.EXAMPLE_vwTPNBQuantity
WHERE TPNB = @TPNB
--ErrorChecking
SELECT @Error = @@Error, @RowCount = @@RowCount
IF @Error <> 0
BEGIN
SET @ErrorDescription = '### ERROR - SQL FAILURE : Stored Procedure [usp_EXAMPLE_ProductAvailability_perTPNB] failed with @@Error of ['+cast(@Error as varchar(16))+'] and is FAILING - ERROR ###'
RAISERROR (@ErrorDescription,10,1,@Error)
RETURN @Error
END ELSE
IF @RowCount <> 1
BEGIN
SET @ErrorDescription = '### ERROR - TPNB VALUE FAILURE : Stored Procedure [usp_EXAMPLE_ProductAvailability_perTPNB] Returned : ['+cast(@RowCount as varchar(16))+'] rows using input TPNB of ['+cast(@TPNB as varchar(16))+'] and is FAILING - ERROR ###'
SET @Error = 3 --Custom value for TPNB Failure
RAISERROR (@ErrorDescription,10,1,@Error)
RETURN @Error
END
--Post contract check of @TPNBAvailable value necessary?
RETURN @Error
GO
----/code--
my Execute SQL task command is:
EXEC ?= dbo.usp_EXAMPLE_ProductAvailability_perTPNB ?, ? OUTPUT
the full error message is:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "varTPNBAvailable": "Exception from HRESULT: 0xC0015005".
Any thoughts
June 9, 2006 at 3:12 am
I found my problem - I had specified that the SP returns RESULTSET of SINGLE ROW - whereas I needed to say that RESULTSET was NONE.
The following page gave me the answer, and should be bookmarked (imho) for all intrepid SSIS developers:
http://msdn2.microsoft.com/en-us/library/ms345164.aspx
Cheers
July 25, 2011 at 9:03 am
Cheers to you Wanderer! I thought that getting a return code from a stored procedure call in SSIS shouldn't be a complicating task, but I spent an embarrassing amount of time trying to figure this out! I will indeed bookmark that link!
August 3, 2011 at 6:43 am
no problem - glad it helped!
lol - just realized I'm registeed with 2 different email addresses here. Wonder if there is a merge function
October 21, 2011 at 6:20 pm
It's been a head banger of a day, but this post gave me all the clues I needed! Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply