none
Stored Procedure Resultset Issue in SSIS RRS feed

  • Question

  • Hello,

    We are upgrading our SQL Server database from 2008 to 2016 and in the process of upgrading, we are also upgrading the SSIS packages. However, there are couple of SSIS jobs that use the update query are failing with the below error message.

    'Executing the query "usp_Accts_SFDCAccountId"  failed with the following error. Unable to populate result columns for single row data type. The query returned an empty result set. Possible failure reasons. Problems with the query, Result set not set correctly, parameters not set correctly, or connection not established correctly'.

    The "Execute SQL Task" in the SSIS package has not changed, the SSIS package was upgraded using Visual Studio 2017. The Resultset is set to "Single Row" in the Execute SQL task. This is working fine in SQL server 2008 job, but failing with the above error message with SQL Server 2016.

    Can someone help?

    Tuesday, June 25, 2019 10:00 PM

Answers

  • Hi pnbalaji,

    Please try to add an alias and SET NOCOUNT ON, like below:


    CREATE PROCEDURE [dbo].[usp_Accts_SFDCAccountId]
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
        UPDATE ORG
        SET SFDCAccountId = ACC.Id,
        RowUpdatedTs = GetDate(),
        RowUpdatedBy = REPLACE(USER,'CMUTUAL\','')
        FROM Origin ORG
        
        JOIN Acc_Stage ACC
        ON ACC.OrgNumber = ORG.OrgNr
    
        WHERE (Org.SFDCAccountId IS NULL OR ORG.SFDCAccountID <> ACC.Id);
        
        SELECT @@ROWCOUNT  AS Row_Count;
        
    END



    Wednesday, June 26, 2019 4:11 PM

All replies

  • Hi pnbalaji,

    Unable to populate result columns for single row data type. The query returned an empty result set.

    Please execute the query "usp_Accts_SFDCAccountId" in SQL Server 2016 to check if it will show results.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 26, 2019 2:31 AM
  • Hi Mona,

    When I manually run the stored procedure, it is updating 5,493 records. The contents of the stored procedure is below.

    =======================================================================

    USE [Demo]
    GO

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [dbo].[usp_Accts_SFDCAccountId]

    AS

    BEGIN

        UPDATE ORG
        SET SFDCAccountId = ACC.Id,
        RowUpdatedTs = GetDate(),
        RowUpdatedBy = REPLACE(USER,'CMUTUAL\','')
        FROM Origin ORG
        
        JOIN Acc_Stage ACC
        ON ACC.OrgNumber = ORG.OrgNr

        WHERE (Org.SFDCAccountId IS NULL OR ORG.SFDCAccountID <> ACC.Id)    
        
        SELECT @@ROWCOUNT    
        
    END
    GO
    =======================================================================

    Irrespective whether the update happens to many records or 0 records, I am facing this result set issue.


    Wednesday, June 26, 2019 2:20 PM
  • Try removing the

    SELECT @@ROWCOUNT    

    part



    Arthur

    MyBlog


    Twitter

    Wednesday, June 26, 2019 2:35 PM
    Moderator
  • Tried removing the SELECT @@ROWCOUNT and the job failed with the same error again.
    Wednesday, June 26, 2019 3:20 PM
  • Hi pnbalaji,

    Please try to add an alias and SET NOCOUNT ON, like below:


    CREATE PROCEDURE [dbo].[usp_Accts_SFDCAccountId]
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
        UPDATE ORG
        SET SFDCAccountId = ACC.Id,
        RowUpdatedTs = GetDate(),
        RowUpdatedBy = REPLACE(USER,'CMUTUAL\','')
        FROM Origin ORG
        
        JOIN Acc_Stage ACC
        ON ACC.OrgNumber = ORG.OrgNr
    
        WHERE (Org.SFDCAccountId IS NULL OR ORG.SFDCAccountID <> ACC.Id);
        
        SELECT @@ROWCOUNT  AS Row_Count;
        
    END



    Wednesday, June 26, 2019 4:11 PM
  • Hi,

    This solution is working fine. Thanks for your help.

    Wednesday, June 26, 2019 7:27 PM