Schedule restore of live company to test company Dyanamics GP

To keep a fresh copy of our test database always available, I scheduled a restore from the live database into the test company database to occur once a week.
[Please also checkout my updated related post Automated restore of Live Company to Test Company in GP2013 CHANGES 21 Jan 2015]

This is how I do that:

Credit to the stack overflow question for how to find latest backup

Sql to get most recent full backup file for a specific database in sql server

This Microsoft Document shows official GP guide for restoring a live db into test. CHECK THIS AS YOUR INSTALL MAY USE MODULES WE DONT- THAT WILL REQUIRE EXTRA STEPS!!

Set up a test company that has a copy of live company data by using SQL Server

Create a SQL Job and schedule it to run once a week (or whatever you frequency you choose). To this job add the following steps.

Step 1 Restore from Live company into Test company

This step should be set to run from the Master database when configuring it in SQL agent (updated for GP2013 21 Jan 2015).

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

Step 2 Restore the company ID or database id name value

There are references in GP to the company ID in the company database. The company ID is held by the DYNAMICS database. To correct the freshly restored database so it thinks it is the test company introduce the following step. Configure this to run from the test company database when working with it in SQL agent.

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

/* 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_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, 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

Step 3 Change the current database owner

Configure to run against the test company.

sp_changedbowner 'DYNSA'

Step 4 Fix the Next Note ID

The next note ID is held against each company in the DYNAMICS database. This needs changing for the test company to represent the freshly restored data. Run the following to correct the IDS. Configure to run against the test company.

/*
** FindMaxNoteIndex.SQL
**
** Purpose:
**
** Find the max value of NOTEINDX from all tables.
**
** This script must be run against the company in which the notes are incorrect.
** it will automatically update your SY01500 for you to the correct next note index.
**
** Modified by MRO
*/

if exists (select * from tempdb..sysobjects where name = '##GPSMaxNote')
drop table dbo.##GPSMaxNote

set nocount on
create table ##GPSMaxNote (MaxNoteIndex numeric(19,5) null)
go


declare @cStatement varchar(255) /* Value from the t_cursor */
declare @noteidx numeric(19,5)
declare @database as varchar(5)
set @database = cast(db_name() as varchar(5))

/* Get the tables that have a column name of NOTEINDX. */
declare T_cursor cursor for
select 'declare @NoteIndex numeric(19,5) select @NoteIndex = max(NOTEINDX) from ' + o.name + ' insert ##GPSMaxNote values(@NoteIndex)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'NOTEINDX'

/* Ok, we have the list of tables. Now get the max value of NOTEINDX from each table. */

open T_cursor
fetch next from T_cursor into @cStatement
while (@@fetch_status <> -1)
begin
exec (@cStatement)
fetch next from T_cursor into @cStatement
end
deallocate T_cursor

select 'Max Note Index:', max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null
use DYNAMICS
set @noteidx = (select max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null)

update SY01500 set NOTEINDX = (@noteidx + 1.0) where INTERID=@database
set nocount off

Dynamics GP Word Templates References

To get going with Dynamics GP Templates visit these references they will save your day:

Ajit Kurian's Dynamics ERP & SQL Blog - Working with Word Templates in GP 2010

Turning on the gridlines will make it easier to move fields around the layout to see how they will line up with the fields that are already on the report.

Under the developer tab there is an option to show the template in design mode.  Once this change is made you will see the field display that is on the template along with the content control of the field.  This makes the report a little more difficult to read and doesn’t need to be turned on, but when we get to deleting fields on the template it can be useful.

Developing for Dynamics GP - Word Template Debugging

DEX.INI settings

KeepTemplateTempFiles=TRUE
TPELogging=TRUE

When you have these settings in your dex.ini, multiple files will be created in the %temp% folder:

  1. Log file
          -TemplateProcessing129357796245864818.txt
  2. The Document
          - Fabrikam, Inc. Invoice [-STDINV2322-]2011.02.23_16;07;27 ~1-1.docx
  3. The Template
          -Template Invoice 1~1 1~1.docx

