Error 0xC0015005 when exec'ing SP from SSIS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Error 0xC0015005 when exec’ing SP from SSIS

rror 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
— @TPNB int,
— @Available varchar(11),
— @RetrunCode int

— EXEC @ReturnCode = usp_EXAMPLE_ProductAvailability_perTPNB @[email protected], @[email protected] OUTPUT
— IF @ReturnCode <> 0
— PRINT ‘An Error occurred : @ReturnCode = [‘+str(@ReturnCode)+’]’
— ELSE
— PRINT ‘SUCCESS : @Available = [‘+(@Available)+’]’ DECLARE
@ErrorDescription NVARCHAR(250),
@Error INT,
@RowCount INT –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 Panic, Chaos, Disorder … my work here is done –unknown
That exception refers to No result rowset is associated with the execution of this query. The result is not correctly specified. The result is not correctly specified. So probably check the variables assignment within the loop. HTH Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
Thanks Satya – just found that as well: 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 Panic, Chaos, Disorder … my work here is done –unknown
]]>