2012年4月28日 上午 12:14
Please see error below:
Altering [sf].[Person]... (94,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The object 'df_Person_IsPartialBirthDate' is dependent on column 'IsPartialBirthDate'. (94,1): SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN IsPartialBirthDate failed because one or more objects access this column.
This error occurs on deployment. My scenario is that I have dropped a column from the design of the "sf.Person" table. That column has a default constraint. The SSDT deployment fails on the error.
This seems like a bug. Shouldn't the tool determine the correct sequence to drop the default constraint first if the column is also being dropped?
The design of this table is in a dacpac referenced to the main project.
2012年4月30日 下午 09:09
I tried to reproduce this issue but couldn't. I had the schema 'sf' in one project, and the table with default constraint in a separate project that references the first project. After dropping the column with the default constraint using table designer and deploying my changes, the default constraint was dropped before the column so the deployment succeeded. Could you tell us a little more about your database schema, including what the table references in the main project, and whether the two projects point to the same or different databases?
-Genevieve Orchard (SQL Server Data Platform team)
2012年5月2日 下午 02:42
Thanks Genevieve for looking into this. After running the same sequence you did above - which worked for me also - I did some more investigation and have found that the problem is a more subtle than the way I first described it. I will set up a test case with clear steps and post that (might take a day or two).
2012年5月24日 下午 06:31擁有者
Any luck in finding clear repro steps for us to try?
2012年5月28日 下午 10:39
Sorry for the delay and thanks again for looking into this.
I have found this problem can occur in multiple ways but the issue always come down to a change in an object in a dacpac file referenced in the main project. It can be consistently reproduced.
In my original post the problem occurred on a default constraint established on a column that was later dropped in the referenced dacpac project. In the steps below, the problem is demonstrated in a change to a FK relationship in a project referenced in a dacpac.
You will need 2 projects - let's call them Project A and Project B.
1. Create ProjectA. It doesn't actually require any content but a HelloWorld sproc will give it something of its own to verify deployment with.
2. Create ProjectB. Create 2 tables in a schema. Use a schema other than DBO for consistency with my scenarios, however, it is possible the error occurs in DBO also. In my example the schema name is "sf".
3. Create sf.TableB1 which can have any structure. Create sf.TableB2 can have any structure but needs a primary key. In my example I used an identity column for the PK.
4. Add a FK so that sf.TableB1 is parented by the PK column of sf.TableB2. Add an index on the FK column in sf.TableB1.
5. Create a dacpac from Project B.
6. Open Project A and add a database reference to the Project B dacpac. Use the "same database" option.
7. Publish Project A (no errors should occur at this point)
8. Open Project B and drop table sf.TableB2. Remove the FK column from sf.TableB1. Create a new dacpac.
9. Open Project A - update the DB reference to use the new dacpac file.
10. Publish project A.
While my table names are different than those above, an error message simliar in structure that provided below should occur.
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The index 'ix_ConfigParam_ConfigParamGroupSID_ConfigParamSID' is dependent on column 'ConfigParamGroupSID'.
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The object 'fk_ConfigParam_ConfigParamGroup_ConfigParamGroupSID' is dependent on column 'ConfigParamGroupSID'.
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN ConfigParamGroupSID failed because one or more objects access this column.
Even though ProjectB is valid when it is published through the reference in ProjectA its deployment is blocked. The dependent objects - in this case an index and a FK that no longer exist in ProjectB or its' dacpac prevent the deployment from succeeding.
We are wondering if this might be considered a bug. It seems the problem could be avoided if the dependencies to the dropped object - in this case the FK column - were also dropped when the publish script is generated. This kind of dependency analysis does occur within the main project – but not in the referenced dacpac (or at least that is what appears to be happening to us.)
Thanks again for looking into this.
2012年6月18日 下午 07:25
Sorry for the delay in getting back to you. Thank you SO much for the detailed and accurate repro steps. We were able to reproduce this behavior, and yes, you have found a bug. We have logged this bug in our system and the problem will be fixed in a future release. Again, many thanks for reporting this.
-Genevieve Orchard (SQL Server Data Platform team)
- 已標示為解答 Kazoo 2012年6月18日 下午 07:39
2012年7月10日 下午 07:22
I was looking at this bug. If you turn on “drop objects in target” you will get the correct behavior. You will need to turn this option on anyway to get that table dropped. When I do this I have the following output:
Jamie Laflen, Developer, Microsoft