Stored procs for Crystal 2013

[2can2]

Hi, I am struggling updating a few custom stored procs used for reports in V6. I am trying to put the changes into the V7 updated procedures wherever possible but 2 in particular I keep coming up with an error 213 - 'Column Name or number supplied does not match the table definition'. I have been through the code many many times but cannot find the issue.
Is there a way I can get an indication of the temptables or field name that are missing or incorrectly named when I exec the stored proc in management studio?
Any advice would be appreciated. Thanks.

[Mike.Sheen]

Hi, I am struggling updating a few custom stored procs used for reports in V6. I am trying to put the changes into the V7 updated procedures wherever possible but 2 in particular I keep coming up with an error 213 - 'Column Name or number supplied does not match the table definition'. I have been through the code many many times but cannot find the issue.
Is there a way I can get an indication of the temptables or field name that are missing or incorrectly named when I exec the stored proc in management studio?
Any advice would be appreciated. Thanks.

I always just use a process of methodical isolation and inspection.

For example - comment out everything except for the first query in your SP that performs an insert - run that - does it work? Yes, move one and uncomment the code to the next insert - repeat this process until you have the query which is incorrect.

Then it's just a case of manually checking the query and matching each column of the temp table with the column of the query.

Stored procedures based off our usp_Jiwa_Invoices_Invoice stored procedure are notorious for this issue, as it has temp tables with 80+ columns and the summary row appended is somewhat easy to mess up:

Code: Select all

INSERT INTO #TempTable2
SELECT
'',1,'Dummy','',0,'','','','',0,0,0,0,'',SO_Main.InvoiceNo,SO_History.HistoryNo,'','','','','','','','','','','','','','','','','','','','','','','','',GETDATE(),'','','','','',0,0,0,0,0,'',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','', 0, 0, 0, 0, '',
'', '', '', '', '', '', '', '', '', ''

Mike