Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint.

Topics: Database Publishing Wizard
Jan 16, 2007 at 2:31 AM
When I run my .sql script in GoDaddy's Query Analyzer after generating the default aspnetdb.sql file using the publisher I get this error message "Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint". I am fairly new to this and I have no idea what this means or the workaround. Any help is appreciated. By the way, I have been very stuck trying to publish my site from VWD to GoDaddy and this blog/forum is way helpful. Thanks for the cool work you guys/gals do.
Jan 16, 2007 at 5:44 PM
We will investigate this. In the mean time, have you tried publishing only the data for this table. You can select this option in the "Options" page of the wizard.

Thanks,
alex
Jan 19, 2007 at 1:00 PM
To Alex:

Hi,

I’ve been trying for days to publish Classifieds (template from WVD) onto GoDaddy – but without any success. I tried all the combinations: your instructions 1 and 2 with the publishing, GoDaddys instructions (if I may call it instructions), … and more-or-less every knowledge base I could find on the web. Also, yes, I have tried to upload only data, but if I manage to log myself – I can not do in the Site administration; if I log myself as Guest – same I cannot do anything.

Does anyone actually successfully upload the Classifieds template on the web (preferably GoDaddy)?

Lets make is simple, as I presume other have same question:
1. The simplest way: lets say I have page “ABC” which is just Classifieds template in WVD; I have run it for the first time and only use ASP.NET Configuration to log myself as administrator.
2. So, the entire template configuration is the same – let’s leave the customising as this is not the problem.

Now – why its not possible to merge these two databases (ASPNETDB.MDF and CLASSIFIEDSDB.mdf) to DB on GoDaddy? (connection strings and everything else is OK)
Simply – the question is “What now”?

These are the errors – depending of the method used to merge the data:

Error -2147217900
Could not drop object 'dbo.aspnet_Roles' because it is referenced by a FOREIGN KEY constraint.

Error -2147217865
Invalid object name 'dbo.aspnet_Profile'.

Error -2147217865
Invalid object name 'aspnet_Membership'.



These are only few errors.

Please, is there a straight forward way to upload the ASPNETDB.MDF and CLASSIFIEDSDB.mdf (or ASPNETDB.sql and CLASSIFIEDSDB.sql)?

Thank you very much.
Regards,

Den
Jan 19, 2007 at 6:50 PM
Hi den -

Your best bet to merge the databases is to do something like the following:

1) Generate a data only script for ASPNETDB.MDF.
2) Generate a complete script for the other database.
3) Upload the two SQL files to GoDaddy.
4) Follow the instructions at http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=UploadAndExecute to run the T-SQL files.

Thanks,
alex
Jan 20, 2007 at 11:19 AM
Dear Alex,

Thank you for reply.

Well, I did all of it before but somehow it doesn’t work. I have tried all the combinations and I have always mistakes, and I don’t know what I am doing wrong. Below are some of the errors when running runsql.aspx on godaddy (differences are because I try whit 2000 and 2005 versions, …)

When I have mentioned in my previous post “I tried all the combinations: your instructions 1 and 2” – I was thinking for “1” from here:
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
and “2” as one you advice me previously.

Well, I have on my PC VWD 2005, SQL Server EE 2005 and SQL Server Database Publishing wizard; and this is what I did:
1. downloaded Classifieds Starter Kit; run it for the first time in VWD, run ASP.NET Configuration and assign myself as administrator; and lets say this is now ready web page for publishing live.
2. I went to GoDaddy and create the SQL server database (with DSN and install schemas) and copy the connection strings details.
3. back on my PC run ASPNETDB.MDF and CLASSIFIEDSDB.mdf to publish as you have instructed – result lets say two files: ASPNETDB.SQL and CLASSIFIEDSDB.SQL.
4. Upload them info GoDaddy info root folder using FTP.
5. Downloaded RunSQL.aspx, edit it and insert the details as instructed. Using the FTP upload the file to GoDaddy.
6. Open it in browser – AND IT’S NOT WORKING

Obviously I am doing something wrong, but I cannot figure out what (it cannot be simpler sample as above). I will be very grateful if you, or anyone else, can point to the mistake I am making - probably you will figure out easily from this:

Opening file classifiedsdb.sql
An error occured: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITYATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode) at ASP.runsqlaspx._Render_control1(HtmlTextWriter __w, Control parameterContainer) The action that failed was: Demand The type of the first permission that failed was: System.Security.Permissions.FileIOPermission The Zone of the assembly that failed was: MyComputer


Opening file d:\hosting\denpolce\classifiedsdb.sql
An error occured: System.IO.FileNotFoundException: Could not find file 'd:\hosting\denpolce\classifiedsdb.sql'. File name: 'd:\hosting\denpolce\classifiedsdb.sql' at System.IO._Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITYATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode) at ASP.runsqlaspx._Render__control1(HtmlTextWriter __w, Control parameterContainer)

Thank you for the help and advice.

Regards,

Den
Jan 20, 2007 at 6:11 PM
Hi Alex,

Just update – I have sorted out the problem – well not with the SQL Server Data publisher but executing the scripts directly to GoDaddy (after lets say 100 mistakes finally success).

Though still puzzled what went wrong with methods 1 and 2 (as above) – however very happy that its sorted now and that I learned something new.

At the end, so far (in last 10 years) I made a lot of simple site (front page, dreamviewer, flash, …) but always without any interactions with the users, so all this staff with databases are basically new to me, and I find it’s a bit confusing, although, as I said, still don’t understand what mistakes I have made… Well, VWD, VB, SQL server (and rest of these new tools) are absolutely brilliant, as well as this (and asp.net,…) forum… where I have find great instructions.

Thank you. Best regards,

Den