Red Gate forums :: View topic - WHERE clause editor not working
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare 10
SQL Data Compare 10 forum

WHERE clause editor not working

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
scottnelson



Joined: 19 Mar 2007
Posts: 17

PostPosted: Mon Jun 09, 2014 5:40 pm    Post subject: WHERE clause editor not working Reply with quote

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
Back to top
View user's profile Send private message
andy.campbell.smith



Joined: 20 Oct 2011
Posts: 173
Location: Red Gate Software

PostPosted: Tue Jun 10, 2014 7:07 pm    Post subject: Reply with quote

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 Campbell Smith

Red Gate Technical Support Engineer
Back to top
View user's profile Send private message
andy.campbell.smith



Joined: 20 Oct 2011
Posts: 173
Location: Red Gate Software

PostPosted: Wed Jun 11, 2014 3:27 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group