Issues with DB after publishing via Database Publishing Wizard from MSFT

Question

I work on quite a few DotNetNuke sites, and occasionally (I haven't figured out the common factor yet), when I use the Database Publishing Wizard from Microsoft to create scripts for the site I've created on my Dev server, after running the scripts at the host (usually GoDaddy.com), and uploading the site files, I get an error... I'm 99.9% sure that it's not file related, so not sure where to begin in the DB. Unfortunately with DotNetNuke you don't get the YSOD, but a generic error, with no real way to find the actual exception that has occured.

I'm just curious if anyone has had similar deployment issues using the Database Publishing Wizard, and if so, how they overcame them? I own the RedGate toolset, but some hosts like GoDaddy don't allow you to direct connect to their servers...

Answer 1

The Database Publishing Wizard's generated scripts usually need to be tweaked since it sometimes gets the order wrong of table/procedure creation when dealing with constraints. What I do is first backup the database, then run the script, and if I get an error, I move that query to the end of the script. Continue restoring the database and running the script until it works.

Answer 2

I'm not having a problem with the scripts executing against the DB. I'm having a problem with the site working after the DB and scripts have been created/run...

Answer 3

You should be able to expose the underlying error message by setting the following in the web.config:

customErrors mode="Off"

Could you elaborate on "and uploading the site files"? New instance of DNN? updating an existing site? upgrading DNN version? If upgrade or update -- what files are you adding/overwriting?

Also, when using GoDaddy, can you check to verify that the web site's identity (network service or asp.net machine account depending on your IIS version) has sufficient permissions to the website's file system? It should have modify permissions and these may need to be reapplied if you are overwriting files.

• IIS6 (XP, Server 2000, 2003) = ASP.Net Machine Account
• IIS7 (Vista, Server 2008) = Network Service

Answer 4

Test your generated scripts on a new local database (using the free SQL Express product or the full meal deal). If it runs fine locally, then you can be confident that it will run elsewhere, all things being equal.

If it bombs when you run it locally, use the process of elimination and work your way through the script execution to find the offending code.

My hunch is that the order of scripts could be off. I think I've had that happen before with the database publishing wizard.

Answer 5

Just read your follow up. In every case that I've had your problem, it was always something to do with the connection string in web.config. Even after hours of staring at it, it was always a connection string issue in web.config. Get up, take a walk and then come back.

Answer 6

If you are getting one of DNN's error pages, there is a chance it may have logged the error to the eventlog table.

Answer 7

There are two areas that I would look at - 1) Are you running in the dbo schema and was your scripted database using dbo? 2) Are you using an objectqualifier in either your dev or your production environment? (look at your sqldataprovider configuration settings)

Answer 8

Depending on exactly what is happening and what DNN is showing you you might be able to manually look inside the EventLog table, pull out the XML data stored there, and parse it to find the stack trace and detailed information regarding the specific error at hand.

I have found however though that I get MUCH better overall experiences with deployments using backups and restores of my database, that way I am 100% sure that all objects moved correctly, and honestly it works better in my experience.

With GoDaddy I know another MAJOR common issue is incorrect file permissions, preventing DNN from modifying the web.config and other files that it needs to do.