Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP 10 Bulk Copy
Microsoft Dynamics GP (Archived)

GP 10 Bulk Copy

(0) ShareShare
ReportReport
Posted on by Richard Wheeler 75,788 Moderator

I need to change the collation  sequence of a SQL GP instance from case sensitive to case insensitive. When I take the output of the CopyOut script

 /* Script to create bcp commands to export data for all tables. */
SET QUOTED_IDENTIFIER OFF
select 'bcp "TWO..' + name + '" out ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"'
from sysobjects where type = 'U' order by name

it creates these bcp commands:

bcp "TWO..MSreplication_options" out MSreplication_options.out -e MSreplication_options.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"
bcp "TWO..spt_fallback_db" out spt_fallback_db.out -e spt_fallback_db.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"
bcp "TWO..spt_fallback_dev" out spt_fallback_dev.out -e spt_fallback_dev.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"
bcp "TWO..spt_fallback_usg" out spt_fallback_usg.out -e spt_fallback_usg.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"
bcp "TWO..spt_monitor" out spt_monitor.out -e spt_monitor.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"
bcp "TWO..spt_values" out spt_values.out -e spt_values.err -c -b 1000 -U sa -P Password99 -t "|" -S HHHN -r "#EOR#\n"

 

When I run these bcp commands I get invalid object name on MSreplication_options, spt_fallback_db, etc. I have tried looking for these objects in the TWo database and I cannot find them. I am thinking it is the case of the text that is wrong. Can anyone tell me where I would find these objects?

*This post is locked for comments

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    Re: GP 10 Bulk Copy

    The collation sequence needs to be changed from Latin General Bin to Latin1_General_CP1_CI_AS. This GP database was born back in the day of SQL Server 7.0.

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    Re: GP 10 Bulk Copy

    Hi Richard,

    My 2 cents to this : why do you try to move your databases from SQL 2005 to 2012 with a bulk copy ? why don't you just take a full backup of all your company DB's and DYNAMICS DB, and restore them on the new SQL 2012 ? or did I missunderstood your intention ?

  • Suggested answer
    John Lowther Profile Picture
    John Lowther 5,122 on at
    Re: GP 10 Bulk Copy

    Yes, you are exactly correct. That would also explain the confusion over the tables showing up that should not have.

    And as you found that, you probably already know, but just to make double sure, I am assuming that you changed the "TWO..' part of your export script to reflect the actual name of each database as you exported each one.

    [If this post helps to resolve your issue, please click the "Mark as Answer" or "Helpful" button at the top of this message. By marking a post as Answered, or Helpful you help others find the answer faster.]

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    Re: GP 10 Bulk Copy

    John, we are on SQL 2005 going to SQL Server 2012. The problem turned out to be when you run the Create Bulk Copy Out script you need to be in each of the company databases. I have the data copied out and am now preparing the 2012 Server. I am going to install GP 2010 and SQL Server 2012, create all the company databases, run the TRUNCATE table scripts on all the company databases plus the DYNAMICS database and then run GP Utilities. Hopefully it will just see the GP 10 tables and then say it is time to upgrade. All this is being done on a set of test virtual servers. I do not believe replication is possible with GP databases due to the use of identity columns.

  • Verified answer
    John Lowther Profile Picture
    John Lowther 5,122 on at
    Re: GP 10 Bulk Copy

    While I have the typical questions like what version of SQL are you on, what service pack, etc, my first question has to be do you have replication setup? The reason I ask it that the tables "MSreplication_options", "spt_fallback_db", "spt_fallback_dev", "spt_fallback_usg", "spt_monitor", "spt_values" are all table used in replication. The part that somewhat confuses me is that on SQL Server 2008 R2 these tables are in the Master database not in an application database, however, as I can not remember is maybe that at some point in the past these tables, or tables with the same names where in the application database that was being replicated.

    If you are replicating the database you will most likely need to stop and remove all of the replications before trying to scrypt the database out.

    If you are not using replication at all a couple of options are:

    (1) Use the "sp_dropdistributor" with the parameter @no_checks = 1 as in  "exec sp_dropdistributor @no_checks = 1". This should get rid of all the replication information.

    Or (2) As a test, note I really mean test, try removing those six lines and see if the script then works.

    Please keep in mind that it has been a long, long time since I work with replication so please take all of this information as suggestions only, I could easily be wrong.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,432 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans