Loading Image Files into SQL Server Modeling Services
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
You can load Microsoft code name “M” models into the SQL Server Modeling Services database by using Mx.exe. Mx.exe installs with the SQL Server Modeling CTP. It can be accessed from the Microsoft SQL Server Modeling CTP Command Prompt.
To load “M” models into the Modeling Services database, you must first compile an image file by using M.exe. For more information, see Compiling "M" Files.
Installing Image Files
To load an image file into a database, you first need an instance of SQL Server, a target database, and login credentials. Because Mx.exe defaults to integrated security, you do not need to explicitly specify the authentication parameters if your SQL Server permissions also use integrated security. All examples in this topic assume the use of integrated security. Other connection options can be specified with the trustedConnection parameter.
The most basic Mx.exe load command uses the install command followed by an image file name and a database option. The following command loads the Person.mx image file into the Repository database on the default SQL Server instance.
mx.exe install Person.mx -database:Repository
Note
A common task is to reload a “M” model that has changed. Certain changes to the model may cause the schema in the database to be incompatible with the new version of the model in the image file. In those cases, you have a choice to force the load of the schema, or you can recreate the target database. Both of these choices delete the database objects for the model, which would delete any data that has been stored in the schema. For more information about these options, see Updating Models in the Database (SQL Server Modeling CTP).
Resolving Dependencies
Images can also have dependencies; this is useful in building larger models, which can be developed independently. For example, consider a “M” source file that exports a Person type and a People extent.
module Contact
{
export Person;
export People;
type Person
{
Id : Integer32;
Name : Text;
} where identity(Id);
People : { Person* };
}
You can compile this “M” source into an image file named Person.mx. A separate “M” source file can use the exported types and extents from the Person.mx image file. The following example shows a Friends extent that references the Contact.People extent. For the purposes of this example, this code is saved to a Friend.m file.
module SpecialContact
{
import Contact;
type Friend
{
Id : Integer32;
Person : Person where value in Contact.People;
Anniversary : Date;
} where identity(Id);
Friends : { Friend* };
}
First compile Person.m into an image file, Person.mx. Then compile the Friend.m file by referencing the Person.mx image. This process is shown in the following “M” Compiler commands.
m.exe Person.m -target:TSql10 -package:Image
m.exe Friend.m -t:TSql10 -p:Image -reference:Person.mx
Now there are two image files, Person.mx and Friend.mx. To load the Friend.mx image, you must reference the Person.mx file as well because of the dependencies. Assuming that both “M” image files are in the same directory, either of the following two commands successfully loads the Friends.mx file by resolving the references to Contact.Person and Contact.People in the Person.mx image file.
mx.exe install Friend.mx –recurse:*.mx –database:Repository
mx.exe install Friend.mx –recurse:Person.mx –database:Repository
Specifying Target Databases
There are several options for specifically targeting the correct SQL Server instance and database. The following example uses the server parameter to specify a specific instance of SQL Server named RepServer1.
mx.exe install Person.mx -server:RepServer1 –database:Repository
The database option specifies a database target. The previous example instructs Mx.exe to install the image file into the Repository database on the RepServer1 server.
You can also use Mx.exe to create the target database. The following example creates a new database by using the create option.
mx.exe install Person.mx -server:RepServer1 -database:RepositoryNew -create