As the title suggests I have a SQL Stored Procedure which runs with no issues and returns the expected values when called from within SSMS. However, when I run the same procedure from C# it fails. The specific error message I get in return when debugging is: "The column names or number of supplied values does not match the table definition"
I was given a line number and find that the error is here:
DROP TABLE IF EXISTS ##TEMP
CREATE TABLE ##TEMP
(BatchID int, task_code nvarchar(100), status_code nvarchar(100), early_start_date nvarchar(100), early_end_date nvarchar(100), month_year_start nvarchar(100), month_year_finish nvarchar(100)) --Finds month year of early start/finish for activities
INSERT INTO ##TEMP
SELECT BatchID, task_code, status_code, early_start_date, early_end_date, CAST(MONTH(early_start_date) AS nvarchar) + '/' + CAST(YEAR(early_start_date) AS nvarchar) AS month_year_start, CAST(MONTH(early_end_date) AS nvarchar) + '/' + CAST(YEAR(early_end_date) AS nvarchar) AS month_year_finish FROM TASK WHERE BatchID = @NextMaxBatch
If necessary, here is the full code of my procedure.
ALTER PROCEDURE [dbo].[AdherenceDrillDown] (@Project nvarchar(10))
AS
BEGIN
DROP TABLE IF EXISTS ##TEMP
DROP TABLE IF EXISTS ##TEMP2
DROP TABLE IF EXISTS ##TEMP3
DROP TABLE IF EXISTS ##TEMP4
DECLARE @BatchCount int
SET @BatchCount = (SELECT COUNT(BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchType = 'Current' AND BatchID IN (SELECT BatchID FROM TASK))
PRINT @BatchCount
IF @BatchCount < 2 -- There must be at least 2 "Current" batches stored under this project number in order to compare records. If this check fails, the procedure exits
BEGIN
PRINT 'Not Enough Records'
Return;
END
DECLARE @BBatchCount int
SET @BBatchCount = (SELECT COUNT(BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchType = 'Baseline' AND BatchID IN (SELECT BatchID FROM TASK))
PRINT @BBatchCount
IF @BBatchCount < 1 -- There must be at least 1 "Baseline" batches stored under this project number in order to compare records. If this check fails, the procedure exits
BEGIN
PRINT 'Not Enough Records'
Return;
END
DECLARE @MaxBatch int
SET @MaxBatch = (SELECT MAX (BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchTypeVersion = 0 AND BatchType = 'Current') --Selects top batch
DECLARE @NextMaxBatch int
SET @NextMaxBatch = (SELECT MAX (BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchTypeVersion = 1 AND BatchType = 'Current' AND BatchID NOT IN (SELECT MAX (BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project)) -- Selects 2nd most top batch
DECLARE @BBatch int
SET @BBatch = (SELECT MAX (BatchID) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchTypeVersion = 0 AND BatchType = 'Baseline') --Selects top baseline batch
DECLARE @DataDate date
SET @DataDate = (SELECT MAX (DataDate) FROM P6Batch WHERE EPMSProjectNumber = @Project AND BatchID = @MaxBatch) -- Selects top Data Date
DECLARE @DataDateMY nvarchar(50)
SET @DataDateMY = (SELECT(CAST(MONTH(@DataDate) AS nvarchar) + '/' + CAST(YEAR(@DataDate) AS nvarchar)))
DROP TABLE IF EXISTS ##TEMP
CREATE TABLE ##TEMP
(BatchID int, task_code nvarchar(100), status_code nvarchar(100), early_start_date nvarchar(100), early_end_date nvarchar(100), month_year_start nvarchar(100), month_year_finish nvarchar(100)) --Finds month year of early start/finish for activities
INSERT INTO ##TEMP
SELECT BatchID, task_code, status_code, early_start_date, early_end_date, CAST(MONTH(early_start_date) AS nvarchar) + '/' + CAST(YEAR(early_start_date) AS nvarchar) AS month_year_start, CAST(MONTH(early_end_date) AS nvarchar) + '/' + CAST(YEAR(early_end_date) AS nvarchar) AS month_year_finish FROM TASK WHERE BatchID = @NextMaxBatch
DROP TABLE IF EXISTS ##TEMP2
CREATE TABLE ##TEMP2
(BatchID int, task_code nvarchar(100), status_code nvarchar(100), act_start_date nvarchar(100), act_end_date nvarchar(100), act_month_year_start nvarchar(100), act_month_year_finish nvarchar(100)) --Finds actual month year of start/finish for activities
INSERT INTO ##TEMP2
SELECT BatchID, task_code, status_code, act_start_date, act_end_date, CAST(MONTH(act_start_date) AS nvarchar) + '/' + CAST(YEAR(act_start_date) AS nvarchar) AS act_month_year_start, CAST(MONTH(act_end_date) AS nvarchar) + '/' + CAST(YEAR(act_end_date) AS nvarchar) AS act_month_year_finish FROM TASK WHERE BatchID = @MaxBatch AND task_code IN (SELECT task_code FROM ##TEMP)
DROP TABLE IF EXISTS ##TEMP3
CREATE TABLE ##TEMP3
(task_code nvarchar(100), status_code nvarchar(100), month_year_start nvarchar(100), month_year_finish nvarchar(100), act_month_year_start nvarchar(100), act_month_year_finish nvarchar(100), start_adherence int, finish_adherence int) --Compares
INSERT INTO ##TEMP3
SELECT NM.task_code, NM.status_code, NM.month_year_start, NM.month_year_finish, M.act_month_year_start, M.act_month_year_finish, IIf(NM.month_year_start = M.act_month_year_start,1,0) AS start_adherence, IIf(NM.month_year_finish = M.act_month_year_finish,1,0) AS finish_adherence FROM ##TEMP2 M INNER JOIN ##TEMP NM ON M.task_code = NM.task_code
SELECT * FROM ##TEMP3 WHERE month_year_finish = '11/2022' OR month_year_start = '11/2022'
INSERT INTO TASKTEST
(
[BatchID]
,[task_id]
,[proj_id]
,[wbs_id]
,[WBSShortPath]
,[clndr_id]
,[week_hr_cnt]
,[est_wt]
,[phys_complete_pct]
,[complete_pct]
,[rev_fdbk_flag]
,[lock_plan_flag]
,[auto_compute_act_flag]
,[complete_pct_type]
,[task_type]
,[duration_type]
,[review_type]
,[status_code]
,[task_code]
,[task_name]
,[rsrc_id]
,[total_float_hr_cnt]
,[free_float_hr_cnt]
,[remain_drtn_hr_cnt]
,[act_drtn_hr_cnt]
,[base_drtn_hr_cnt]
,[total_drtn_hr_cnt]
,[act_work_qty]
,[remain_work_qty]
,[target_work_qty]
,[target_drtn_hr_cnt]
,[target_equip_qty]
,[act_equip_qty]
,[remain_equip_qty]
,[cstr_date]
,[act_start_date]
,[act_end_date]
,[late_start_date]
,[late_end_date]
,[expect_end_date]
,[target_start_date]
,[target_end_date]
,[restart_date]
,[reend_date]
,[early_start_date]
,[early_end_date]
,[review_end_date]
,[rem_late_start_date]
,[rem_late_end_date]
,[cstr_type]
,[priority_type]
,[guid]
,[tmpl_guid]
,[cstr_date2]
,[cstr_type2]
,[float_path]
,[float_path_order]
,[act_this_per_work_qty]
,[act_this_per_equip_qty]
,[driving_path_flag]
,[suspend_date]
,[resume_date]
,[external_early_start_date]
,[external_late_end_date]
,[cbs_id]
,[location_id]
,[UDF3]
,[PK]
,[PD]
,[PH]
)
SELECT [BatchID]
,[task_id]
,[proj_id]
,[wbs_id]
,[WBSShortPath]
,[clndr_id]
,[week_hr_cnt]
,[est_wt]
,[phys_complete_pct]
,[complete_pct]
,[rev_fdbk_flag]
,[lock_plan_flag]
,[auto_compute_act_flag]
,[complete_pct_type]
,[task_type]
,[duration_type]
,[review_type]
,[status_code]
,[task_code]
,[task_name]
,[rsrc_id]
,[total_float_hr_cnt]
,[free_float_hr_cnt]
,[remain_drtn_hr_cnt]
,[act_drtn_hr_cnt]
,[base_drtn_hr_cnt]
,[total_drtn_hr_cnt]
,[act_work_qty]
,[remain_work_qty]
,[target_work_qty]
,[target_drtn_hr_cnt]
,[target_equip_qty]
,[act_equip_qty]
,[remain_equip_qty]
,[cstr_date]
,[act_start_date]
,[act_end_date]
,[late_start_date]
,[late_end_date]
,[expect_end_date]
,[target_start_date]
,[target_end_date]
,[restart_date]
,[reend_date]
,[early_start_date]
,[early_end_date]
,[review_end_date]
,[rem_late_start_date]
,[rem_late_end_date]
,[cstr_type]
,[priority_type]
,[guid]
,[tmpl_guid]
,[cstr_date2]
,[cstr_type2]
,[float_path]
,[float_path_order]
,[act_this_per_work_qty]
,[act_this_per_equip_qty]
,[driving_path_flag]
,[suspend_date]
,[resume_date]
,[external_early_start_date]
,[external_late_end_date]
,[cbs_id]
,[location_id]
,[UDF3]
,[PK]
,[PD]
,[PH]
FROM TASK
WHERE BatchID = @MaxBatch AND task_code IN (SELECT task_code FROM TASK WHERE BatchID = @NextMaxBatch AND (CAST(MONTH(early_start_date) AS nvarchar) + '/' + CAST(YEAR(early_start_date) AS nvarchar) = @DataDateMY OR CAST(MONTH(early_end_date) AS nvarchar) + '/' + CAST(YEAR(early_end_date) AS nvarchar) = @DataDateMY))
UPDATE TASKTEST
SET StartAd = T3.start_adherence FROM TASKTEST TT INNER JOIN ##TEMP3 T3 on T3.task_code = TT.task_code AND TT.BatchID = @MaxBatch --Updates Task Table with boolean
UPDATE TASKTEST
SET FinishAd = T3.finish_adherence FROM TASKTEST TT INNER JOIN ##TEMP3 T3 on T3.task_code = TT.task_code AND TT.BatchID = @MaxBatch --Updates Task Table with boolean
DROP TABLE IF EXISTS ##TEMP4
CREATE TABLE ##TEMP4
(task_code nvarchar(100), status_code nvarchar(100), month_year_start nvarchar(100), month_year_finish nvarchar(100), act_type nvarchar(10))
INSERT INTO ##TEMP4
SELECT M.task_code, M.status_code, NM.month_year_start, NM.month_year_finish, IIf(NM.month_year_start = @DataDateMY AND NM.month_year_finish = @DataDateMY,'Both',IIf(NM.month_year_start = @DataDateMY,'Start',IIf(NM.month_year_finish = @DataDateMY,'Finish','None'))) AS act_type FROM ##TEMP2 M INNER JOIN ##TEMP NM ON M.task_code = NM.task_code
UPDATE TASKTEST
SET ACTTYPE = T4.act_type FROM TASKTEST TT INNER JOIN ##TEMP4 T4 ON T4.task_code = TT.task_code AND TT.BatchID = @MaxBatch --Updates Task Table with Activity Type: Start, Finish, or Both
DELETE FROM TASKTEST WHERE StartAd IS NULL AND FinishAd IS NULL AND BatchID = @MaxBatch
DELETE FROM TASKTEST WHERE BatchID = @MaxBatch AND task_code NOT IN (SELECT task_code FROM TASK WHERE BatchID = @NextMaxBatch AND (CAST(MONTH(early_start_date) AS nvarchar) + '/' + CAST(YEAR(early_start_date) AS nvarchar) = @DataDateMY OR CAST(MONTH(early_end_date) AS nvarchar) + '/' + CAST(YEAR(early_end_date) AS nvarchar) = @DataDateMY))
DECLARE @ActualStarts int
DECLARE @PlannedStarts int
DECLARE @BPlannedStarts int
DECLARE @ActualFinishes int
DECLARE @PlannedFinishes int
DECLARE @BPlannedFinishes int
DECLARE @WithinMonth int
DECLARE @FailedStarts int
DECLARE @FailedFinishes int
SET @ActualStarts = (SELECT SUM(StartAd) FROM TASKTEST WHERE (ACTTYPE = 'Start' OR ACTTYPE = 'Both') AND BatchID = @MaxBatch)
SET @PlannedStarts = (SELECT COUNT(task_code) FROM TASK WHERE MONTH(early_start_date) = MONTH(@DataDate) AND YEAR(early_start_Date) = YEAR(@DataDate) AND BatchID = @NextMaxBatch)
SET @FailedStarts = (SELECT COUNT(task_code) FROM TASKTEST WHERE (ACTTYPE = 'Start' OR ACTTYPE = 'Both') AND StartAd = '0' AND BatchID = @MaxBatch)
SET @BPlannedStarts = (SELECT COUNT(task_code) FROM TASK WHERE MONTH(early_start_date) = MONTH(@DataDate) AND YEAR(early_start_Date) = YEAR(@DataDate) AND BatchID = @BBatch)
SET @ActualFinishes = (SELECT SUM(FinishAd) FROM TASKTEST WHERE (ACTTYPE = 'Finish' OR ACTTYPE = 'Both') AND BatchID = @MaxBatch)
SET @PlannedFinishes = (SELECT COUNT(task_code) FROM TASK WHERE MONTH(early_end_date) = MONTH(@DataDate) AND YEAR(early_end_Date) = YEAR(@DataDate) AND BatchID = @NextMaxBatch)
SET @FailedFinishes = (SELECT COUNT(task_code) FROM TASKTEST WHERE (ACTTYPE = 'Finish' OR ACTTYPE = 'Both') AND FinishAd = '0' AND BatchID = @MaxBatch)
SET @BPlannedFinishes = (SELECT COUNT(task_code) FROM TASK WHERE MONTH(early_end_date) = MONTH(@DataDate) AND YEAR(early_end_Date) = YEAR(@DataDate) AND BatchID = @BBatch)
SET @WithinMonth = (SELECT COUNT(task_code) FROM TASKTEST WHERE MONTH(act_end_Date) = MONTH(@DataDate) AND YEAR(act_end_date) = YEAR(@DataDate) AND BatchID = @MaxBatch AND StartAd = 1 AND FinishAd = 1)
INSERT INTO DRILLDOWN
VALUES(@MaxBatch, @Project, @DataDate, @ActualStarts, @PlannedStarts, @BPlannedStarts, @ActualFinishes, @PlannedFinishes, @BPlannedFinishes, @WithinMonth, @FailedStarts, @FailedFinishes)
DROP TABLE ##TEMP
DROP TABLE ##TEMP2
DROP TABLE ##TEMP3
DROP TABLE ##TEMP4
END
Here is how I am calling the procedure from C#:
SqlCommand runMetricsCmd2 = new SqlCommand();
runMetricsCmd2.Connection = sqlConnectionP6;
runMetricsCmd2.CommandText = "AdherenceDrillDown";
runMetricsCmd2.CommandType = CommandType.StoredProcedure;
runMetricsCmd2.Parameters.AddWithValue("@Project", ProjectParam);
runMetricsCmd2.ExecuteNonQuery();
I want to reiterate that it runs, provides the expected output, and returns no errors when ran in SSMS. It does not produce any result when called from C# and specifically returns the error above. I have confirmed that the connection is correct, the name of the parameter/procedure match, and that the parameter value is valid as well. I read elsewhere that it could be an issue with table definition caching, however I have not made any changes to the table definition in weeks and only recently started to receive this error when I began attempting to call it from C#.
The error is not obvious. I have two suggestions.
1 - Why are you using global temporary tables (##TEMP). Unless you are sharing these tables between two parallel processes, use local temporary tables (#TEMP).
2 - While semicolons, commenting and formatting are seldom used, I would refactor your code to use them. Some TSQL statements require them.
3 - Here is a refactor of the code that causes the error. It has semicolons between statements, comments and formatting. Unfortunately, there is not obvious coding error.
-- Remove old
DROP TABLE IF EXISTS ##TEMP;
-- Create new
CREATE TABLE ##TEMP
(
BatchID int,
task_code nvarchar(100),
status_code nvarchar(100),
early_start_date nvarchar(100),
early_end_date nvarchar(100),
month_year_start nvarchar(100),
month_year_finish nvarchar(100)
);
-- Insert data
INSERT INTO ##TEMP
SELECT
BatchID,
task_code,
status_code,
early_start_date,
early_end_date,
CAST(MONTH(early_start_date) AS nvarchar) + '/' + CAST(YEAR(early_start_date) AS nvarchar) AS month_year_start,
CAST(MONTH(early_end_date) AS nvarchar) + '/' + CAST(YEAR(early_end_date) AS nvarchar) AS month_year_finish
FROM
TASK
WHERE
BatchID = @NextMaxBatch;
Hopefully the refactoring will allow you to discover an issue.
4 - You have a ton of drop temp table statements. You only really need one before the create table if you are using local temp tables. The scope of the table goes away after the session is dropped.
In short, refactor and hopefully the issue appears.