Kbase P134656: Progress DB to MS SQL Server utility (ProtoMSS) does not pull any table definitions from the SQL Ser
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
23/12/2008 |
|
Status: Verified
SYMPTOM(s):
Progress DB to MS SQL Server utility (ProtoMSS) does not pull any table definitions from the SQL Server 2005 Database when used other than sa user
Received informational message "There are no objects in the MSS database that satisfy the selected criteria. (Remember: some data sources are case sensitive.) Reenter preselection criteria?"
Clicking yes redisplays the informational message above.
clicking no shows following message:
Run pro -p c<Schema holder name>.p to connect to new MSS Database
Clicking ok on the above message returns to the Data Admin window.
FACT(s) (Environment):
The user performing the ProToMSS has System Administrator server role in SQL Server.
Progress DB to MS SQL Server utility pushes all the tables to SQL Server Database with "dbo" as owner.
There are no user tables in the schema holder
MS SQL DataServer
Windows
MS SQL Server 2005
CAUSE:
When the user who performs the ProToMSS had System Administrator Server role or has been defined as database owner, SQL Server creates the objects with an owner of "dbo". When the ProToMSS utility searches for objects to pull into the schema holder, it uses the user-id of the user running the utility in it's search criteria. Since the object are owned by"dbo" no objects can be found to pull.
FIX:
Use the following steps on the SQL Server 2005 to appropriately assign the empty database to the specific user:
1. First, create an empty Database (you already may have created the DB on the SQL Server 2005)
2. Create a user you like to use from the OpenEdge (you may already have the user created on the SQL Server 2005)
3. Go to the properties for the user which opens up the Login Properties - <user name> windows
4. Select User Mapping page on the user properties and select the empty database created in step 1 on the "User mapped to this login:"
5. Change the "Default schema" from "dbo" TO the user created on the step 2, and check the "db_ddladmin" database role membership for the user (Do NOT uncheck "public" role)
6. Click OK to close the property window
7. Create a system DSN with SQL Server authentication, and change the default database to the one created on step1, and test the connection
8. Perform the protoMSS
You can also use one of the following workaround to resolve this issue:
- Use default dba sa
Or,
- After the above error
o connect to the schema holder that was created by the protoMSS process
o use DataServer > MS SQL Server Utilities > Update/Add Table Definitions tool to pull the tables from SQL Server DB
o on the pre-selection criteria screen, change owner to "dbo" (FYI: This is where is it failing during the pull process of protoMSS utility)
o connect to Progress/OE Database that is migrated to MS SQL server DB
o use DataServer > MS SQL Server Utilities > Schema migration Tools > Adjust Schema... tool to adjust the schema holder to add Progress/OE table/field level triggers, tables'/fields' names, description, display format, etc.