What are the challenges you face when working across database platforms? Take the survey
Options

Problem Adding Constraints

JustinPaquinJustinPaquin Posts: 7
edited April 21, 2005 7:14PM in SQL Compare Previous Versions
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&gt;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&gt;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&gt;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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi justin,

    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?
  • Options
    We do not have any stored procedures that reference tables in a fully qualified name down to the database. In all of our stored procedures the name is qualified to the owner

    e.g.
    SELECT @PipePartGUID = PipePartGUID, @FinishGUID = FinishGUID
    FROM dbo.PIPE_tblPartHangerFinishJoins
    WHERE dbo.PIPE_tblPartHangerFinishJoins.PartHangerFinishJoinGUID = @PartHangerFinishJoinGUID


    Justin
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Justin,

    Does the script work if you generate it with the 'do not use plumbing for transactional scripts' option on?
  • Options
    I feel kinda sheepish. There was some code I was adding to the script in order to insert data into the tables as it went along. Turns out this extra code was causing the problem.

    Product works great!

    Justin
This discussion has been closed.