Added to the Useful SQL Scripts Series.
This is a follow on post from my previous article: Backing up and Restoring data when recreating SQL Tables which explains how to recreate tables (usually to fix table structure issues) while making sure that the data is preserved.
In the previous post; it has a step 3 to recreate the table and suggested that you can use the SQL Maintenance window from the application or T-SQL commands.
However, if you are in the middle of an upgrade with a failed table conversion, you will need to ensure that the table has the structure of the version you are upgrading from and not the version you are upgrading to. This will then allow the Microsoft Dynamics GP utilities to complete the table conversion for us (including any data manipulation required). So using SQL Maintenance while logged into an already upgraded company back to the failed company database is not the correct method.
Another method of re-creating the table is to generate the script from a correct version in a pre upgrade database. You can use the Script Table as and Script Stored Procedure as options to generate the scripts to Drop and Create the Table and its associated zDP Stored Procedures.
Using the scripts generated by this method miss a couple of steps. They don’t:
- Grant Access to DYNGRP for the Table and Stored Procedures
- Bind Defaults for datetime, character, integer and currency datatypes
Granting access is required to allow users (other than ‘sa’) to be able to access the newly created SQL objects. Binding defaults sets up a default value for table columns to use when no data is provided on an insert statement. This will avoid “Cannot insert NULL” errors when inserting into a table without providing data for every column.
Note: Mariano has provided a method to get the Access and Defaults included in the scripts generated by SQL Server. Please see his post Granting Access and Binding Defaults when recreating SQL Tables: a follow up for details.
The following script (also attached at the bottom of this article) can be used to grant access to DYNGRP for the table and stored procedures and Bind Defaults for all tables in the database.
Hope you find this script useful.
06-Dec-2011: Added a link to Mariano’s post which shows the steps to avoid the need for the additional script provided in this post.