July 16, 2018 at 9:24 am
Hi,
After upgrading SQL Server from 2008r2 to 2016, I'm getting the below listed error.
the metadata could not be determined because statement 'exec(@Exec sql)' in procedure 'spXYZABC'contains dynamic SQL, COnsider using the WITH RESULT SETS clause to explicitly describe the result set
Here is how i'm calling the stored proc in SSIS. I'm using OLEDB Command with EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT.
I tried
EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT
WITH RESULT SETS
(
(
A INT,
B INT,
C INT,
D INT
)
)
and got the below error EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.
and when i try WITH RESULT SET NONE i get the below error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
and when i try WITH RESULT SETS UNDEFINED i get the error 'the metadata could not be determined because statement 'exec(@Exec sql)' contains dynamic SQL, Consider using the WITH RESULT SETS clause to explicitly describe the result set' again.
Can some one help me out with this?
July 16, 2018 at 10:02 am
PravSat - Monday, July 16, 2018 9:24 AMHi,
After upgrading SQL Server from 2008r2 to 2016, I'm getting the below listed error.the metadata could not be determined because statement 'exec(@Exec sql)' in procedure 'spXYZABC'contains dynamic SQL, COnsider using the WITH RESULT SETS clause to explicitly describe the result set
Here is how i'm calling the stored proc in SSIS. I'm using OLEDB Command with EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT.
I tried
EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT
WITH RESULT SETS
(
(
A INT,
B INT,
C INT,
D INT
)
)
and got the below error EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.and when i try WITH RESULT SET NONE i get the below error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
and when i try WITH RESULT SETS UNDEFINED i get the error 'the metadata could not be determined because statement 'exec(@Exec sql)' contains dynamic SQL, Consider using the WITH RESULT SETS clause to explicitly describe the result set' again.
Can some one help me out with this?
Can you post the definition of the proc?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 16, 2018 at 10:27 am
PravSat - Monday, July 16, 2018 9:24 AMHi,
After upgrading SQL Server from 2008r2 to 2016, I'm getting the below listed error.the metadata could not be determined because statement 'exec(@Exec sql)' in procedure 'spXYZABC'contains dynamic SQL, COnsider using the WITH RESULT SETS clause to explicitly describe the result set
Here is how i'm calling the stored proc in SSIS. I'm using OLEDB Command with EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT.
I tried
EXEC [schema].[spXYZABC] ?,?,? OUTPUT, ? OUTPUT
WITH RESULT SETS
(
(
A INT,
B INT,
C INT,
D INT
)
)
and got the below error EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.and when i try WITH RESULT SET NONE i get the below error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
and when i try WITH RESULT SETS UNDEFINED i get the error 'the metadata could not be determined because statement 'exec(@Exec sql)' contains dynamic SQL, Consider using the WITH RESULT SETS clause to explicitly describe the result set' again.
Can some one help me out with this?
If your dynamic SQL does NOT return a result set, you have a problem. SSRS expects there to at least be an empty result set. As Phil requested, post your code. IF statements that result in NO result set are a bad idea for a sproc for SSRS.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply