 |
|
|||||||
 |
   
|
|
||||||
|
|
|||||||
|
|
|
||||||
| Vendor | Microsoft |
| Tool Name | SQL Server Database |
| Tool Version | 7.0 to 9.0(2005) |
| Tool Web Site | http://msdn.microsoft.com/en-us/library/bb418493.aspx |
| Supported Methodology | [Database] Data Store (Physical Data Model) via JDBC API |
Import tool: Microsoft SQL Server Database 7.0 to 9.0(2005) (http://msdn.microsoft.com/en-us/library/bb418493.aspx)
Import interface: [Database] Data Store (Physical Data Model) via JDBC API from Import bridge: 'JdbcMicrosoftSqlServer' 6.0.3 - Nov 12 2008 19:32:53
IMPORTING FROM A MICROSOFT SQL SERVER DATABASE USING JDBC.
This bridge establishes a JDBC connection with a physical database in order to extract the physical metadata. It is critical that the parameters are filled correctly for the local connection requirements on the client workstation that is running the bridge. Please refer to the individual parameter tools tips for more detailed examples by database type.
| Parameter Name | Description | Type | Values | Default | Mandatory |
| URL | A MS SQL JDBC database connection URL has the following syntax: [jdbc:sqlserver://]serverName[\instance][:port][;property=value[;property=value]] where: jdbc:sqlserver:// (Optional) is known as the sub-protocol and is constant that will be added to your URL automatically if it is missing. - serverName (Required) is the address of the server to connect to. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. This can also be specified by using the serverName and instanceName named value pairs, but duplicates are not allowed. - instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made. - port (Optional) is the port to connect to on serverName. The default is 1433. If you are using the default, you do not have to specify the port, nor its preceding ':', in the URL. This can also be specified by using the portNumber named value pair. Note: For optimal connection performance, you should set the portNumber when connecting to a named instance. This will avoid a round trip to the server to determine the port number. - property (Optional) is one or more option connection properties. For more information, see Setting the Connection Properties. Any property from the list can be specified. Properties can only be delimited by using the semi-colon (';'), and they cannot be duplicated. Connection Examples: - Connect to the default database on the local computer: jdbc:sqlserver://localhost localhost - Connect to a named database on a remote server: remoteHostName;databaseName=AdventureWorks - Connect on the non-default port 4000 to the remote server: remoteHostName:4000;databaseName=AdventureWorks - Connect specifying a customized application name: localhost;databaseName=AdventureWorks;applicationName=MyApp Named and Multiple SQL Server Instances SQL Server 2000 and SQL Server 2005 allow for the installation of multiple database instances per server. Each instance is identified by a specific name. To connect to a named instance of SQL Server, you can either specify the port number of the named instance (preferred), or you can specify the instance name as a JDBC URL property or a datasource property. If no instance name or port number property is specified, a connection to the default instance is created. See the following examples: - To use a port number, do the following: localhost:5555;user=MyUserName;<more properties as required> - To use a JDBC URL, do the following: localhost\instance1 - To use a named property, do the following: localhost\instanceName=instance1 Escaping Values in the Connection URL You might have to escape certain parts of the connection URL values due to the inclusion of special characters such as spaces, semicolons, and quotation marks. The JDBC driver supports escaping these characters if they are contained between braces. For example, {;} escapes a semicolon. Escaped values can contain special characters (especially '=', ';', '[]', and space) but cannot contain braces. Values that must be escaped and contain braces should be added to a properties collection. Note: White space inside of the braces is literal and not trimmed. Connecting with IPv6 Addresses The JDBC driver supports the use of IPv6 addresses with the connection properties collection, and with the serverName connection string property. The initial serverName value, such as jdbc:sqlserver://serverName, is not supported for IPv6 addresses in connection strings. Using a name for serverName instead of raw IPv6 address will work in all cases in the connection. The following examples provide more information. To use the serverName property jdbc:sqlserver://;serverName=3ffe:8311:eeee:f70f:0:5eae:10.203.31.9\instance1 To use the serverName property jdbc:sqlserver://;serverName=3ffe:8311:eeee:f70f:0:5eae:10.203.31.9\\instance1 |
STRING | localhost | Yes | |
| User | The database user name on whose behalf the connection is being made. Important note about permissions: This bridge reads the metadata for a database, rather than the data. Hence, permission assignments need to be quite different than for a standard database user. The bridge requires access to the system tables, not the actual data tables, thus it is highly recommended that one engages their DB Admin staff to ensure proper permissions for the user name entered here. We do not have any specific instructions as to what are the minimal set of permissions required as it varies from database type to database type, as well as from version to version. In order to ensure that the bridge is working properly, one must first import using the bridge with a FULL Sys Admin type user name (all permissions), i.e., the same type of user name that is used to create and maintain the DB. Once the bridge is working properly against a give DB, then the DB Admin staff may create a user with lesser privileges that still produce a successful result. |
STRING | |||
| Password | The database user's password on whose behalf the connection is being made | PASSWORD | |||
| Use catalog constraint | This option can be used as a constraint to import a particular database Catalog. |
BOOLEAN | False | ||
| Catalog | This option can be used as a constraint to import a particular database Catalog. The possible values are: - empty string: Only the Tables without a Catalog will be retrieved, - a name: Only the tables that belong to this catalog will be imported. |
STRING | |||
| Use schema constraint | This option can be used as a constraint to import a particular database Schema. - 'False' : the SQL pattern % (no constraint) is used as a selection criterion (default) - 'True' : use the name provided in the 'Schema' option as a constraint |
BOOLEAN | False | ||
| Schema | This option can be used as a constraint to import a particular database Schema. - empty string: Only the Tables without a Schema will be retrieved, - a SQL pattern: only the tables those schema name matches the pattern will be imported, - a name: only the tables that belong to this schema will be imported. |
STRING | |||
| Import views | Specifies how to import the database Views. - 'As Views' : database Views are imported as Views - 'As Tables' : database Views are imported as regular Tables - 'do not import Views' : database Views are not imported (default) |
ENUMERATED | As Views As Tables do not import Views |
do not import Views | |
| Import system tables | Specifies if the system Tables and Views should be imported or not. - 'False' : system Tables and Views are not imported(default) - 'True' : system Tables and Views are imported |
BOOLEAN | False | ||
| Import stored procedures | Specifies if the Stored Procedures should be imported or not. - 'False' : Stored Procedures are not imported(default) - 'True' : Stored Procedures are imported |
BOOLEAN | False | ||
| Import indexes | Specifies if indexes metadata should be imported or not. Some databases may experience significant delays if this option is set to 'True'. | BOOLEAN | True | ||
| Import synonyms, aliases, nicknames | Specifies if the Synonym Tables, Alias Tables and Nickname Tables should be imported. - 'False' : Synonym, Alias and Nickname Tables are not imported - 'True' : Synonym, Alias and Nickname Tables are imported (default) |
BOOLEAN | True |
| Meta Integration Repository (MIR) Metamodel (based on the OMG CWM standard) |
"Microsoft SQL Server Database" Metamodel |
Mapping Comments |
| Name | Name | |
| Argument | Procedure Column | The stored procedure parameters are stored in the arguments |
| Description | Comments on the column | |
| Kind | Type | The kind of parameter |
| Name | Name | |
| Position | The order in which the parameters are returned by JDBC | |
| Association | Exported Keys | For Access databases, the MsysRelationships table must be readable |
| Aggregation | True if all the attributes in the Exported Key a+C105re in the Primary Key | |
| AssociationRole | Exported Keys | |
| ExtraConstraint | Update Rule, Delete Rule | |
| Multiplicity | Nullable property of the columns of the Exported Key | |
| Source | Based on the multiplicity of each role | |
| AssociationRoleNameMap | Exported Keys | The rolename map is created if the columns in the primary and foreign keys are different |
| Attribute | Table Column | |
| Comment | Comments on the column | |
| InitialValue | Default value | |
| Name | Name | |
| Optional | Based on the nullable property | For Access databases, set to false only if the attribute is in a Unique Index |
| PhysicalName | Name | |
| Position | Position | If position is not provided, the order in which the attributes are retrieved is used. |
| BaseType | Types | |
| DataType | Data Type | See datatype conversion array |
| Length | Size | |
| Name | The name is computed from the datatype | |
| PhysicalName | Name | |
| Scale | Maximum scale | |
| CandidateKey | Index, Primary Key | |
| Name | Name | |
| PhysicalName | Name | |
| UniqueKey | Non-Unique property | |
| Class | Table | of type "TABLE" |
| Comment | Comments on the table | |
| CppClassType | Set to ENTITY | |
| CppPersistent | Set to True | |
| Name | Name | |
| PhysicalName | Name | |
| ClassDiagram | Schema | A class diagram is created for each package and contains all the elements of the package |
| DerivedType | Column | Table column, stored procedure column SQL View column or type |
| DataType | Data Type | See datatype conversion array |
| Length | Size | |
| Name | The name is computed from the datatype | |
| PhysicalName | Name | |
| Scale | Decimal digits | |
| UserDefined | True for Type | |
| DesignPackage | Schema | A Package is created for each retrieved schema. If there is no schema a default package is created. |
| Name | Name | Set to "Schema" if there is no schema or the schema has no name. |
| ForeignKey | Exported Keys | |
| Name | Name | |
| PhysicalName | Name | |
| Index | Index, Primary Key, Exported Keys | |
| Clustered | Index type | true if index type is tableIndexClustered |
| Name | Name | |
| PhysicalName | Name | |
| IndexMember | Index Member, Key Member | |
| Position | Position in the Index or key | |
| SortOrder | Ascending/descending order | |
| Model | Catalog | The model is built using the elements contained in the catalog (e.g. the database for MS SQL server) |
| Name | Name | Set to "Database" if the catalog has no name. |
| SQLViewAttribute | View Column | |
| Comment | Comments on the column | |
| Name | Name | |
| PhysicalName | Name | |
| Position | Ordinal position | |
| SQLViewEntity | Table | of type "VIEW" |
| Comment | Comments on the table | |
| Name | Name | |
| PhysicalName | Name | |
| StoredProcedure | Stored Procedure | The stored procedures are connected to the packages, except for Oracle where System tables are used to connect them to their Class/Table. |
| Description | Comments on the stored procedure | |
| Name | Name | |
| TypeValue | Check Constraint | The CHECK constraints are parsed (Oracle and MS SQL Server databases only) |
| Name | Value | |
| Position | In the order they are set in the constraint string | |
| Value | Value |
Last updated on Fri, 14 Nov 2008 20:26:07
