TIP: You can review the compatability guide, considerations and premilinary steps for the migration here

NOTE: This article and it's contents are provided as a guideline for migrating from Sage 200 Professional to Sage 200 Standard with steps we have tested with. That isn't to say that these steps will necessarily work for all customer data due to the differing configuration, data size and bespoke integration. If you have problems when attempting to migrate in this way, we would suggest exporting information from Sage 200 Professional and importing into Sage 200 Standard which has been the accepted process for some time.

CAUTION: It is imperative before attempting any migration to make sure that a customer is not using features in 200 Professional that aren't available in Standard Online or that they are aware these won't be available. To help with this, please see this article which outlines the main differences between the variants and whether migration is possible.

The below instructions are for migrating the database from Sage 200 Professional 2020 R1 to Sage 200 Standard with the settings configured in this article. If the settings haven't been altered as detailed in the article it's likely there will be issues with either the migration process or using the software after.

CAUTION: These steps include editing SQL Server database tables and should only be attempted by a user trained in both Sage 200 Professional and SQL Server.

CAUTION: The following script MUST be run before migrating into a 200 Standard environment.

1. The following script will adjust the PLPaymentGroup table to match the options in 200 Standard application, if this is not carried out you will likely have issues with the payment processing feature in the application.

SET XACT_ABORT ON
BEGIN TRAN

-- Create temporary stored procedure to ensure a payment group exists with the required settings
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID('tempdb.[dbo].[#spCreateOrUpdatePaymentGroup]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE #spCreateOrUpdatePaymentGroup
END
GO

CREATE PROC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID BIGINT, @PLPaymentDocumentTypeID BIGINT, @PaymentGroupNumber SMALLINT, @Description VARCHAR(30), 
	@PostToLedgerOnPaymentRun BIT, @UseThisWhenSinglePaymentGroup BIT, @SummarisePayments BIT, @IsSagePaymentsGroup BIT
AS
IF EXISTS (SELECT 1 FROM PLPaymentGroup WHERE PLPaymentGroupID = @PLPaymentGroupID)
BEGIN
	UPDATE PLPaymentGroup
	SET 
		PLPaymentDocumentTypeID = @PLPaymentDocumentTypeID, PaymentGroupNumber = @PaymentGroupNumber, [Description] = @Description,
		PostToLedgerOnPaymentRun = @PostToLedgerOnPaymentRun, UseThisWhenSinglePaymentGroup = @UseThisWhenSinglePaymentGroup, 
		SummarisePayments = @SummarisePayments, IsSagePaymentsGroup = @IsSagePaymentsGroup
	WHERE PLPaymentGroupID = @PLPaymentGroupID
END
ELSE
BEGIN
	INSERT INTO PLPaymentGroup (PLPaymentGroupID, PLPaymentDocumentTypeID, PaymentGroupNumber, [Description], PostToLedgerOnPaymentRun, 
		UseThisWhenSinglePaymentGroup, SummarisePayments, IsSagePaymentsGroup)
	VALUES (@PLPaymentGroupID, @PLPaymentDocumentTypeID, @PaymentGroupNumber, @Description, @PostToLedgerOnPaymentRun,
		@UseThisWhenSinglePaymentGroup, @SummarisePayments, @IsSagePaymentsGroup)
END
GO

-- Get a list of all suppliers and which document type it is targeting
DECLARE @SUPPLIER_GROUPS TABLE (PLSupplierAccountID BIGINT, PLPaymentDocumentTypeID BIGINT)

INSERT INTO @SUPPLIER_GROUPS (PLSupplierAccountID, PLPaymentDocumentTypeID)
SELECT SA.PLSupplierAccountID, PG.PLPaymentDocumentTypeID
FROM PLSupplierAccount SA
INNER JOIN PLPaymentGroup PG ON SA.PLPaymentGroupID = PG.PLPaymentGroupID

-- If any document types will be out of range (not within 0 - 6) put them into the first document type
UPDATE @SUPPLIER_GROUPS SET PLPaymentDocumentTypeID = 0 WHERE PLPaymentDocumentTypeID < 0 OR PLPaymentDocumentTypeID > 6

-- Use the stored procedure to set up the required payment groups
EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 1, @PLPaymentDocumentTypeID = 0, @PaymentGroupNumber = 0, @Description = 'Remittance & Cheque', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 2, @PLPaymentDocumentTypeID = 1, @PaymentGroupNumber = 1, @Description = 'Remittance Only', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 3, @PLPaymentDocumentTypeID = 2, @PaymentGroupNumber = 2, @Description = 'Cheque Only', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 4, @PLPaymentDocumentTypeID = 3, @PaymentGroupNumber = 3, @Description = 'EPayment', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 5, @PLPaymentDocumentTypeID = 4, @PaymentGroupNumber = 4, @Description = 'EPayment Remittance', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 6, @PLPaymentDocumentTypeID = 5, @PaymentGroupNumber = 5, @Description = 'Email Remittance', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 7, @PLPaymentDocumentTypeID = 6, @PaymentGroupNumber = 6, @Description = 'EPayment Email Remittance', 
	@PostToLedgerOnPaymentRun = 1, @UseThisWhenSinglePaymentGroup= 0, @SummarisePayments = 0, @IsSagePaymentsGroup = 0

EXEC #spCreateOrUpdatePaymentGroup
	@PLPaymentGroupID = 100, @PLPaymentDocumentTypeID = 0, @PaymentGroupNumber = 100, @Description = 'Default Supplier Payment Group', 
	@PostToLedgerOnPaymentRun = 0, @UseThisWhenSinglePaymentGroup= 1, @SummarisePayments = 0, @IsSagePaymentsGroup = 0
	
-- Link the suppliers back to their original document type
UPDATE PLSupplierAccount
SET PLPaymentGroupID = SG.PLPaymentDocumentTypeID + 1	-- All group IDs are now hardcoded to the document type + 1
FROM PLSupplierAccount SA
INNER JOIN @SUPPLIER_GROUPS SG ON SA.PLSupplierAccountID = SG.PLSupplierAccountID

-- Remove any payment groups not required now
DELETE FROM PLPaymentGroup WHERE PLPaymentGroupID NOT IN (1, 2, 3, 4, 5, 6, 7, 100)

COMMIT TRAN

TIP: Make sure that supplier accounts are attached to the correct payment group after running the above script.

NOTE: In some circumstances, having run the above script, you may have duplicate entries in the PLPaymentGroup table, remove these if there are, it should be easy to spot based on the DateTimeCreated field.

2. Run the simple script below to prevent the system loading the configutation assistant when accessing the database once in the 200 Standard software.

UPDATE SYSConfigAssistant
SET TileStatus = 3

3. Run the drop users script detailed here.

CAUTION: It is imperative that the above script is run to prevent issues creating the database.

4. In SQL Server Management Studio, right click the databse you are needing to migrate and navigate to Tasks > Export Data-tier Application and save to a location on the PC.

5. Log into the Sage Provisioning Portal website and access the customers site page and select the Bacpacs button, as below -

6. Select the New bacpac button and browse to the location of the saved .bacpac file created giving it a meaningful description and click Save, this will upload the file to the site.

7. As long as the customer has a spare company licence, when opening the 200 Standard application and logging in, you should be presented with a box like below -

Select the Restore Bacpac option and select the database you uploaded in the dropdown box and enter a company name.

TIP: Try to stay away from special characters in the name such as (&'@# to avoid issues creating the database or entering a space at the end of the name as this causes issues with opening the spooler.

Click OK to start the database creation (this can take over an hour to complete).

Once this is complete you should be automatically logged into the new company database. From this point make sure there is thorough user acceptance testing carried out to be sure that there are no issues in areas the customer will use.