Introduction
This article applies to FME Flow Distributed Installations using SQL Server or Express Installs that are reconfigured to use SQL Server as the FME Flow Database. This article is for environments that do not allow the default schema to be used (dbo) and must use another schema name.
Our documentation instructs the FME Flow Administrator (or DBA) to run SQL scripts against the SQL Server Instance that creates the database, login, and user as well as the tables and indexes. See the documentation.
Scripts
If you run the scripts as is, they will do the following:
- Create a database called 'fmeflow'
- Create the FME Flow tables in the database called fmeflow, in the default schema dbo.
- Create a login called 'fmeflow'
- Create a user called 'fmeflow' with a password.
FME Flow admins and SQL Server DBA's often tweak these scripts to change the username, password, login or database name. Those changes are supported.
However, some company standards require the database objects (tables and indexes) to be created in a schema other than the default dbo. In this case, the shared scripts do not indicate how this can be done or whether FME Flow even supports the schema change. The short answer is yes, FME Flow does support this configuration change; however, it is necessary to alter the user and set the default schema to the new schema value.
The statements are:
CREATE SCHEMA [<schema>] GO ALTER USER <username> WITH DEFAULT_SCHEMA = <schema> GO
CREATE SCHEMA [fmeflow] GO ALTER USER fmeflow WITH DEFAULT_SCHEMA = fmeflow GO
As mentioned, editing the existing scripts and adding the above lines isn't enough. It won't quite work that way because the schema will not exist until the database can be created.
So you'll need to edit the scripts and move the create database out of the sqlserver_createDB.sql script and into the sqlserver_createUser.sql script. Using the following sequence may help:
- As DBA: Create the new 'fmeflow' database
- As DBA: Create schema, login, and user within the new 'fmeflow' database.
- As the new 'fmeflow' database User, log in and run the sqlServer_createDB.sql (with the 'create database' line commented out). This creates the objects in the new schema.