Microsoft Dynamics GP (Archived)

Getting cannot create stored procedure error on company on upgrade to 2016 r2

(0) Share

Report

Posted on by Community Member

Trying to prepare for an upgrade to R2 and getting an error on updating the companies for 1 company out of 14.

It fails on the following:

the below is redacted some for space.

*****************************

/* Date: 03/23/2017 Time: 11:18:11
stmt(27781896):*/
create procedure dbo.glpPostBudgetTransaction

*******************************

/* Date: 03/23/2017 Time: 11:18:11
SQLSTATE:(21S01) Native Err:(213) stmt(27781896):*/
[Microsoft][SQL Server Native Client 11.0][SQL Server]Column name or number of supplied values does not match table definition.*/

I am not sure what it could be or how to resolve. I thought about exporting the SP from a company that did upgrade and see what the errors are but I suspect it will be the same the above.

*This post is locked for comments

All responses (4)

Answers (0)

Community Member on at

Like (1)

Report

RE: Getting cannot create stored procedure error on company on upgrade to 2016 r2

great I just did it in production and it messed up our system. Just kidding.

I am having someone do a compare on all the tables between two companies now. Then I will go from there. Since the upgrade did not work I suspect I will have to reinstall the older version of GP in the test environment and do the drop/add before the upgrade. I don't think I can get to the SQL main on the company that is failed using the new version of GP.
twelvestrikes 3,657 on at

Like (1)

Report

RE: Getting cannot create stored procedure error on company on upgrade to 2016 r2

Hi Aaron

I am going to agree with Tim on this one, that something didn't upgrade correctly in a previous upgrade.

This is what I would try if I was faced with this issue and by no means is this the best way or the only way to resolve the issue.

I do stress that you should only try what I am suggestion in a test environment first and validate the results.

My understanding is you are on Dynamics GP 2016 and updating to GP 2016 R2.

Looking at the error you provided, it looks like it is those tables that allow you to do Budget transactions (GL12000, GL12001 & GL32000).

Firstly I would check the pre-upgraded databases and compare the fields for these 3 tables in the company with the problems vs. one of your other company's that did upgrade correctly. You many see the problem doing this visual.

To fix this, If you do not use Budget transactions then I would go into your working Dynamics GP company (in a test environment) and use SQL Maintenance (Microsoft Dynamics GP > Maintenance > SQL).

For the 3 tables drop the tables and the stored procs and then rebuild them. That should give them the proper format. Now with this company you can try your upgrade to GP2016 R2 and see if it works.

If you do have data, you may have to write the data out to a temp table and then write the data back in to the correct fields along with adding any relevant data for the new table format.

Again I caution you to do this in a test environment first.
Community Member on at

Like (0)

Report

RE: Getting cannot create stored procedure error on company on upgrade to 2016 r2

am not sure since as I did not have nay issue with the past upgrade to 2016 about a year ago. everything else with the company seems to work fine. It is only that company that seems to have an issue. any other thoughts?
Tim Wappat 5,703 on at

Like (0)

Report

RE: Getting cannot create stored procedure error on company on upgrade to 2016 r2

I bet this is due to that one company having had a table definition changed by someone or it has not be upgraded properly on a previous upgrade so the table schema is out of sync with the version it should be at.

There are some good SQL schema compare tools to compare tables.

David Musgrave has some tools for checking GP before upgrade that can help identify issues too.

My fear would be that there may be more than one table at fault, so some investigation is needed.

Tim.