Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas

"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff
choppertoo
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message555779.html