May 10, 2011 at 7:12 am
I have an application that has a migrator that will take changes made in dev and apply them to test, train, and then prod. It always fails when it tries to alter the contestants on the tables. The account the migrator uses has db_owner access to the database. When we run the code for ALTER TABLE Table Name NOCHECK CONSTRAINT constraint name logged on as the account we get the following error Constraint 'constraing name' does not exist. Could not enable or disable the constraint. We can see the constraint and I tried to run the same code under my account with has a server role of sysadmin. I get the same error message. Any idea's on why we cannot alter the constant?:exclamation:
May 10, 2011 at 9:20 am
We would really need more information. Maybe an example of one of the tables (script the creation code), and an example of the alter code? And the exact error couldn't hurt either.
At a guess though the only thing I could think to check is that you are in the correct database. I've seen more code fail just because the developer forgot to make sure what database they were actually in.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
May 11, 2011 at 11:08 am
I was able to get a hold of a DBA in our company that is not in IT. He suggested I run the following SQL quarry.
SELECT *
FROM dbname.information_schema.table_constraints
WHERE constraint_name LIKE 'constraint name'
When I ran it I noticed on one of the tables we were not able to alter the constraint the constraint_schema did not match the table_schema. On tables we can alter the constraint both of these are the same. This database does not use the standard dbo schema it has its own. Who could I correct it so that the constraint schema matches the table schema.
May 11, 2011 at 11:59 am
I would change the constraint to match the table. If you change the table you will have to change code to access them.
However you should still be able to change the constraint by using a 2 part name.
As far as how you can do an
ALTER SCHEMA schema_name TRANSFER object_name
or you can drop the constraint and recreate it using the correct 2 part name.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply