If you feel there is a question and/or answer that would benefit other clients, please feel free to drop Synergetic Support an email

Where should I start?

A: Most likely in this order

This page
Very general notification about version 68 Synergetic v68 Gold Release
Introduction, specifically targeting Single DB specific changes (not including other general enhancements to the system) v68 - Single Database
1. Note, down lower on the v68 - Single Database page, this contains an attempt to group pages for your specific needs, e.g. As a System Administrator

Error messages

You are not authorised to access the financial schema. Please contact your System Administrator.

A: Financial Schema Access

I can't access the Synonym databases

After the upgrade, Crystal reports and MS Query objects should continue to work seamlessly.

The Synonym databases are just a pointer to the database objects in the Single Database, so access is dependant on the permissions at the Single database.

To troubleshoot, login to the synonym database with the MS Query credentials and try to run your query directly against this.

If you get the following error there may be an issue with the migration of permissions.

Msg 916, Level 14, State 1, Line 1
The server principal "{domain}\{user}" is not able to access the database "SynergyOne" under the current security context.

Also check for the Database role of zSynergeticSynonymAccess in the synonym database- if that does not exist then there is an issue.

Please contact us immediately to assist with resolution.

Other issues may be due to complex custom objects - see My custom objects (egTables,Views,StoredProcs,Functions)have not migrated across

The synonym database can be easily droped and recreated if necessary- contact us if you have any questions about this.

I'm a Synergetic Administrator but I don't have permissions

There have been some instances where Synergetic Adminstrators have not had comparable permissions migrated to the Single Database instance.

When accessing Group/User Security Maintenance to give SQL Server Object Permissions you get the error:

"User username does not have enough underlying SQL privileges to perform this function."

Synergetic administrators need a higher level of permissions in order to grant permissions within the Synergetic software.

In the contained database (ie. Synergetic_AUVIC_SYNERGETIC_PRD - not at the server level!) give the relevant database user access to the db_owner role.

My custom objects (eg Tables, Views, Stored Procs, Functions) have not migrated across

A: Some database objects may not have migrated to the v68 single database, generally due to the way these have been written or where there are invalid object references.

User stored procedures for use with Crystal Reports (prefixed with 'ucrsp...') were not being given the appropriate permissions correctly until build v68.27

If you have Crystal Reports using a user stored porcedure, please follow the process detailed in the link below.

User stored procedures that are not prefixed with 'ucrsp...' will NEVER be granted permissions as part of the v68 upgrade process- if required these permissions will need to be granted individually as required.

These can be scripted out of the zPre68 databases, reviewed, and then played as required in the new single database.

These may require permissions to be granted.

Please review User Objects created for use in Single Database for more information on how to address these issues.

If you have custom logic generating emails, please refer to how to effectively generate the equivalent of a DBMail.

Contact Support if you are unable to resolve internally.

I have users reporting they are not receiving emails

You may have custom mail profiles - please refer to Mail profiles - ErrorMessage 'profile name is not valid'.

If you have custom logic generating emails, please refer to how to effectively generate the equivalent of a DBMail.

I'm getting error with reports running in the Community Portal

Please review User Objects created for use in Single Database for more information on how to address these issues.

What is Octopus Deploy?

A: See Installing and upgrading, containing document Automated Deployment - Octopus Deploy.pdf

Are there any known issues (apart from the bug list)?

A: Single Database - Known Issues

Can the database name be changed?

A: The naming syntax is predefined and is intended to be one of many steps to incorporate the databases in to a cloud hosted solution.

If you have concerns about the database name please contact the Development Manager.

v68 user accounts now appear in 3 places; Server Level, SynergyOne and Synergetic_{region}_{client}_PRD and passwords can be reset in each of them. How do I manage passwords?

A. All user accounts should be managed at the database level. User accounts (and passwords) are not required for the synonym databases.

Server level user accounts are not required for Synergetic v68 functionality and, while these were retained as we are unable to determine if they are otherwise used elsewhere, they should be removed. (Other than accounts with a 'ServerLogin' prefix, ie. zSynergetic_AUVIC_CDA_68PRD_ServerLogin)

Creating users in Synergetic v68

Detailed information is available at Creating users in Synergetic v68.

To log in to your Synergetic single database make sure you enter the single database name in the Connection Properties tab in SQL Management Studio.

Granting users access to SQL objects for MSQuery or Crystal Reports

A number of clients had given users full database level access via the 'db_datareader' permission.

