Kbase P142202: Receiving various sql errors when using sqlexp with a -infile that was created via sqlschema -g opti
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/6/2009 |
|
Status: Unverified
SYMPTOM(s):
Trying to migrate sql permissions from an existing database into a new freshly dumped and loaded copy of that database and receiving errors.
Getting several sql errors when loading a .dfsql file that was produced as a result of the sqlschema -g command
sqlexp dbname -H hostname -S portnumber -infile .dfsql fails
The .dfsql file contains grant rights for all tables within the database.
SQLState=42000
ErrorCode=-20003
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error (7587)
SQLState=42S02
ErrorCode=-20005
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Table/View/Synonym not found (7519)
SQLState=HY000
ErrorCode=-20122
[DataDirect][OpenEdge JDBC Driver][OpenEdge] procedure not found (7678)
Some of the permissions contained within the .dfsql file do get granted against the database regardless of the errors.
Created test db and attempted to load original dbname.df file from the database into this newly created database and received errors during .df load.
dbname.e file shows the following error:
** Error during ADD FIELD TestDate **
Invalid Initial value
FACT(s) (Environment):
.dfsql file was created using sqlschema -g command
OpenEdge 10.1B03 Service Pack
HP-UX 11i
ia64 (Itanium)
CHANGE:
Created this new database as a result of dumping and loading existing database.
CAUSE:
One of the fields in the database that is a date field has an invalid initial value that is incorrect for a date field.
FIX:
1. Correct the schema within the database to identify a valid initial value for the date field instead of a field name.
Example:
ADD FIELD "TestDate" OF "table" AS date
DESCRIPTION "R&D"
FORMAT "99/99/99"
INITIAL "TestDate" <--------------------- Wrong value. Should be either a date, today or ?.
LABEL "Testing Date"
POSITION 77
SQL-WIDTH 4
COLUMN-LABEL "TestDate"
HELP "Test Date"
ORDER 2050
2. Then run the sqlexp dbname -H hostname -S portnumber -infile .dfsql to successfully update all permissions and rights within the SQL engine for all the tables contained within that .dfsql file.