SQL Server

The project database in Staffmap 4 can be converted from using the default, SQL Compact database, to SQL Server instead. This is generally a one-way conversion. The only way to convert back to SQL Compact is to create a backup copy of the project. In order to convert to SQL Server:

  • The project name must match the database name.
  • The database must be created.
  • The database must be blank.

Once a project has been converted to SQL Server an indication icon will be visible on the project management screen:

Converting to SQL Server

1. Enter the project you'd like to convert and and go to Tools > Project Settings.

2. In the Database Type option, select the Convert button.

3. Enter the SQL Server address.

4. If the SQL Server is local, you can use Windows Authentication to connect. Otherwise, un-check 'Windows Authentication' and provide credentials.

Troubleshooting

Cannot open database

ERROR: Cannot open database "MainProject" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\DefaultAppPool'.

  1. In Microsoft SQL Server Management Studio, under the target database, expand Security, then right click select "New User".
  2. In the New Login dialog, enter 'IIS APPPOOL\DefaultAppPool' as the User name and Login name.
  3. In the Membership section, assign the 'db_owner' role to the IIS user.

Create table permission denied

ERROR: CREATE TABLE permission denied in database 'MainProject'.

  1. In Microsoft SQL Server Management Studio, under the target database, go to Security > Users.
  2. Right click the IIS APPPOOL user, and select Properties.
  3. In the Membership section, assign the 'db_owner' role to the IIS user.