Dynamics GP Blogster - Debugging Microsoft Dynamics GP 2010 Word Templates

The Dynamics GP Blogster - Debugging Microsoft Dynamics GP Word Templates Revisited

VEIS Webservice matchcode error

Something changed this week with the VEIS web service that provides validation of European tax registration numbers.

http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl

All of a sudden we get an error! The SOAP error on deserialization is:

'Invalid enum value '3' cannot be deserialized into type 'canford.checkVATWebservice.matchCode'

I regenerated the SOAP reference in Visual Studio, -still not working.

It seems the soap response is containing an invalid (according to the WSDL) value of 3 for the type match code that is returned.

I’ve emailed the address provided for support, no response yet, so to get us running again I’ve added in this as a valid enum, however I have no idea of what the value of 3 actually means!

To fix, edit the Reference.vb file generated after refreshing the reference, found under your service reference directory,  (this example in VB.NET)

<System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "4.0.0.0"),  _
System.Runtime.Serialization.DataContractAttribute(Name:="matchCode", [Namespace]:="urn:ec.europa.eu:taxud:vies:services:checkVat:types")> _
Public Enum matchCode As Integer

<System.Runtime.Serialization.EnumMemberAttribute(Value:="1")> _
_1 = 0

<System.Runtime.Serialization.EnumMemberAttribute(Value:="2")> _
_2 = 1

End Enum

 

Adding

<System.Runtime.Serialization.EnumMemberAttribute(Value:="3")> _        _3 = 2
to give this:
<System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "4.0.0.0"),  _
System.Runtime.Serialization.DataContractAttribute(Name:="matchCode", [Namespace]:="urn:ec.europa.eu:taxud:vies:services:checkVat:types")> _
Public Enum matchCode As Integer

<System.Runtime.Serialization.EnumMemberAttribute(Value:="1")> _
_1 = 0

<System.Runtime.Serialization.EnumMemberAttribute(Value:="2")> _
_2 = 1

<System.Runtime.Serialization.EnumMemberAttribute(Value:="3")> _
_3 = 2
End Enum

Now it will work again. I expect this will be fixed sometime after this post, but for those of you struggling with this issue at least you know what to do now!

update 9th Jan 2015

Refresh of references today brought in the “missing” enum – I guess it is fixed.

<xsd:enumeration value="3">
<xsd:annotation>
<xsd:documentation>NOT_PROCESSED</xsd:documentation>
</xsd:annotation>
</xsd:enumeration>

SQL OUTPUT Clause to move Dynamics GP prices to history

SELECT *
FROM iv00108_history;

DECLARE @ROWCOUNTER AS INT;

SET @ROWCOUNTER = 903843;

WHILE @ROWCOUNTER != 0
BEGIN
DELETE TOP (1000)
IV00108
OUTPUT DELETED.[ITEMNMBR], DELETED.[CURNCYID], DELETED.[PRCLEVEL], DELETED.[UOFM], DELETED.[TOQTY], DELETED.[FROMQTY], DELETED.[UOMPRICE], DELETED.[QTYBSUOM], DELETED.[DEX_ROW_TS] INTO IV00108_History
WHERE PRCLEVEL IN ('LIST');
SET @ROWCOUNTER = @@ROWCOUNT;
END

--select * from iv00108_history
SELECT *
FROM iv00108_history;


Create a history table with the same schema as the live table (might want to remove the indexes to keep size down).

Use the above script, supplying the price list name to move the prices from the live IV00108 table to the new custom table IV00108_history.

This moves the records in batches of 1000 to keep the transaction locking under control in the database. If too much information is locked (selected) we start going from row locks to page locks into table locks. Performance goes at and users start to complain at that point. The price table has three million rows in it so this is a wise precaution.