Dynamic Code Blocks

Microsoft Dynamics GP & .NET technical Blog by Tim Wappat

Automated restore of Live Company to Test Company in GP2013 CHANGES

Don’t be caught out by this change in the automated restore of test company database from live. This is something we do every Friday night on a scheduled SQL job, however since updating to GP2013 our test company is not working after the restore. Attempts to login are greeted by:

The selected company is not configured as a company of the current Microsoft Dynamics System Database

If you refer to my post Schedule restore of live company to test company Dynamics GP, then you will see that after the database is restored a script is ran to restore the company ID in the restored company database to update it to match that of the test company as registered in the DYNAMICS database.

A change has happened in GP2013, where the DYNAMICS database in GP can now be named anything you like, this has resulted in some changes to the way this script needs to work.

My first move was to refer to the original Microsoft KB that I got the information from the first time around, and yes there is a new script that forks depending on the presence of table SY00100. Check it out below, or better yet go to the KB itself, in case it has been updated since this post. 

Set up a test company that has a copy of live company KB 872370

So the new script for GP2013 (and previous versions) is:

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
declare @Statement varchar(850)
select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('
'COMPANYID'',''CMPANYID'')
then '
'update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
else '
'update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '
+rtrim(DBNAME)+'.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('
'COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
and rtrim(a.TABLE_NAME)+'
'-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'

from SY00100
exec (@Statement)
end
else begin
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off
end

The old script for reference was:

/******************************************************************************/

/* Description: */

/* Updates any table that contains a company ID or database name value */

/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */

/* */

/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+ a.TABLE_SCHEMA + '.' + a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
'update '+ a.TABLE_SCHEMA + '.' + a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'
and a.TABLE_NAME <> 'UPR70501' and a.TABLE_NAME <> 'MPOSMAIN'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
Exec (@cStatement)

FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables


NOTE: If you have already ran the old script, then the new script will now not execute, the error indicates that it can’t find company lookup table SY01500 in the company database. That is because it should be looking for it in the  DYNAMCS database.

A look at the script reveals the issue, the old script wrongly updates table SY00100 to the test database name (it is just dumbly looking for instances of DBNAME), it should have DYNAMICS in the DBNAME field to make the new script work, but it has the test company DBNAME in there, due to this issue. Update it to be whatever your DYNAMICS database name is, using DYNAMICS as example below (your CMPANYID may be different).

image

Now run the new script again and it will run successfully and you can now login to the test company.

I have updated my previous post to point to this post and changed the script there to protect the innocent.

GP2015 Warns of CAPS LOCK on login

Vaidhyanathan Mohan has observed the long missing caps lock warning has been added to GP2015 on the login form. See it for yourself on his blog

GP 2015 – New Feature – Caps Lock On Message

image

Selecting a Microsoft Dynamics GP Partner

This is a very difficult task. There are many factors that can go into choosing a Dynamics Partner. With so much to think about, on this page I have noted some things you might like to consider.

image

Service Orientated Architecture & Dynamics GP–the future is bright

Andrew Hayward keynote the m-hance conference earlier in the year It being late December I’m ref

Andrew Haywood of m-hance #DYNBC14

Andrew Hayward keynote the m-hance conference earlier in the year


It being late December I’m reflecting on the year, one of the highlights for me was when I got the pleasure of a long chat, one to one, with Chad Sogge & Daryl Anderson and then a quick chat to Errol Schoenfish. I came away having learnt many things that have really changed my whole opinion of the Dynamics GP product future.

The move to service orientated architecture in GP 2015 version is perfect for developers, as is the tighter integration with .NET from Dexterity. This really does open up the product to do virtually anything we can dream up. In my view, this is a really significant moment in the evolution of GP. Today it can now leverage the investment Microsoft has made with the .NET framework and the full Microsoft product stack, including cloud computing.

I also observe Microsoft handing back third party products to the original developers and simultaneously unzipping the core product for more developers to build awesome things upon it. To me this feels like a message that – hey, we can’t be everything to everyone and also keep it all maintained and yet still drive the product forward, it’s time to let you do that for us.  Microsoft have a growing ISV community around that can build great things for specific market verticals,  thus allowing  Microsoft to maintain the framework, core product, adding new features and performance… boy so many features have been delivered recently! The agile development burndown cadence MS have at the moment is really swelling the bullet points in the sales datasheets and giving us plenty of “new toys” to implement! Smile

I do think there are some amazing years ahead of us in the Dynamics world, presenting many opportunities for the ISV community to grow and build some great products on top of our Fargo friends hard work! I can’t wait to see what the next year brings on.