March 29, 2005 at 8:17 am
This has been posted in the T-SQL forum for several days without response. Perhaps this is a more appropriate venue.
We recently stopped replication of a production database, due to problems which probably have no bearing on THIS problem.
All of the databases mentioned below are SQL Server 2000, version 8.00.818(SP3).
All are running on Windows 2000 Server. I don't know the version, but we generally stay current on Service Packs and
security hot-fixes.
We restored a copy of the production database to another server, using T-SQL with the KEEP_REPLICATION option.
We wished to inspect some of the replication-related items which are not normally restored via the GUI Restore option.
For this discussion, I will refer to the restored database as DB_R, on SQLServer_A.
Note that this DB_R was NOT involved in any replication; it merely contained the additional structures necessary for replication.
While this research was progressing, another project also utilized this DB_R for a comparison of a sub-set of the
standard User Tables (not replication information) with a later calendar version of the production data.
I will refer to this later version as DB_B.
It was located on a different server, refered to as SQLServer_B.
So, this comparision process was BOTH cross-database AND cross-server.
The comparison process consisted of a T-SQL Batch Script, running from Query Analyzer, with the active or "current" database
being "Master" on SQLServer_B. All references to DB_R and DB_B Tables were by four-part names.
No external processes were involved, and no dynamic SQL was used.
The user has "SA" rights for these servers.
During testing of this T-SQL Script, an anomaly was found while comparing the contents of a particular Table.
A SELECT against one of the Tables in DB_R was NOT returning a row, even though the row existed.
Column headings WERE returned, as well as the informational message "(0 row(s) affected)."
The Table's Primary Key, an IDENTITY column, was the sole criterion, and the row's existence was confirmed in both DB_R and DB_B.
Other Tables, with similar IDENTITY Primary Keys, had already been compared, with no problem.
Note that NO errors were reported in either the SQL Server or OS logs for either server.
Additional investigation produced some puzzling information.
Using Query Analyzer, the specific row in question could be SELECTed by Primary Key value in SOME situations.
If the "Current Database" for Query Analyzer was ANY database residing on SQLServer_A, the row's data was returned.
If however, the "Current Database" was ANY database residing on SQLServer_B, or any other database on a server which had
a Linked Server defined for SQLServer_A, NO row was returned. Whether the Table was referenced by four-part or
one-part name, ONLY running from SQLServer_A returned the row.
An inspection of the visual Estimated Query Plans of a simple SELECT using the Primary Key added to the puzzle.
From any database on SQLServer_A, a standard-looking plan.
From SQLServer_B, or others, the plan consisted only of a "Constant Scan" and a "SELECT." The target Table in question wasn't even part of the plan!
We ran DBCC CHECKDB against DB_R. No problems were found.
We ran DBCC DBREINDEX for the table in DB_R.
The problem still existed.
A copy of the original Table in DB_R was made, also in DB_R.
A SELECT against the copied Table worked from every environment!
So, this was something that DBCC didn't detect, or didn't recognize as a problem.
More investigation revealed that the problem Table had been involved in Merge Replication.
A "not for replication" CHECK Constraint had been added by replication to reserve a range of IDENTITY values for the Subscriber.
The Primary Key value which was causing the problem was NOT within the range reserved for the Subscriber.
Testing with a variety of values determined that ONLY values within the reserved range would produce a row of results for any server other the SQLServerA. The CHECK Constraint was removed, and the query then functioned normally from ALL environments.
Since these Constraints were not material to the replication-related research, this Constraint (and 58 of its fellows) was removed. This solved the immediate problem, but left questions.
Why does a CHECK constraint on a Table influence a SELECT against the Table? Why does it differentiate between a SELECT from another Database on the same Server, and a SELECT from another Server? Either could be a Subscriber, although none actually were.
Can anyone provide an explanation for this behavior, or have I found a "feature?"
March 30, 2005 at 6:47 am
I recently read (and I'm sorry I can't find the reference now) how adding constraints with a nocheck option can cause query result problems.
It went something like this:
SQL will use a constraint during query optimization and ASSUME the target of the constraint (such as foreign key) is valid. If you have data that won't pass the constraint but you still defined it with nocheck, then in certian situations this can cause a problem.
I wish I could find the article I was reading, but it sounds like the problem you are having might be related to this and not to anything really dealing with the fact that it is a remnant of replication.
March 30, 2005 at 8:15 am
The NOCHECK option was NOT used.
Any other thoughts?
Note the difference in results, depending on which server was used to run the SELECT.
March 30, 2005 at 12:56 pm
You probably didn't get an answer because this is a super hard problem. Really, you are looking at what can only be called a bug (functionaly challenged, if you will).
Here's a couple thoughts, though. Constraints are used during Query optimization. If you look at the execution plan for the remote call you'll notice it's just a "Remote Query". It's possible the remote execution plan and the local execution plan were different, with one (mis?)-using the check constraint and the other not using it at all.
Still, like I said, looks like a replication bug to me; you may want to check the Microsoft site about it.
cl
Signature is NULL
March 31, 2005 at 7:04 pm
Did you try dropping and re-creating the pk index you are selecting on?
Just a thought...
Robert
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply