SQL developer caused problem

davmac1

We are running a SQL Server 2000 database witrh Navision with defaults enabled. This means every Navision defined field as a default constraint in SQL Server.

I had a SQL Server programmer change the field size of a field in a Navision defined table from 30 to 50 characters using SQL Server Enterprise instead of Navision.
Naturally Navision did not like that.
I changed the field size back in SQL Server and Navision is happy again.
I then tried to change the field size in Navision and received a drop constraint error where it cannot find the constraint.

Does anyone know a fix for this?
Could I create a contraint with the name it is expecting so it will drop it sucessfully or is it more complicated than that?

DenSter

You should stay away from modifying anything directly on SQL Server. In this case though it looks like you won't have a choice.

First of all, you should create a NAV backup, just in case.

I'd probably try to create a new database with your objects, which then would create a new table according to the NAV design. Then script the table you're talking about into an UPDATE script and see if you can update the table.

davmac1

Thanks - I will give it a try.
I have a copy of the database before it was changed and the constraint name it is looking for is not there - I am looking in the sysobjects table.

kine

than just generate the script for the constrain from the backup DB and run the script on the problematic DB.

davmac1

I generated a script from SQL Server for the table and then modified it in notepad to drop all the default constraints and then add them back with parent_obj id as the suffix like Navision expects.
Then executed the script.
Navision can now modify the table fields again.
The constraints are stored in the sysobjects table and you can find the parent_obj number by querying on [name] = companyname$tablename and using the id.

Savatage

davmac1 wrote:

I had a SQL Server programmer change the field size of a field in a Navision defined table from 30 to 50 characters using SQL Server Enterprise instead of Navision.

I guess you won't be doing that again [-o<
the changing of fields has been discussed to infinitum.

better to add a new field or use the developers tool kit to find out where all the places that field needs to be updated. Plus as I've read here, it usually will cause problems during upgrades.

davmac1

I don't think you understand.
I did not ask him to change it - he just did it.
I found out when Navision got an error accessing that table when its internal definition did not match SQL Server's definition.

I had told him that table changes needed to be done in Navision. Judging from the errors he keeps fixing in his custom applications, I think this guy does not understand the importance of testing and collaboration.

DenSter

Glad you were able to fix that, I was getting a bit worried there :-k I take it you revoked his rights to your SQL Server?

davmac1

Unfortunately, he is not under my control. I can only let his manager know the problem he caused.
The previous .net developer was not as quick, but a lot more mature, and understood we were sharing the same database and referred all table changes to me.