[Execute SQL Task] Error: Executing the query " : "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Pro

Question

Hi all, I use the following code in execute sql task. I set the result set to single row. Input parameter data type is varchar (8000). Result set is saved in a variable with data type varchar(8000). When I run the package I get an error. ////[Execute SQL Task] Error: Executing the query " -- SET NOCOUNT ON added to prevent extra result ..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Pro/////* Can anyone tell me how to fix this please? I think the way I use parameters in my code is not correct. (FROM (SELECT [object_id] = OBJECT_ID( ? , 'U')) o ) This is the hardcoded one. ( part of the query). When I run the hardcoded query in management studio, I don't get any error.

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE
      @object_name SYSNAME
    , @object_id INT
    , @SQL VARCHAR(8000)
	, @SourceTable varchar(8000)= '[dbo].[ImportTableList]'

SELECT
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
    , @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID(''+@SourceTable+'', 'U')) o

This is the one I used in execute sql task. (Full query)

DECLARE
      @object_name SYSNAME
    , @object_id INT
    , @SQL VARCHAR(8000)
	, @SourceTable varchar(8000)= '[dbo].[ImportTableList]'

SELECT
      @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
    , @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID( ?  , 'U')) o

SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
    SELECT CHAR(13) + '    , [' + c.name + '] ' +
        CASE WHEN c.is_computed = 1
            THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
            ELSE
                CASE WHEN c.system_type_id != c.user_type_id
                    THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
                    ELSE '[' + UPPER(tp.name) + ']'
                END  +
                CASE
                    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
                        THEN '(' + CASE WHEN c.max_length = -1
                                        THEN 'MAX'
                                        ELSE CAST(c.max_length AS VARCHAR(5))
                                    END + ')'
                    WHEN tp.name IN ('nvarchar', 'nchar')
                        THEN '(' + CASE WHEN c.max_length = -1
                                        THEN 'MAX'
                                        ELSE CAST(c.max_length / 2 AS VARCHAR(5))
                                    END + ')'
                    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
                        THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
                    WHEN tp.name = 'decimal'
                        THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                    ELSE ''
                END +
                CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
                    THEN ' COLLATE ' + c.collation_name
                    ELSE ''
                END +
                CASE WHEN c.is_nullable = 1
                    THEN ' NULL'
                    ELSE ' NOT NULL'
                END +
                CASE WHEN c.default_object_id != 0
                    THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
                         ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
                    ELSE ''
                END +
                CASE WHEN cc.[object_id] IS NOT NULL
                    THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
                    ELSE ''
                END +
                CASE WHEN c.is_identity = 1
                    THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
                                    CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
                    ELSE ''
                END
        END
    FROM sys.columns c WITH(NOLOCK)
    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
         ON c.[object_id] = cc.parent_object_id
        AND cc.parent_column_id = c.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +
    ISNULL((SELECT '
    , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
    CASE WHEN i.index_id = 1
        THEN 'CLUSTERED'
        ELSE 'NONCLUSTERED'
    END +' (' + (
    SELECT STUFF(CAST((
        SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
                CASE WHEN ic.is_descending_key = 1
                    THEN ' DESC'
                    ELSE ''
                END
        FROM sys.index_columns ic WITH(NOLOCK)
        WHERE i.[object_id] = ic.[object_id]
            AND i.index_id = ic.index_id
        FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
    FROM sys.indexes i WITH(NOLOCK)
    WHERE i.[object_id] = @object_id
        AND i.is_primary_key = 1), '') + CHAR(13) + ');'

---SET @Result =  @SQL
---PRINT @Result
SELECT @SQL

Answer 1

The error message you're receiving suggests that there is an issue extracting the result of your query into a variable of type DBTYPE_STR in your Execute SQL Task. The issue may be related to the way you are using parameters in your query.

One thing to check is whether the parameter you are passing in is the correct data type and size. You mentioned that your input parameter has a data type of varchar(8000), but it would be helpful to double-check that this matches the data type of the parameter in your query. Additionally, if the length of the input parameter is shorter than 8000 characters, you may want to consider using a smaller data type, such as varchar(255).

Another potential issue could be related to the way you are referencing the @SourceTable variable in your query. It looks like you are using dynamic SQL to create a table based on the table name stored in the @SourceTable variable. In your full query, you are using a parameter (?) to pass in the table name, but in your hardcoded query, you are not using a parameter. If the issue persists, you may want to try using a hardcoded table name in your full query to see if that resolves the issue.

Lastly, it's possible that there is an issue with the result set being returned by your query. You mentioned that you set the result set to a single row and saved it in a variable of type varchar(8000). If the result set contains more than one row or if the size of the result set exceeds the maximum length of the variable, this could cause an issue. You may want to check the size of the result set and consider using a larger variable if necessary.

Overall, without more information about your query and package setup, it's difficult to provide a definitive solution. I would recommend double-checking your parameter data types, trying a hardcoded table name in your full query, and verifying the size of your result set. Additionally, if you are still experiencing issues, you may want to look into logging or debugging tools to help you identify the root cause of the issue.