This is not recommended and these permissions will not be migrated as expected with v68.

See page Granting access to SQL objects

I receive an error message when accessing a maintenance program (e.g. Current Student Maintenance) like: The server principal "zSynergetic_main_dbo" is not able to access the database "SynergyOne" under the current security context.

A: This would most likely be due to custom code. Some possible checks are:

What is the legacy ODBC prompt I receive when logging in to Windows Synergetic?

A: Legacy ODBC Entries

Crystal Reports issues with Single Database

My site report doesn't run in v68

There can be issues with reports created in earlier versions of Crystal Reports which can be resolved. See Crystal Reports have locked SQL (from old version) & don't run in v68.

Site reports should run by default from within the Synergetic application, regardless of the existence of any specific ODBC entries.

If they don't and this does not resolve your issue please review contact us ASAP.

If you plan to modify any reports, you will need to reset the data source location as per Changing reports Database source to new Single Database Source

Config.Value issue

Where there are references to the dbo.Config table, table mismatches may occur. This is because we have removed resrictions on the Value field (ie. made this VARCHAR(MAX)). This has been observed to have a flow on effect in Crystal Reports where, when setting datasource location against the single database, the Value field is not able to be matched. This is because of a behaviour in Crystal Reports where an unrestricted text field is considered a memo field, and there is no ability to map between the two. https://archive.sap.com/discussions/thread/3647546

We are currently refactoring this config table to make it a view to preserve backwards compatibility as it is frequently used in reports. However, if legacy reports are verified against the current view, then while this will work, the school name will 'drop off' the report footer. The school name can be manually added with minimal effort.

*EDIT* This has now been addressed on DSY-11275 for builds on or greater than v68.33 and v69.05.

I can't see tables/views/stored procedures when in the Database Expert

Please refer to Unable to see all database objects in Crystal Reports

My report is not working in the Community Portal

Please refer to User Objects created for use in Single Database#ObjectsreferencedinCrystalReportsrunviaCommunityPortal

I am having issues running standard Crystal Reports

Check to make sure the Use Reserved ODBC Entry for Crystal Reports has been checked, and the value 'SynergeticReserved' has been entered.

I have an unhelpful Crystal Reports error running a finance report

When running (specifically) a finance site report an error is generated. This occurs even though the database source has been updated to the single database.

Details: [Database Vendor Code: 4121 ] (Code: 723)

This issue is due to a SQL Expression Field being used.

SQL Expression fields are raw SQL and cannot be updated with existing processes when updating the database source.

The issue will likely be that there is a reference to "dbo.fieldname"- where this used to work when there was a database per finance entity, this is not compatible with the Single Database.

To rectify, remove any "dbo" prefix.

Granting users access to SQL objects for Crystal Reports

A number of clients had given users full database level access via the 'db_datareader' permission.

This is not recommended and these permissions will not be migrated as expected with v68.

See page Granting access to SQL objects

DocMan Bulk Loader

Scripts for v68 are available at DocMan Bulk Uploader

A v68 version of the bulk loader is at http://support.synergetic.net.au/Downloads/other/DocMan_Bulk_Loader/

Issues with backup to test/DR environments

There are a few things that you need to consider if you are backing up a production environment to a secondary test/sandbox/DR environment.

With v68 there is a modified approach to Restoring & Renaming Single Database

Read more potential gotchas at Backing up to a test environment.

Staff can't access finance data (including payslips in kiosk)

If there are staff that are unable to access payslips in the SynWeb staff kiosk, it may be that they do not have access to the main finance database.

In this event, the resolution is to:

Identify a relevant group the staff member is a part of, and
Tick the Allow Access to Main Finance option

MS Query

I can't add/change criteria in MS Query

Given there are no table or view objects in the synonym database this is not unexpected.

This will be a limitation in while queries will continue to work, you will be unable to use interfaces such as the change criteria.

However you can manually modify the SQL (using the SQL button) to change queries as required.

If you will be changing queries, you will want to point these queries to the single database anyway as that will need to occur at some point.

At some point in the future (well ahead of now!) we expect that we will look to deprecate the synonym databases.

While an older article, Switching Data Sources in Excel 2007 describes how to do this process.

While technical, it is simply the process of changing the text in the following dialog.

Remove the "SynergyOne." prefix, and where there is a reference to "SynergyOneFinance.dbo." change this to "finance."
Change the Connection string property for the DSN to the Single Database ODBC- this will be called "Synergetic"

