HELP : Remote query from 2005 to 2000 returns wrong recordset when

Hi there
I came across this bug and could not find a solution, and I'm blocked.
Can anyone help ?
I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
SP4) server.
When I make a remote query from a (linked) SQL 2005 server (SP1 or
SP2), the returned recordset is totally wrong (rows are missing).
The problem does not occur between two SQL 2000, nor between two SQL
2005.Eric,
Can you define the condition of the missing rows better? That might give a
clue.
One suggestion is to compare the collations of the SQL 2000 server and
database with the collations of the SQL 2005 server and database. If that
is a problem, you might be able to resolve it with a COLLATE clause in the
query.
RLF
<EricBello@.eurofins.com> wrote in message
news:9c42bd9d-8d35-4370-bcd5-293830ac27f7@.l1g2000hsa.googlegroups.com...
> Hi there
> I came across this bug and could not find a solution, and I'm blocked.
> Can anyone help ?
> I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
> SP4) server.
> When I make a remote query from a (linked) SQL 2005 server (SP1 or
> SP2), the returned recordset is totally wrong (rows are missing).
> The problem does not occur between two SQL 2000, nor between two SQL
> 2005.
>|||Here is the script to reproduce the problem
At SQL 2000 side (SP3 or SP4) :
--cleanup
--DROP TABLE MyPackagesTranslations
--DROP TABLE MyPackages
GO
USE pubs
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
-- Create the table MyPackages,
CREATE TABLE [dbo].[MyPackages](
[packageIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[packageSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackages_packageSqlId] DEFAULT (1121),
[packageName] [nvarchar](60) NULL,
[isDeleted] [bit] NOT NULL CONSTRAINT
[DF_MyPackages_isDeleted_Default] DEFAULT (0x00),
CONSTRAINT [PK_MyPackages] PRIMARY KEY CLUSTERED
(
[packageIncId] ASC,
[packageSqlId] ASC
)
)
GO
-- Create linked table MyPackagesTranslations
CREATE TABLE [dbo].[MyPackagesTranslations](
[packageTranslationIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL,
[packageTranslationSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackagesTranslations_packageTranslationSqlId] DEFAULT (1121),
[packageIncId] [int] NOT NULL,
[packageSqlId] [smallint] NOT NULL,
[packageTranslationName] [nvarchar](60) NULL,
CONSTRAINT [PK_MyPackagesTranslations] PRIMARY KEY CLUSTERED
(
[packageTranslationIncId] ASC,
[packageTranslationSqlId] ASC
)
)
GO
-- ... with a foreign key between them
ALTER TABLE [dbo].[MyPackagesTranslations] WITH NOCHECK ADD CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
FOREIGN KEY([packageIncId], [packageSqlId])
REFERENCES [dbo].[MyPackages] ([packageIncId], [packageSqlId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[MyPackagesTranslations] CHECK CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
GO
-- Insert some data with "packageSqlId = 1"
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Foo')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Bar')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Cat')
INSERT MyPackagesTranslations (packageIncId, packageSqlId,
packageTranslationName)
SELECT packageIncId, packageSqlId, 'Translated ' + packageName
FROM dbo.MyPackages
GO
-- Now Add a CHECK CONSTRAINT for MyPackages.packageSqlId = 1121
ALTER TABLE [dbo].[MyPackages] WITH NOCHECK ADD CONSTRAINT
[CK_MyPackages] CHECK NOT FOR REPLICATION
(([packageSqlId] = 1121))
GO
ALTER TABLE [dbo].[MyPackages] CHECK CONSTRAINT [CK_MyPackages]
GO
=> And now at SQL 2005 side (SP1 or SP2)
Execute the query :
-- Now connect to another server with SQL 2005 SP1 or SP2, and launch
the remote query
-- (first verify you have a linked server)
SELECT count(*)
from <remoteserver>.pubs.dbo.MyPackages pack
INNER JOIN <remoteserver>.pubs.dbo.MyPackagesTranslations trans
ON (pack.packageIncId=trans.packageIncId and
pack.packageSqlId=trans.packageSqlId)
where pack.isDeleted=0x0
--=> returns "0" (KO) : should be "3"|||The collations are the same.
I really suspect a bug like the one on the optimizer that considered
some constraints trustworthy whereas they were "not for replication".|||I drilled down to the execution plan, and I can clearly see the
optimizer including the condition "where ...packageSqlId = 1121",
which shows it actually considers the table constraint
"trustworthy" (or sth alike) and restricts the recordset accordingly.|||The missing rows are those that don't comply with the TABLE CONSTRAINT.|||Eric,
Sorry, I really don't know. I used your repro script and get the same
results that you do.
Other things that I tried, on the SQL 2000 server create the RemoteServer
linked server pointing back to itself. Here were my results trying this:
SQLOLEDB connection: Returned 3 as desired.
SQLNCLI connection: I get the following error
OLE DB error trace [Non-interface error: Column 'packageTranslationName'
(compile-time ordinal 5) of object '"pubs"."dbo"."MyPackagesTranslations"'
was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
And, no matter what I try from the SQL 2005 server, I always get 0 returned.
In addition to SQLNCLI connect, I tried to script in a SQLOLEDB connect, but
it converted to SQLNCLI. I tried creating the remote server using the SQL
Server radio button, also. No change in query result.
Is SQLNCLI at the root of the problem? I don't know enough to say.
So, I would say that you have probably found a bug that you could choose to
report to Microsoft. At least a bit of googling did not help me find an
answer.
RLF
<erbellico@.gmail.com> wrote in message
news:06af6c0c-6f36-4e3d-b671-64a27caa12d2@.s12g2000prg.googlegroups.com...
> The missing rows are those that don't comply with the TABLE CONSTRAINT.