Error while Executing stored procedure with Dynamic Query in SQL Server Agent Jobs

Question

I'm getting below error while executing a stored procedure in SSIS. Want to know what will be possible reasons for below error. I have dynamic query in stored procedure:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXECUTE sp_executesql @SQLString' in procedure 'ABC_test' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Below is my stored procedure. It is executing in SSIS but while scheduling this package in SQL Server Agent Job its throwing the above error

Create PROCEDURE [dbo].[ABC_test]
    @ProgramVersionName     NVARCHAR(255) = NULL,
    @FiscalYear             INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQLString          NVARCHAR(MAX)
    DECLARE @SQLWhereString     NVARCHAR(MAX)

    SET @ProgramVersionName = LTRIM(@ProgramVersionName)
    SET @ProgramVersionName = RTRIM(@ProgramVersionName)
    SET @FiscalYear = LTRIM(@FiscalYear)
    SET @FiscalYear = RTRIM(@FiscalYear)

    /********** Parameter conditions check ***********************/
    IF (@ProgramVersionName IS NOT NULL AND @ProgramVersionName <> '') AND (@FiscalYear IS NULL OR @FiscalYear = 0)
    BEGIN
        SET @SQLWhereString = ' WHERE PV.[Name] = ''' + @ProgramVersionName + ''''
    END
    ELSE IF (@ProgramVersionName IS NULL OR @ProgramVersionName = '') AND (@FiscalYear IS NOT NULL AND @FiscalYear <> 0)
    BEGIN
        SET @SQLWhereString = ' WHERE DATEPART(YYYY, D.Date) = ' + CONVERT(NVARCHAR(10), @FiscalYear)
    END
    ELSE IF (@ProgramVersionName IS NOT NULL AND @ProgramVersionName <> '') AND (@FiscalYear IS NOT NULL AND @FiscalYear <> 0)
    BEGIN
        SET @SQLWhereString = ' WHERE PV.[Name] = ''' + @ProgramVersionName + ''' AND DATEPART(YYYY, D.Date) = ' + CONVERT(NVARCHAR(10), @FiscalYear)
    END
    /********** End ***********************/

    SET @SQLString = '  SELECT  CASE WHEN PV.IsBaseline = 1 AND PV.IsForecast = 0 THEN ''BUDGET''
                                     WHEN PV.IsBaseline = 0 AND PV.IsForecast = 1 THEN ''FORECAST''
                                 END AS BALANCE_TYPE
                                ,CONVERT(VARCHAR(50), PV.[Name]) AS BUDGET_ID
                                ,CONVERT(VARCHAR(255), PV.Description) AS BUDGET_DESCRIPTION
                                ,DATEPART(YYYY, PV.Date) AS FISCAL_YEAR

                        FROM
[dbo].[CPP] PV 
                        '

    SET @SQLString = @SQLString + ISNULL(@SQLWhereString, '')

    SET @SQLString = @SQLString + ' UNION ALL

                                    SELECT  CASE WHEN PV.IsBaseline = 1 AND PV.IsForecast = 0 THEN ''BUDGET''
                                                 WHEN PV.IsBaseline = 0 AND PV.IsForecast = 1 THEN ''FORECAST''
                                            END AS BALANCE_TYPE
                                            ,CONVERT(VARCHAR(50), PV.[Name]) AS BUDGET_ID
                                            ,CONVERT(VARCHAR(255), PV.Description) AS BUDGET_DESCRIPTION
                                            ,DATEPART(YYYY, PV.Date) AS FISCAL_YEAR

                                    FROM
[dbo].[CPP] PV'

    SET @SQLString = @SQLString + ISNULL(@SQLWhereString, '')

    SET @SQLString  = ' SELECT  A.BALANCE_TYPE

                                ,A.BUDGET_ID
                                ,A.BUDGET_DESCRIPTION
                                ,A.FISCAL_YEAR

                        FROM
                        (
                      ' + @SQLString +
                      ' ) A

                     '

    --print @SQLString
    EXECUTE sp_executesql @SQLString
END

Above SP is executing in SSIS but while scheduling this package in SQL Server Agent Job its giving above error. It is giving error for dynamic SQL in SP. Kindly let me know what are the possible solution to achieve the above solution to work in SQL Server Agent Jobs

Possible duplicate of Using dynamic SQL in an OLE DB source in SSIS 2012 — Tab Alleman
– Tab Alleman, Commented Jun 1, 2016 at 13:47
Have you tried delaying the validation for the SQL execute task? — Paul Andrew
– Paul Andrew, Commented Jun 1, 2016 at 13:49
oledb source or execute sql task? It sounds like a source component but that would not work in any case regardless of being an agent job. — Joe C
– Joe C, Commented Jun 1, 2016 at 13:56
@JoeC it is Oledb source. can you explain in more detail as im not getting any clear idea — Nilesh Gajare
– Nilesh Gajare, Commented Jun 1, 2016 at 13:58

Philip Kelley · Accepted Answer · 2016-06-01 14:17:03Z

One way is to remove the dynamic SQL. Analyzing your query as posted, I don't really see the need for it. As near as I can tell, the following would do the same thing:

CREATE PROCEDURE [dbo].[ABC_test]

    @ProgramVersionName     NVARCHAR(255) = NULL,
    @FiscalYear             INT = 0

AS

    SET NOCOUNT ON;

    DECLARE @SQLString          NVARCHAR(MAX)
    DECLARE @SQLWhereString     NVARCHAR(MAX)

    SET @ProgramVersionName = LTRIM(@ProgramVersionName)
    SET @ProgramVersionName = RTRIM(@ProgramVersionName)


    SELECT
       CASE
         WHEN PV.IsBaseline = 1 AND PV.IsForecast = 0 THEN 'BUDGET'
         WHEN PV.IsBaseline = 0 AND PV.IsForecast = 1 THEN 'FORECAST'
       END AS BALANCE_TYPE
      ,CONVERT(VARCHAR(50), PV.[Name]) AS BUDGET_ID
      ,CONVERT(VARCHAR(255), PV.Description) AS BUDGET_DESCRIPTION
      ,DATEPART(YYYY, PV.Date) AS FISCAL_YEAR
     FROM [dbo].[CPP] PV 
     WHERE PV.Name = case
                       when isnull(@ProgramVersionName, '') = '' then PV.Name
                       else @ProgramVersionName
                     end
      AND YEAR(D.Date) = case
                           when isnull(@FiscalYear, 0) = 0 then D.Date
                           else @FiscalYear
                         end
    UNION ALL SELECT
       CASE
         WHEN PV.IsBaseline = 1 AND PV.IsForecast = 0 THEN 'BUDGET'
         WHEN PV.IsBaseline = 0 AND PV.IsForecast = 1 THEN 'FORECAST'
       END AS BALANCE_TYPE
      ,CONVERT(VARCHAR(50), PV.[Name]) AS BUDGET_ID
      ,CONVERT(VARCHAR(255), PV.Description) AS BUDGET_DESCRIPTION
      ,DATEPART(YYYY, PV.Date) AS FISCAL_YEAR
     FROM [dbo].[CPP] PV

     WHERE PV.Name = case
                       when isnull(@ProgramVersionName, '') = '' then PV.Name
                       else @ProgramVersionName
                     end
      AND YEAR(D.Date) = case
                           when isnull(@FiscalYear, 0) = 0 then D.Date
                           else @FiscalYear
                         end

Moreover, the queries on either side of the UNION ALL appear to be completely identical, meaning you're returning two of every returned row.

Dynamic SQL can be necessary when you don't know the names of the tables or the columns being queried against. If those are known, it's almost always possible to write dynamic-free (if perhaps convoluted) code.

marc_s · Accepted Answer · 2016-11-19 08:36:51Z

The actual issue is not with this "scheduling this package in SQL Server Agent Job its giving above error", instead the issue is on OLE DB Source i.e. how you are calling the stored procedure from your OLE DB Source. Here, you would need column names (returned from your stored procedure) to be mapped to what ever destination you are using.

To get the columns names from your stored procedure, we have to use " WITH RESULT SETS" along with the EXECUTE command.

Try this SQL Command in your OLE DB Source component-

EXEC [dbo].[ABC_test]
WITH RESULT SETS
(
 ( 
  [BALANCETYPE] varchar(10) , 
  [BUDGETID] VARCHAR(10),
  [BUDGETDESCRIPTION] VARCHAR(10),
  [FISCALYEAR] VARCHAR(10) 
 )
)

Here, the columns names (may be different) and data types (should be compatible) should match with the what stored procedure is returning. Here, I modified the column names slightly, so it maps like this

Stored procedure -> OLE DB Source column names

BALANCE_TYPE -> BALANCETYPE

BUDGET_ID -> BUDGETID

BUDGET_DESCRIPTION -> BUDGETDESCRIPTION

FISCAL_YEAR -> FISCALYEAR

Collectives™ on Stack Overflow

Error while Executing stored procedure with Dynamic Query in SQL Server Agent Jobs

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related