2
Vote

04.05.00.SqlDataProvider: Potential Issues

description

Some constraint drops occur that need to have the word "IN" added to the TSQL in case other modules are using similar column names (this should have been here regardless, just an oversight on my part in the past).

file attachments

comments

bhmortim wrote Feb 10, 2011 at 4:38 PM

how about a corrected 04.05.00 file? Where should I add the IN to correct the problem? I am getting all sorts of errors where more than on object is returned when trying to do a drop. MY DB is all screwed up now and I cant clean out the forum garbage.

bhmortim wrote Feb 10, 2011 at 7:55 PM

I replaced the = for in in the id = subqueries. Once I cleared everything out of the DB and replaced the fixed file for the one in the install zip, everything installed fine. Attaching the fixed data provider i used.

slundahl wrote Oct 2, 2011 at 1:23 PM

I just got burned by this one as well. In the course of trying to recover from a failed forums upgrade, I've run into several more SQL problems that are making it difficult:
  • All stored procedures should be preceded by a drop statement, since SQL Server does not have the equivalent of Oracle's "CREATE OR REPLACE PROCEDURE" syntax.
  • Before adding a column, make sure that the column isn't already there (probably due to a failed install)
  • The Uninstall.SqlDataProvider has a problem (and, since there is already an issue out there for this it may be in an install script as well) dropping Forum_Forums due to a constraint that has not been dropped.
Also, I wouldn't categorize the Impact of these issues as Low as this particular issue completely hoses an upgrade of this module on a database with columns with non-unique names (and it bombs on a really common column name of DateAdded) and the subsequent errors you get when you try to first fix one SQL problem and then you start running into the others as it tries to rerun prior scripts really becomes time-consuming. Furthermore, the uninstall script doesn't work because of the Forum_Forums constraint problem (and you wouldn't want to run that if you have active forums using this module).