Could not create constraint error trying to create a SQL table with Dexterity

Question:
I'm trying to use the open table/close table method to create my new SQL table with Dexterity. However on the 'open table' statement, this SQL error is returned:


"An open operation on table xxx failed accessing SQL data."When I click on More Info, it says "[Microsoft][ODBC SQL Server Driver][SQL Server] There is already an object named PKADSY4997 in the database. [Microsoft][ODBC SQL Server Driver][SQL Server] Could not create constraint. See previous errors."


The physical name of my table is PKADSY4997 which doesn't exist nor do any of the zDP stored procedures for this table. What could be causing the error?


Answer:

In this case, the table had been created and dropped previously but for some reason a constraint on the table had not been removed.


In Query Analyzer, we ran this query:


select * from sysobjects where name = 'PKADSY4997'


The id returned was 151193332 and the parent_obj was 173243672.


Looking at the parent_obj column, we next ran the select query against the parent object and couldn't find one. So this was an orphaned constraint as we expected.


At that point, we needed to delete the orphaned constraint. But first, a setting needed to be changed.


In the SQL Server Properties window, there is a checkbox that must be marked to allow this constraint to be deleted- Allow Modifications to be directly made to the system catalogs.


Now from Query Analyzer:


delete from sysobjects where name = ''PKADSY4997'


This removed the constraint and the table could be re-created correctly from Dexterity now.








This article was TechKnowledge Document ID:26107