Troubleshooting
Problem
An IBM i2 iBase database has been upgraded to SQL Server. When next using iBase designer (same build/version as previously installed), to create new entities an error message is reported.
Symptom
The IBM i2 iBase database remains the same version as previously installed but it has been upgraded to become a SQL server database, held on a SQL 2008 R2 machine.
When next using iBase designer (same build/version as previously installed), to create new entities an error message is reported.
Object _Get_SAR does not exist or is not a valid object for this
operation
Where SAR is the name of the new entity being requested/created
A similar message is reported when trying to create a new link
Object _Get_SAR_Link does not exist or is not a valid object for this
operation
After accessing the database through designer a new error appears in
iBase user which also prevents users creating new entities. This error is
Could not find stored procedure _Next_Person_
Where Person is the type of new entity attempting to be created
Cause
For each SQL server iBase database there is an Application role i2iBaseSSE.
The iBaseSSE application role properties default schema should be set to = dbo. (It should not be set to i2iBaseSSE).
If it is not set to dbo - what may have happened?
A SQL server dba may have reviewed the iBase settings, or have an automated policy in place that reduces permissions, removes dbo privileges/ownership; changing it to a lesser schema.
This results in the stored procedure being created against the i2iBaseSSE schema and this is incorrect, causes the problems reported; the first time an entity or link creation is attempted following the incorrect setting of the application role schema.
Also note:
The database role 'db_owner' must have the i2iBaseSSE application role as a role member and the owner of db_owner is dbo
Diagnosing The Problem
1.) Check the iBaseSSE application role properties default schema = dbo.
In SQL server Management Studio, for the specific iBase database affected, under Security, application roles, check the properties of the i2iBaseSSE application role and ensure the Default schema is set to dbo
2.) In SQL server Management Studio, for the specific iBase database affected, under Security, Database roles, check the database role 'db_owner' has the i2iBaseSSE application role as a role member and the owner of db_owner is dbo
Resolving The Problem
1.) In SQL server Management Studio, for the specific iBase database affected, under Security, application roles, change the properties of the i2iBaseSSE application role, set it to dbo
2.) In SQL server Management Studio, for the specific iBase database affected, under Security, Database roles, ensure the database role 'db_owner' has the i2iBaseSSE application role as a role member and the owner of db_owner is dbo.
3.) Locate the 'missing stored procedure', currently held against the i2iBaseSSE schema. It will be visible in SQL Server Management Studio, under the affected iBase database, under Programmability, Stored procedures - at the bottom of the list of stored procedures (which are alphabetically ordered)
4.) Delete the i2iBaseSSE stored procedure.
The next time you access iBase designer it will automatically create the necessary stored procedure (as long as you have already made the necessary changes in steps 1 and 2 above).
NOTE: A SQL server database backup should be made of the affected iBase database prior to making any of these changes to the iBase database. No users should be accessing the iBase database. No database activity should be occurring when these changes are made.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21992483