Joined: 19 Mar 2007
|Posted: Mon Jun 09, 2014 5:40 pm Post subject: WHERE clause editor not working
|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.
/****** Object: Table [dbo].[TradeReviewReason] Script Date: 06/09/2014 12:35:07 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_isActiveReviewReason] DEFAULT ((1)) FOR [isActiveReviewReason]
ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_DollarValue] DEFAULT ((0.0)) FOR [DollarValue]
ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_PercentValue] DEFAULT ((0.0)) FOR [PercentValue]
ALTER TABLE [dbo].[TradeReviewReason] ADD CONSTRAINT [DF_TradeReviewReason_QuantityValue] DEFAULT ((0.0)) FOR [IntegerValue]
Joined: 20 Oct 2011
Location: Red Gate Software
|Posted: Wed Jun 11, 2014 3:27 pm Post subject:
|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?
Andy Campbell Smith
Red Gate Technical Support Engineer