Problem Adding Constraints
JustinPaquin
Posts: 7
Hello,
I'm evaluating your product for use in our development environment and I am having a problem with the script created when comparing our databse to an empty database.
The script runs fine while it creates all the tables and stored procedures but when it gets down to the section where it starts adding constraints it experiences problems. Here is a sample of the errors
Adding constraints to [dbo].[PIPE_tblStockReducers]
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockReducers' because this table does not
exist in database 'SC2006Dev'.
Adding constraints to [dbo].[PIPE_tblStockWyes]
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockWyes' because this table does not exist
in database 'SC2006Dev'.
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockWyes' because this table does not exist
in database 'SC2006Dev'.
Here is the offending code:
PRINT N'Adding constraints to [dbo].[PIPE_tblStockReducers]'
GO
ALTER TABLE [dbo].[PIPE_tblStockReducers] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockReducers] CHECK (([End1ConnectionEndGUID] <> [End2ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[PIPE_tblStockWyes]'
GO
ALTER TABLE [dbo].[PIPE_tblStockWyes] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockWyes] CHECK (([End1ConnectionEndGUID] <> [End2ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[PIPE_tblStockWyes] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockWyes_1] CHECK (([End1ConnectionEndGUID] <> [End3ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
These lines of code and any subsequent lines all fail due to the same problem. (Apparently the tables they are trying to add constraints to do not exist.
I know for sure that the objects it complains about do exist because it created them earlier in the script execution and displayed no error.
Creating [dbo].[PIPE_spPartPipeGetAll]
Creating [dbo].[PIPE_tblStockReducers]
Creating primary key [PK_PIPE_tblStockReducers] on
[dbo].[PIPE_tblStockReducers]
Creating [dbo].[HVAC_spPartCrossGetArrayExt]
Any ideas as to what the problem might be and how I can correct it.
Justin
I'm evaluating your product for use in our development environment and I am having a problem with the script created when comparing our databse to an empty database.
The script runs fine while it creates all the tables and stored procedures but when it gets down to the section where it starts adding constraints it experiences problems. Here is a sample of the errors
Adding constraints to [dbo].[PIPE_tblStockReducers]
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockReducers' because this table does not
exist in database 'SC2006Dev'.
Adding constraints to [dbo].[PIPE_tblStockWyes]
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockWyes' because this table does not exist
in database 'SC2006Dev'.
Msg 4902, Level 16, State 1, Server JUSTIN, Line 1
Cannot alter table 'dbo.PIPE_tblStockWyes' because this table does not exist
in database 'SC2006Dev'.
Here is the offending code:
PRINT N'Adding constraints to [dbo].[PIPE_tblStockReducers]'
GO
ALTER TABLE [dbo].[PIPE_tblStockReducers] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockReducers] CHECK (([End1ConnectionEndGUID] <> [End2ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[PIPE_tblStockWyes]'
GO
ALTER TABLE [dbo].[PIPE_tblStockWyes] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockWyes] CHECK (([End1ConnectionEndGUID] <> [End2ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE [dbo].[PIPE_tblStockWyes] WITH NOCHECK ADD CONSTRAINT [CK_PIPE_tblStockWyes_1] CHECK (([End1ConnectionEndGUID] <> [End3ConnectionEndGUID]))
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
These lines of code and any subsequent lines all fail due to the same problem. (Apparently the tables they are trying to add constraints to do not exist.
I know for sure that the objects it complains about do exist because it created them earlier in the script execution and displayed no error.
Creating [dbo].[PIPE_spPartPipeGetAll]
Creating [dbo].[PIPE_tblStockReducers]
Creating primary key [PK_PIPE_tblStockReducers] on
[dbo].[PIPE_tblStockReducers]
Creating [dbo].[HVAC_spPartCrossGetArrayExt]
Any ideas as to what the problem might be and how I can correct it.
Justin
This discussion has been closed.
Comments
We'd had a similar problem reported a few weeks ago, and it had something to do with tables that were fully-qualified down to the database name. Do you have any stored procedures that reference tables in this way?
e.g.
SELECT @PipePartGUID = PipePartGUID, @FinishGUID = FinishGUID
FROM dbo.PIPE_tblPartHangerFinishJoins
WHERE dbo.PIPE_tblPartHangerFinishJoins.PartHangerFinishJoinGUID = @PartHangerFinishJoinGUID
Justin
Does the script work if you generate it with the 'do not use plumbing for transactional scripts' option on?
Product works great!
Justin