WHERE clause editor not working

scottnelson

I'm trying to use the WHERE clause editor to filter rows included in data comparisons, and I've found that SDC is ignoring it. I'm using SDC v10.7.0.23.

For example, for the table scripted below, I accidentally put in a WHERE clause that was intended for another table which has completely different columns and I received NO ERROR from SQL Server...So I tried changing the filter to something totally invalid like 'this will fail xxxx' and still no error, so presumably SDC is not even attempting to use this filter.

The project options I have switched on are:
1. Include identity columns
2. Include timestamp columns
3. Trim trailing white space
4. Use checksum comparison
5. Include comment header in the deployment script

All other options are off.

The db collation is SQL_Latin1_General_CP1_CI_AS on both source and target.

Update: I checked the .sdc project file and the old, valid filter is there along with the new, invalid filter. Somehow SDC is using the previous filter instead of the new one. I can supply the .sdc file for analysis if necessary. It's too big to post here.

Table script:
USE [TradeBlotter]
GO

/****** Object: Table [dbo].[TradeReviewReason] Script Date: 06/09/2014 12:35:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TradeReviewReason](
[ReviewReasonId] [SMALLINT] IDENTITY(1,1) NOT NULL,
[isActiveReviewReason] [BIT] NOT NULL,
[EditCheckDesc] [VARCHAR](20) NOT NULL,
[SPName] [VARCHAR](20) NOT NULL,
[TradeSource] [dbo].[TBTradeSource] NOT NULL,
[AccountType] [VARCHAR](3) NOT NULL,
[InvRetType] [VARCHAR](3) NOT NULL,
[DollarValue] [MONEY] NOT NULL,
[PercentValue] [DECIMAL](18, 2) NOT NULL,
[IntegerValue] [INT] NOT NULL,
[FullDesc] [VARCHAR](500) NOT NULL,
[NotesDisplayDesc] [VARCHAR](150) NOT NULL,
[SearchListDesc] [VARCHAR](45) NOT NULL,
[MouseOverDesc] [VARCHAR](100) NULL,
[NotesRequired] [BIT] NULL,
[isApproveRequiredFlag] [BIT] NULL,
CONSTRAINT [PK_TradeReviewReason] PRIMARY KEY CLUSTERED
(
[ReviewReasonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_isActiveReviewReason] DEFAULT ((1)) FOR [isActiveReviewReason]
GO

ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_DollarValue] DEFAULT ((0.0)) FOR [DollarValue]
GO

ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_PercentValue] DEFAULT ((0.0)) FOR [PercentValue]
GO

ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_QuantityValue] DEFAULT ((0.0)) FOR [IntegerValue]
GO

Andy C S

Hi Scott,

That's really odd - if you could send us the .sdc file that'd be great so we can try and reproduce what's going on here. Can you send that to support@red-gate.com and quote the ticket number #21166? Thanks for your help with this!

Andy C S

Well, something's definitely gone wrong creating that project file - there's no fewer than 5 separate WHERE clauses specified for the table [dbo].[TradeReviewReason], and it looks like SQL Data Compare is just using the first it runs into.

I'm afraid I'm having trouble reproducing this, though - whenever I edit a WHERE clause in a project file SQL Data Compare behaves as I'd expect it to, which is to say it removes the old WHERE clause, adds the new one, and if the new one is invalid throws an error on comparison. Can you run through the exact steps you took in changing that WHERE clause?