|
BugNET Forums
 | | |
| | |
| | |
| | |
 | | |
|
|
|
 |
Joined: 1/30/2009
Posts: 33
|
|
|
admin wrote
| carlowahlstedt wrote
Script #1 run file for me after I fixed the casing errors (my database is case sensitive).
Script #2 had come casing errors as well, but here's the first major error:
(0 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BugNet_IssueNotifications_BugNet_Issues". The conflict occurred in database "BugNetBackup8", table "dbo.BugNet_Issues", column 'IssueId'.
Doing a quick query:
SELECT * FROM BugNet_Issues
Tells me there is nothing in the BugNet_Issues table.
|
Check the BugNotifications table for orphaned records with no matching Bug / Issue Id - SELECT * FROM BugNotifications WHERE BugId NOT IN (SELECT BugId From Bug)
If that is the case then I can add in another cleanup statement to execute before data copy.
Query:
SELECT * FROM BugNotification WHERE BugId NOT IN (SELECT BugId From Bug)
Returns 0 rows. I did this on a backup restore not the database I already ran the conversion scripts on.
|
|
|
|
| |
|
|
 |
 |
bugnetproject.com
Joined: 3/2/2006
Posts: 1007
|
|
|
carlowahlstedt wrote
| admin wrote
| carlowahlstedt wrote
Script #1 run file for me after I fixed the casing errors (my database is case sensitive).
Script #2 had come casing errors as well, but here's the first major error:
(0 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BugNet_IssueNotifications_BugNet_Issues". The conflict occurred in database "BugNetBackup8", table "dbo.BugNet_Issues", column 'IssueId'.
Doing a quick query:
SELECT * FROM BugNet_Issues
Tells me there is nothing in the BugNet_Issues table.
|
Check the BugNotifications table for orphaned records with no matching Bug / Issue Id - SELECT * FROM BugNotifications WHERE BugId NOT IN (SELECT BugId From Bug)
If that is the case then I can add in another cleanup statement to execute before data copy.
|
Query:
SELECT * FROM BugNotification WHERE BugId NOT IN (SELECT BugId From Bug)
Returns 0 rows. I did this on a backup restore not the database I already ran the conversion scripts on.
I would have thought that was the problem given the foriegn key error.
Are there any text messages output by the script other than that error? In case of an error it should log a message in the #ErrorLog temp table and there is a select * from #ErrorLog on event of an error.
|
|
|
|
| |
|
|
|
Joined: 11/8/2006
Posts: 51
|
|
|
admin wrote
Oops forgot that use fn statement in there. If you restore your database and change the use or remove it do you get further?
Those temp tables usually get stuck in the create state if there is an error in the script and you have to restore the database or run a drop table #OldResolutions .
I think the problem is that you try to create the same temp table for every project you have setup, I don't now if you have tested the script on a setup with multiple projects.
But i added the following code before ever temp table you create:
IF OBJECT_ID('tempdb..#OldResolution', 'u') IS NOT NULL
DROP TABLE #OldResolution ;
But now I recieve the following errors:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.dtproperties', because it does not exist or you do not have permission.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Permission' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Priority' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Project' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.ProjectCustomFields' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.ProjectCustomFieldType' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Resolution' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Roles' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Status' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Type' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Version' because it is referenced by a FOREIGN KEY constraint.
|
|
|
|
| |
|
|
|
Joined: 1/30/2009
Posts: 33
|
|
|
admin wrote
| carlowahlstedt wrote
| admin wrote
| carlowahlstedt wrote
Script #1 run file for me after I fixed the casing errors (my database is case sensitive).
Script #2 had come casing errors as well, but here's the first major error:
(0 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BugNet_IssueNotifications_BugNet_Issues". The conflict occurred in database "BugNetBackup8", table "dbo.BugNet_Issues", column 'IssueId'.
Doing a quick query:
SELECT * FROM BugNet_Issues
Tells me there is nothing in the BugNet_Issues table.
|
Check the BugNotifications table for orphaned records with no matching Bug / Issue Id - SELECT * FROM BugNotifications WHERE BugId NOT IN (SELECT BugId From Bug)
If that is the case then I can add in another cleanup statement to execute before data copy.
|
Query:
SELECT * FROM BugNotification WHERE BugId NOT IN (SELECT BugId From Bug)
Returns 0 rows. I did this on a backup restore not the database I already ran the conversion scripts on.
|
I would have thought that was the problem given the foriegn key error.
Are there any text messages output by the script other than that error? In case of an error it should log a message in the #ErrorLog temp table and there is a select * from #ErrorLog on event of an error.
Ok, I was looking at it this morning and I started seeing the #Temp tables issue the other guy did. I put these statements in:
DROP TABLE #OldResolution LINE 1076
DROP TABLE #NewResolution LINE 1113
DROP TABLE #OldIssueTypes LINE 1133
DROP TABLE #NewIssueTypes LINE 1170
DROP TABLE #OldStatus LINE 1190
DROP TABLE #NewStatus LINE 1230
DROP TABLE #OldPriority LINE 1250
DROP TABLE #NewPriority LINE 1288
DROP TABLE #NewIssues LINE 1308
Now I get error message for each of those saying:
Cannot drop the table '#TableName', because it does not exist or you do not have permission.
However, it gets further than it's gotten before and finally errors at this point:
Msg 3728, Level 16, State 1, Line 1
'FK_ProjectMailBox_aspnet_Users' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
It says it's this line (as inserting some stuff has changed my line numbers, not that sql line numbers in error messages are always right anyway):
ALTER TABLE [dbo].[ProjectMailBox] DROP CONSTRAINT [FK_ProjectMailBox_aspnet_Users]
I can also post the #ErrorLog if you'd like.
|
|
|
|
| |
|
|
|
|
bugnetproject.com
Joined: 3/2/2006
Posts: 1007
|
|
|
BC wrote
| admin wrote
Oops forgot that use fn statement in there. If you restore your database and change the use or remove it do you get further?
Those temp tables usually get stuck in the create state if there is an error in the script and you have to restore the database or run a drop table #OldResolutions .
|
I think the problem is that you try to create the same temp table for every project you have setup, I don't now if you have tested the script on a setup with multiple projects.
But i added the following code before ever temp table you create:
IF OBJECT_ID('tempdb..#OldResolution', 'u') IS NOT NULL
DROP TABLE #OldResolution ;
But now I recieve the following errors:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'dbo.dtproperties', because it does not exist or you do not have permission.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Permission' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Priority' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Project' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.ProjectCustomFields' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.ProjectCustomFieldType' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Resolution' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Roles' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Status' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Type' because it is referenced by a FOREIGN KEY constraint.
(1 row(s) affected)
Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Version' because it is referenced by a FOREIGN KEY constraint.
This is progress. I didn't test with multiple projects so that explains the temp table failures. I will add your suggested statement to drop the temp tables before creation.
You can delete the statement that tries to delete the dbo.dtproperties table and that should let you progress further with the script. My database has some extra tables and this statement should not be in there.
"Cannot drop the table 'dbo.dtproperties', because it does not exist or you do not have permission".
Thanks a bunch for all the testing guys, this really really helps.
|
|
|
|
| |
| | |
| | |
| | |
|
Forum Policy
These Discussion Forums are dedicated to the discussion of the BugNET issue tracker.
For the benefit of the community and to protect the integrity of the project, please observe the following posting guidelines:
1. No Advertising.
2. No Flaming or Trolling.
3. No Profanity, Racism, or Prejudice.
4. Site Moderators have the final word on approving/removing a thread or post or comment.
5. English language posting only, please.
|
|