Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie