SQL Scripts SQL Server and MSDE


Before you set up EA for use with SQL Server, we recommend you run the Tools | Data Management | Data Integrity tool on the base project you wish to upsize to SQL Server. This will ensure data is 'clean' before uploading.

 

Warning: Before proceeding, ensure MDAC 2.6 or 2.7 is installed on your system.

 

 

Upsizing Your Database

There are three stages to upsizing your database for SQL Server. Follow them in order:

 

Stage One: Create an Empty Database

 

1. Install SQL Server.

 

2. Create an empty database.

 

Note: See Create a New SQL Server Repository.

 

 

Stage Two: Configure the Database

1. Using a tool such as the SQL Query Analyser, load the SQL Server - Base Model.sql file. This is available to registered users on the Corporate Edition resources page of the Sparx website at http://www.sparxsystems.com/registered/reg_ea_corp_ed.html.

 

2. Make sure the new database is the currently active database.

 

3. Run the script to create all required tables/indexes etc.

 

 

 

Note: See Create a New SQL Server Repository.

 

 

Stage Three: Transfer the Data

 

You now have an empty database - you can use the Tools | Data Management | Data Transfer menu option in EA to transfer an existing model into the server.

 

1. Open EA (you can press Cancel at the Open Project screen to open with no project loaded).
 
2. From the Tools | Data Management submenu, select Data Transfer. This will open the Full Model Data Transfer dialog.

 

transferData

 

 

3. Select .EAP to DBMS as the Data Transfer Type.
4. Enter the name of the .EAP file to upsize to SQL Server as the Source Model.
5. Press the Browse [...] button at the right of the Target Model field. This will open the Datalink Properties dialog.
 
6. Select "Microsoft OLE DB Provider for SQL Server" from the list, then press Next.
 
7. On the Data Source details page of the connection dialog, enter the server name, database name, security details as required.

 

Note: See Connect to a SQL Server Data Repository for more information.

 

8. Press OK.
 
9. If desired, check the Logfile checkbox and enter a path for the data transfer log file.
                               
10. Press Transfer Data to begin the data transfer process.

 

 

Once the process is completed, you will have upsized your model to SQL Server and can now open it from EA.