Wednesday, June 8, 2011

Epicor Replication - Fully-Functional Database

Epicor Replication allows you to replicate one or more tables from Epicor into a seperate SQL database.

Because we use OpenEdge for our Epicor database, we use replication to first export the data to an SQL database, before we import into our data warehouse, or as a source for other applications.

When you select the replicated database, you have two choices, ad-hoc or fully-functional. An Ad-hoc database starts empty, and the replication server will create the table schemas only for the replicated tables. A fully-functional database has all tables, view, stored procedures, etc. predefined, and can act as a read-only database for the Epicor client (or so they say, I haven't tried).

However, there is another, undocumented difference. The tables created in an ad-hoc database are different than the tables predefined in the fully-functional database. Specifically, a Character01 field in an ad-hoc database automatically gets created as an NTEXT column. NTEXT columns can not be used in comparison operations, which can be very restricting.

The same table in a fully-functional database has a Character01 field defined as a VARCHAR, which is much more useful.

So, where do you get a copy of a fully-functional database create script?

In your Epicor installation, find your Epicor905 folder, and then go to Epicor905\db with install\newdb\Epicor90564.sql.