Error in godaddy query analyzer when executing publishing wizard output

Topics: Database Publishing Wizard, Hoster Discussion, Using SQL Server in Hosted Environments
Dec 20, 2007 at 10:46 PM
Hi,
I keep getting the error "Could not drop object 'dbo.aspnet_Applications' because it is referenced by a FOREIGN KEY constraint. " when I paste and execute the output of the publishing wizard. The database that I am trying to create is the one from the TimeTracker example. The procedure that I am following is very simple.

1.) I opened the TimeTracker template in visual studio 2005.
2.) Right click on the 'TimeTracker.mdf' file in the Server Explorer frame of VS 2005
3.) I select the 'Publish to provider' option
4.) I use the publishing wizard's GUI to select the TimeTracker database as the input and my desktop as the output. I leave the publishing options at their default settings.
5.) Once the script is created I open the file in Notepad. Copy all of the text and paste it into Godaddy's Query Analyzer and hit execute...
That's when the error "Could not drop object 'dbo.aspnet_Applications' because it is referenced by a FOREIGN KEY constraint. " appears.

I have seen one other post where one user was getting the same error but with a different template... however when he finally got it to work he didn't know what he did to make it work...

ANY help would be greatly appreciated.... the amount of time that I have spent tinkering and searching for answers has been WAY too much...

Thanks
Jan 7, 2008 at 6:38 PM
In reviewing this error, we see that it is coming directly from the database server and is not GoDaddy specific. Without being able to look at the exact database, we can only guess at the root cause.

What we think it may be is, by default the Publishing Wizard drops objects it will publish to keep things “clean” in the database. This works well if you are publishing a new database or making an update to an old setup. It doesn’t work well if you have an existing database with some schema defined and you attempt to publish a different database with an overlapping schema (i.e., they use the same tables). In this case, it is probable that the Publishing Wizard’s output will perform operations in an order that causes conflicts.

It’s possible you created a new database and applied the asp.Net schema (either at creation or later) on our systems, which is all done through the control panel. Some of the objects in this schema are predefined in the Time Tracker database (e.g., the aspnet_Applications table). The publishing wizard is unaware of any existing structure on the target database and, as a result, couldn’t modify its output to drop objects in a safe order. When the script was executed, the table in question had an existing relationship and could not be dropped, causing the error reported.

There are two ways to deal with this:
First, you can disable the drop statements in the publishing wizard output. On the ‘Select Publishing Options’ page, set ‘Drop existing objects in script’ to false. This setting will cause the output script to check for the existence of objects before publishing and only create them if the object does not exist. This will work if there are no differences between objects that overlap in the target and source database. It will work if, for example, you attempt to publish an existing table and there are no changes. However, if you attempt to publish an existing table that contains an additional column, the new column will not be published because the script will see the table exists and not execute the create statement.

Second, you can create a completely clean target database. To do this, go to the ‘Hosting Control Center’ and remove the existing database. When this completes, create a new database and be sure not to apply the asp.NET schema. The database will be created with no objects and the publish should complete successfully.

It’s also worth noting that GoDaddy supports direct publishing from the Publishing Wizard utility. This will eliminate the need to copy and paste the script into the query analyzer. Details on where to find configuration information is in this help article, http://help.godaddy.com/article.php?article_id=3443&topic_id=102.

Hope this clarifies things for you!

Alicia R.
Go Daddy Hosting