Moving Great Pains to new server (SQL Server) 1

[ghnz]

I have moved the databases from to my new server but I am getting an errors when I try to access them,

FP:Bad Component offset: 80Wind:Fld 14

followed by;

Unhandled script exception:
Illegal address for field 'Account Number' in script 'Setup_Account_Format'. Script terminated.

EXCEPTION_CLASS_SCRIPT_BAD_TYPE
SCRIPT_CMD_COMPONENT


Clearly i am missing some files, can anyone out there provide me with a list of files I need to copy to move a SQL Server GP instance

 

[Dhariwal]

Hi
Have you installed Great Plains and then moved the databases or you just copied the directory and databases.

thanks
Dhariwal

 

[Luvsql]

The easiest way I have found to move the databases to a new server is to configure the new server with the same drive mappings as the old, stop services on old server, copy the DYNAMICS mdf and ldf plus all the company's mdfs and ldfs to the same locations on the new server. Restart services on new server. Then install eEnterprise client and setup ODBC.

If this fails, I use this document from Microsoft:

How to Transfer Great Plains and SQL 6.5/7.0/2000 to a New Server
Issue


Before starting these steps, you will need to have SQL installed on the new server with the same sort order as the server the backups are coming from.

**If you are using SQL 2000, You will want to use the scripts for SQL 7.0 and you will need to add the following line at the top of each script:

set quoted_identifier off

Here are the steps to recreate your setup at a new server:

1. Load SQL and Great Plains on the new computer

2. Run the SM_Capture Logins 70 (SQL 7.0 & SQL 2000) script against the DYNAMICS database on the old server.

SM_Capture_Logins_70
set quoted_identifier off
/*
** SM_Capture Logins_70.sql
**
** Creates table called RecoveryMaster that stores username and password.
** Insert the name and password from the master database for all Dynamics logins.
** This table will be used for recovery on new server.
**
**
*/

/* Always recreate the RecoveryMaster table. */
if exists (select * from DYNAMICS..sysobjects
where id = Object_id('DYNAMICS..RecoveryMaster') and type = 'U')
begin drop table DYNAMICS..RecoveryMaster end
go
CREATE TABLE DYNAMICS..RecoveryMaster (
name char(15) not null unique,
password varbinary(256) null,
DEX_ROW_ID int IDENTITY(1,1) not null )
go


/* Now insert the information */
insert DYNAMICS..RecoveryMaster (name, password)
select name, password from master..sysxlogins
where name in (select USERID from DYNAMICS..SY01400)
and name not in ('sa', 'DYNSA')


3. Make a backup of the DYNAMICS database and company databases.

4. Copy the backup file from the old database to a local drive on the server where Microsoft SQL Server is installed.

5. Create a new DYNAMICS database by using Great Plains Utilities - be sure to use the same number of account segments and digits as before (if not sure, query the SY003001 and SY00302 tables). Create the new company database(s) on the new server using Great Plains Utilities.

6. Setup an ODBC to point to the new server.

7. Restore the database backup file in Microsoft SQL Enterprise Manager.
8. Repeat Step 7 for each database.

9. Run the SM_Drop User_Dynamics against the DYNAMICS database on the new server.

SM_Drop_User_Dynamics
set quoted_identifier off
/*
** SM_Drop Users_Dynamics_65&70.sql
** Drops all users from the DYNAMICS database.
** Drops the DYNGRP and recreates it.
*/
declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */



/*
** Remove all users from the DYNAMICS DYNGRP group and delete the group.
*/
use DYNAMICS
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select "sp_dropuser " + name from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

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
DEALLOCATE G_cursor

/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use DYNAMICS
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end



/*
** Recreate the DYNGRP group in DYNAMICS.
*/
use DYNAMICS
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec (&quot;sp_addgroup DYNGRP&quot
end



10. Run the SM_Drop User_Company against each of your COMPANY databases on the new server.

* Note: You will need to replace %Companydb% with your company database name for this script. SM_Drop_User_Company
set quoted_identifier off
/*
** SM_Drop User_Company_65&70.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/



declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */


/*
** Loop through all company databases, emptying the DYNGRP group.
*/

use %Companydb%
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')

declare G_cursor CURSOR for select &quot;sp_dropuser &quot; + name from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'

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
DEALLOCATE G_cursor

/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/

if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end




/*
** Recreate the DYNGRP group in all company databases.
*/
use %Companydb%
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec (&quot;sp_addgroup DYNGRP&quot
end




11. Run the SM_Create SQL Logins_70.sql (SQL 7.0 & SQL 2000) script against the DYNAMICS database on the new server.

SM_Create_SQL_Logins_70
set quoted_identifier off
/*
** SM_Create SQL Logins_70.sql
**
** Drop and Recreates all Dynamics logins based off of the RecoveryMaster table.
** Adds users to the DYNGRP for the Dynamics database.
**
*/

declare @cStatement varchar(255) /* Misc exec string */
declare @DynDB varchar(15) /* DB Name exec string */
declare @DYNGRPgid int /* Id of DYNGRP group */



/* drops all Dynamics logins. */

use master

declare G_cursor CURSOR for select &quot;if exists (select name from master..syslogins
where name = '&quot; + USERID + &quot;') begin exec('sp_droplogin &quot;
+ USERID + &quot;') end&quot; from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

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
DEALLOCATE G_cursor

/*
** Recreate all Dynamics logins.
*/

use master
declare G_cursor CURSOR for select &quot;sp_addlogin &quot; + USERID + &quot;, NULL, DYNAMICS&quot;
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

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
DEALLOCATE G_cursor


/* Adds users to the DYNGRP in the Dynamics database. */

use DYNAMICS
declare G_cursor CURSOR for select &quot;sp_adduser &quot; + USERID + &quot;, &quot; + USERID + &quot;, 'DYNGRP'&quot;
from DYNAMICS..SY01400
where USERID in (select name from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

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
DEALLOCATE G_cursor

/*
** Update the login passwords with the backed up values from the production server.
** This has to be done in 'allow updates' mode because the syslogins system table
** is being directly updated.
*/

exec sp_configure 'allow updates', 1
exec (&quot;reconfigure with override&quot

exec (&quot;update m
set m.password = rm.password
from master..sysxlogins m, DYNAMICS..RecoveryMaster rm
where m.name = rm.name&quot

exec sp_configure 'allow updates', 0
exec (&quot;reconfigure with override&quot






12. Run the SM_Add Users_Company against each of your COMPANY databases on the new server.

* Note: You will need to replace %Companydb% with your company database name for this script. SM_Add_Users_Company
set quoted_identifier off
/*
** SM_Add Users_Company_65&70.sql
**
** Adds users to the DYNGRP for the Company database.
**
** NOTE: will need to replace %Companydb% with the company database name.
**
*/



declare @cStatement varchar(255) /* Misc exec string */

/*
** Add users back to the appropriate DYNGRP groups for the company database.
*/


use %Companydb%
declare G_cursor CURSOR for select &quot;if exists(select * from
DYNAMICS..SY60100 a,DYNAMICS..SY01500 c,DYNAMICS..RecoveryMaster u
where u.name=a.USERID and a.CMPANYID=c.CMPANYID and c.INTERID=&quot;&quot;%Companydb%&quot;&quot;
and u.name='&quot;+rtrim(USERID)+&quot;')
exec(&quot;&quot;sp_adduser &quot;+rtrim(USERID)+&quot;,&quot;+rtrim(USERID)+&quot;,'DYNGRP'&quot;&quot&quot;
from DYNAMICS..SY01400
where USERID in (select USERID from DYNAMICS..RecoveryMaster)
and USERID not in ('sa', 'DYNSA')

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
DEALLOCATE G_cursor






13. Run the Grant.sql (SQL 6.5) or Grant70.sql (SQL 7.0 & SQL 2000) script against the DYNAMICS and ALL COMPANY databases on the new server.

Grant70.sql

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on ' + convert(varchar(64),name) + ' to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1

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
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on ' + convert(varchar(64),name) + ' to DYNGRP' from sysobjects
where type = 'P'

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
DEALLOCATE G_cursor


14. Run locks.sql in Query Analyzer to recreate the dex_lock and dex_session tables in the tempdb database. You can find this script in Windows Explorer in Great Plains folder|SQL folder|Utils folder|Locks.sql.

15. Load any third party products or additional products (such as Human Resources and Fixed Assets, etc.) that are needed at the new server.

 

[Luvsql]

The easiest way I have found to move the databases to a new server is to configure the new server with the same drive mappings as the old, stop services on old server, copy the DYNAMICS mdf and ldf plus all the company's mdfs and ldfs to the same locations on the new server. Restart services on new server. Then install eEnterprise client and setup ODBC.

If this fails, I have a document from Microsoft on how to copy databases and sql logins.