#GPPT Why wait till you upgrade to install GP Power Tools?

David Meego - Click for blog homepageYesterday, I received an email from a customer that I met in Reno at the GPUG Summit 2016 conference. He asked a technical question on GP Power Tools and mentioned that he had purchased the tool to be used once he had completed the upgrade to Microsoft Dynamics GP 2015.

The question I had to ask was …

  • Why wait till you upgrade to install GP Power Tools?

Continue reading

Microsoft Dynamics GP 2010 Utilities “Hanging” when Upgrading the Databases with Service Pack 3 or any Later Hotfix

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Last month, Kelly Youells posted on the Dynamics GP Support and Services Blog about an issue which can cause Microsoft Dynamics GP 2010 Utilities to hang when updating to Service Pack 3 or later. The details of the issue are posted on the blog and also linked to the Dynamics GP 2010 Hot Topic:

Continue reading

Granting Access and Binding Defaults when recreating SQL Tables

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

Continue reading

Workstation Level Settings Lost during Upgrade

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

When upgrading Microsoft Dynamics GP, it is quite common to get one client (often on the SQL Server) updated first and then update the system and company databases using Dynamics GP Utilities. The final step is then to go around the various workstations and upgrade the client application on those machines.

Continue reading

Backing up and Restoring data when recreating SQL Tables

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Added to the Useful SQL Scripts Series.

There are times when you need to recreate a table to change its structure but don’t want to lose the data stored in the table.  I have had this situation a number of times when working on upgrade support cases where (for some unknown reason) a table does not have the correct table structure to allow the Dexterity Utilities to upgrade it.

Disclaimer: I know that later versions of the Professional Services Tools Library (PSTL) does support recreating a table while maintaining the data.  However, you might not have PSTL installed and the script in this post is simple to use.

The script makes a backup of the current table to a new table of the same name with the suffix BAK.  Once the backup is created, you can use the SQL Maintenance (File >> Maintenance >> SQL) window in Microsoft Dynamics GP to drop and create the table and its Auto Stored Procedures.

Note: Please see the post Granting Access and Binding Defaults when recreating SQL Tables for more information on creating tables (especially when related to upgrades).

Once the table has been recreated, you can run the rest of the script to copy the contents of the backup table into the newly recreated table.  Then the final step is to remove the backup table.

Note: Please make sure you have a current backup of the database before using the code in this article.

T-SQL Script Code

-- Written by David Musgrave of Winthrop Development Consultants

-- Last Modified: 23-Jun-2015

/* This Script is designed to be executed in sections */
/* Please read the instructions included as comments  */
/* Use Find and Replace to change the table name      */
/* Highlight each Section and click Execute or F5     */


/* 1 - Make a backup of the table so the data is saved */
-- Make backup of table
select * into TEMP_GL00105 from GL00105 
select count(*) from TEMP_GL00105 
/* 1 - End of Section ................................ */


/* 2 - Optional for testing: Remove data from original table */
-- Test code to remove data
select count(*) from GL00105
delete from GL00105
select count(*) from GL00105
/* 2 - End of Section ...................................... */


/* 3 - Drop and recreate original table using correct structure */
-- Recreate table at this stage using SQL Maintenance or T-SQL
-- This will leave a blank table of the correct structure

/* 3 - End of Section ......................................... */


/* 4 - Re-populate original table from the previously backed up table */
-- Declare variable for SQL 2005/2008
declare @srcfieldlst varchar(max)
declare @dstfieldlst varchar(max)
declare @dstvalues varchar(max)
-- Declare variable for SQL 2000
-- declare @fieldlst varchar(8000)
-- declare @newfieldlst varchar(8000)
-- declare @dstvalues varchar(8000)

-- Insert data from backup table
set @srcfieldlst = ''
select @srcfieldlst = @srcfieldlst + case when len(@srcfieldlst) > 0 then ', ' else '' end + '[' + dst.cn + ']' 
from ( 
	select o.name tn , c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'TEMP_GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) src
join ( 
	select o.name tn, c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) dst
on src.cn = dst.cn
order by src.colid 

set @dstfieldlst = @srcfieldlst
set @dstvalues = ''
select @dstfieldlst = @dstfieldlst + case when len(@dstfieldlst) > 0 then ', ' else '' end + '[' + dst.cn + ']' 
	 , @dstvalues = @dstvalues + ', ' + '''' + dst.emptyvalue + '''' 
from ( 
	select o.name tn , c.name cn , c.colid from sysobjects o join syscolumns c on o.id = c.id 
	where o.name = 'TEMP_GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) src
right outer join ( 
	select o.name tn, c.name cn , c.colid, c.xtype, t.name,
	case 
		when t.name in ('tinyint','smallint', 'int', 'bigint','bit','uniqueidentifier') then '0' 
		when t.name in ('numeric', 'real','decimal','float','money') then '0.0' 
		when t.name in ('char', 'varchar','nchar','nvarchar','text','ntext','sysname') then ''
		when t.name in ('binary','varbinary','image') then '0x0'
		when t.name in ('datetime','smalldatetime','datetime2','timestamp') then '1900-01-01 00:00:00.000'
		when t.name in ('date') then '1900-01-01'
		when t.name in ('time') then '00:00:00.000'
		else ''
	end emptyvalue
	from sysobjects o join syscolumns c on o.id = c.id join systypes t on c.xtype = t.xtype
	where o.name = 'GL00105'  
	and c.name != 'DEX_ROW_ID'  
--	and c.name != 'DEX_ROW_TS'   
) dst
on src.cn = dst.cn
where src.tn is null
order by src.colid 
 
--print @dstfieldlst
--print @srcfieldlst + @dstvalues

exec ( 'insert GL00105 ( ' + @dstfieldlst + ' ) ' + 'select ' + @srcfieldlst + @dstvalues + ' from TEMP_GL00105 ' ) 
select count(*) from GL00105
/* 4 - End of Section ............................................... */


/* 5 - After verifying that the recreated table has the correct data */
-- Remove Backup Table
drop table TEMP_GL00105 
/* 5 - End of Section .............................................. */



select * from GL00105

Note: The declaration of the varchar for Microsoft SQL Server 2000 cannot use the “max” syntax.

To use this script (also attached at the bottom of this article), please highlight the portions you need to execute and press F5.  Only run the next section of code once you are satisfied that the previous code has executed successfully.

Hope you find this script useful.

David

11-Feb-2011: Added comments to better explain how script works.

06-Dec-2011: Added link to Granting Access and Binding Defaults when recreating SQL Tables post.

12-Mar-2019: Updated with latest code.

SQL Backup & Restore Table Contents.zip

Users (other than ‘sa’) unable to login after upgrade

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

A couple of nights ago, my friend Sivakumar Venkataraman (Siva) pinged me on Messenger about an issue he was having.  He was in the process of upgrading a customer’s site from Microsoft Business Solutions – Great Plains 8.0 to Microsoft Dynamics GP 10.0, and after the upgrade none of the users were able to log into Dynamics GP. The only exception was the ‘sa’ user.

My first thought was … Security.

Continue reading

Information about using v10.0 Security Conversion Tool

David <a href=David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

From the Microsoft Dynamics GP Application Level Security Series. This is the final article in this series at this time.

Microsoft Dynamics GP version 10.0 introduces a new pessimistic task and role based security model. This new model has a completely different table structure to the previous optimistic user and class based security model.  Which means that when upgrading the security settings cannot just be transferred across.

Continue reading