You may also have to do reference your specific database if this is not hard coded in your ODBC.

To do this add a semi-colon in the Connection String and add "Database=Synergetic_AUVIC_ClientCode_Live" (or whatever the specific database you are using is).

Users don't seem to have access to tables and views in MS Query

Many Synergetic clients require some staff to have access to database objects.

These usually include table and views, but may also include more complex objects such as stored procedures.

In some instance, uses appear to have been given broad access to all objects in the entire database. (As required for system administrators)

As this was often done due to unclear instructions on how to otherwise give users this access, these permissions have not been brought through during the single database migration.

In order to provide effective security and control around access to database objects, security groups must be created and given the appropriate access to relevant objects.

More information on this can be found at Granting access to SQL objects

Granting users access to SQL objects for MS Query

A number of clients had given users full database level access via the 'db_datareader' permission.

This is not recommended and these permissions will not be migrated as expected with v68.

See page Granting access to SQL objects

Switching between multiple environments

Some clients may develop Excel-based MS Queries against a test environment and then look to use them on a production environment.

When you run the Windows Synergetic application, the local ODBC is updated to reflect the instance being run.

Where MS Query has been used in Excel, a DATABASE value with the database name is stored in the connection string.

As a result, the following scenario will take place:

you run a test instance which updates the local ODBC
you develop an Excel MS Query which uses the local ODBC, however this saves the DATABASE value in Excel
you ran a production instance which updates the local ODBC
you open and refresh the MS Query, while expecting the production instance ODBC to be in effect, data from the test instance is still being displayed

The workaround for this is to remove the DATABASE value in the connection string which will then result in the current ODBC default database to then be used.

However the best approach is to develop any queries against the production environment.

An alternate approach may be to have both (multiple) test and production instances of any Excel-based MS Queries.

Other untested approaches may also be suitable, such as this one identified in the MSDN forums or this one in the Microsoft Office TechCenter forums.

Why can't I access my old SynergyOne databases?

When a v68 upgrade is conducted, the legacy SynergyOne databases are renamed with a 'zPre68' prefix.

In most client environments, these legacy databases are taken offline. This is done so that they will not form part of the scheduled database backups and cause an excessive amount of drive use.

These databases can be easily brought back online (or taken back offline) through SQL Server Management Studio using the following steps:

Right click on the database
Select Tasks
Select Take Offline (or Bring Online)
Select OK

Offline databases will visually display as '(Offline)'.

If you are looking at server level users who have permissions against any databases that are offline, you will get the following pop up notification. (Login Properties > User Mapping)

This will not show if the databases are brought back online.

There are users missing from the single database

Some clients have reported that users have not been migrated- this can be because of several reasons.

Firstly, this may occur due to a mismatch between User name and Login name.

SQL to identify affected logins

SELECT
  DatabaseUserName = su.name,
  DatabaseLoginName = sl.name
FROM sys.sysusers su
  INNER JOIN sys.syslogins sl ON
  (sl.sid = su.sid)
WHERE
  sl.name <> su.name COLLATE database_default
  AND su.name NOT IN ('sa', 'dbo')

Ideally this needs to be addressed before the migration is conducted.

If related issues are identified within a short time of the migration, you are able to update the affected logins and either you can re-migrate these to the single database or we can assist. This will only create users that do not already exist.

This will require you to:

Open SQL Server Management Studio
Bring the zPre68... databases online
Go to the affected login at the server level, enter the proprieties for the user and select the User Mapping option, then update the User column in the zPre68... database/s the user is authorised to
Run the following script (in the single database) C:\SynUpd\DatabasePatcher\SingleDBConvert\60 Add Z System Users and Copy Users and Roles\02_MigrateDatabasePrincipalsToSingleDB.sql
1. There will be a number of messages here- don't be alarmed!
Confirm that you can see the user in the single database and the appropriate permissions are in place
Take the zPre68... databases offline

Please communicate via your SYS ticket if you require assistance in this case.

Secondly, the pre-checker may identify issues with accounts with an old password format- these will be called out under the heading "Users with old password format have been found. These will not be converted to contained users" and present a list of affected users.

Again, this ideally needs to be fixed prior to any production migration.

If either of these instances occur and are identified post-migration, any missing users may need to be manually created in the single database.

In this case Securables will also need to be manually associated with the login created.

Please see Creating users in Synergetic v68 for more details.