SQL Server Performance

Error 0xC0015005 when exec'ing SP from SSIS

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Jun 8, 2006.

  1. SQL_Guess New Member

    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 @TPNB=@TPNB, @TPNBAvailable=@Available 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
  2. satya Moderator

    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.
  3. SQL_Guess New Member

    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

Share This Page