Smart Rename fails when column participates in check

Comments

• Eddie D Redgate › Posts: 1,800 Rose Gold 5

  July 30, 2007 12:11PM

  Unfortunately I am to recreate the reported fault symptoms, as per my example below. Can you please reply back with further details.
  
  Many Thanks
  Eddie Davis
  Red Gate Software Ltd
  Technical Support
  E-Mail: support@red-gate.com
  
  /*
  Script created by SQL Refactor version 1.1.0.97 from Red Gate Software Ltd at 30/07/2007 16:43:50
  Run this script on Sample to perform the Smart Rename refactoring.
  
  Please back up your database before running this script.
  */
  -- Summary for the smart rename:
  --
  -- Action Plan:
  -- Drop check constraint [order_check] from table [dbo].[Sales]
  -- Alter table [dbo].[Sales]
  -- Alter function [dbo].[customer_orders]
  -- Add constraint [order_check] to [dbo].[Sales]
  --
  -- No warnings
  SET NUMERIC_ROUNDABORT OFF
  GO
  SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
  GO
  IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
  GO
  CREATE TABLE #tmpErrors (Error int)
  GO
  SET XACT_ABORT ON
  GO
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO
  BEGIN TRANSACTION
  GO
  PRINT N'Dropping constraints from [dbo].[Sales]'
  GO
  ALTER TABLE [dbo].[Sales] DROP CONSTRAINT [order_check]
  GO
  @TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Altering [dbo].[Sales]'
  GO
  sp_rename N'[dbo].[Sales].[Ship_Date]', N'Shipping_Date', 'COLUMN'
  GO
  @TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Altering [dbo].[customer_orders]'
  GO
  ALTER FUNCTION dbo.customer_orders(@pr_number INT)
  RETURNS TABLE
  AS RETURN(SELECT Shipping_Date AS Ship_Date
  FROM dbo.customer, dbo.Sales
  WHERE customer.cust_no = Sales.cust_no
  AND Order_no = @pr_number)
  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].[Sales]'
  GO
  ALTER TABLE [dbo].[Sales] ADD CONSTRAINT [order_check] CHECK (([Order_Date]<=Shipping_Date))
  GO
  @TRANCOUNT&gt;0 ROLLBACK TRANSACTION
  GO
  @TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
  GO
  @TRANCOUNT&gt;0 BEGIN
  PRINT 'The database update succeeded'
  COMMIT TRANSACTION
  END
  ELSE PRINT 'The database update failed'
  GO
  DROP TABLE #tmpErrors
  GO

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• fabianc2k Posts: 8

  July 30, 2007 8:13PM

  Steps to reproduce:
  
  1. Run create table / function script below
  2. Attempt to use smart rename on a column ColumnTwo
  
  

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunctionTableProblem_Function]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
  BEGIN
  execute dbo.sp_executesql @statement = N'-- =============================================
  -- Author:		Fabian Chessell
  -- Create date: 
  -- Description:	
  -- =============================================
  CREATE FUNCTION [dbo].[FunctionTableProblem_Function]
      (
        @columnOne int,
        @columnTwo int 
      )
  RETURNS bit
  AS BEGIN
      DECLARE @Result bit
  
  	-- This is an example function to reproduce the refactor bug
  	--
  	-- This function performs a uniqueness check, and returns
  	-- true if the value of column two has already been used for
  	-- a different row
  
      SET @Result = CASE WHEN EXISTS ( SELECT 1
                                       FROM   FunctionTableProblem_Table
                                       WHERE  ColumnTwo = @columnTwo
                                              AND NOT ColumnOne = @columnOne )
                         THEN ''true''
                         ELSE ''false''
                    END
  
      RETURN @Result
  
     END
  ' 
  END
  
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FunctionTableProblem_Table]') AND type in (N'U'))
  BEGIN
  CREATE TABLE [dbo].[FunctionTableProblem_Table](
  	[ColumnOne] [int] IDENTITY(1,1) NOT NULL,
  	[ColumnTwo] [int] NOT NULL,
   CONSTRAINT [PK_FunctionTableProblem] PRIMARY KEY CLUSTERED 
  (
  	[ColumnOne] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  END
  GO
  IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_FunctionTableProblem_Table]') AND parent_object_id = OBJECT_ID(N'[dbo].[FunctionTableProblem_Table]'))
  ALTER TABLE [dbo].[FunctionTableProblem_Table]  WITH CHECK ADD  CONSTRAINT [CK_FunctionTableProblem_Table] CHECK  (([dbo].[FunctionTableProblem_Function]([ColumnOne],[ColumnTwo])='false'))
  GO
  ALTER TABLE [dbo].[FunctionTableProblem_Table] CHECK CONSTRAINT [CK_FunctionTableProblem_Table]
  

  [/code]

  0 · Share on Twitter
• Eddie D Redgate › Posts: 1,800 Rose Gold 5

  July 31, 2007 10:29AM

  Thank you very much for your reply.
  
  Using your code I reproduced the reported fault symptoms, where the function is incorrectly altered before the table.
  
  As per your orignal post, changing the dependency order so that the table is altered before function resolves the issue.
  
  
  I will feed this information onto our Development and wait for the outcome of their investigations.
  
  Many Thanks
  Eddie
  
  Eddie davis
  Red Gate Software Ltd
  Technical Support

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter