Linked Server

Here is bug in SQL 2000. In linked server, SQL query such
as "Select * from openquery (<LinkName>, 'Select * from
<Table name> where <key> in (-- list of key values --
--)')" works well as long as number of items in key
list are limited to 300 or so. In enterprise environment
where one deals with large data, this openquery fails
when items in key list are more then 1000.
Plain query with more then 1000 items in key list works
well against table in SQL server database and other
company product database (which is linked in above case)
when run separately.
In above case, other company database is linked with
provider as "Microsoft OLE DB Provider for ODBC" and data
source is a User DSN in ODBC.
Any comments...Have you tried using 4-part naming:
select
*
from
LinkedServer.MyDB.dbo.MyTable
where
PK in (1, 2, 3, 4, 5)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in message
news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
Here is bug in SQL 2000. In linked server, SQL query such
as "Select * from openquery (<LinkName>, 'Select * from
<Table name> where <key> in (-- list of key values --
--)')" works well as long as number of items in key
list are limited to 300 or so. In enterprise environment
where one deals with large data, this openquery fails
when items in key list are more then 1000.
Plain query with more then 1000 items in key list works
well against table in SQL server database and other
company product database (which is linked in above case)
when run separately.
In above case, other company database is linked with
provider as "Microsoft OLE DB Provider for ODBC" and data
source is a User DSN in ODBC.
Any comments...|||Four part naming does not work and gives error.

quote:

---

>--Original Message--
>Have you tried using 4-part naming:
>
>select
> *
>from
> LinkedServer.MyDB.dbo.MyTable
>where
> PK in (1, 2, 3, 4, 5)
>
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in

---

message

quote:

---

>news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
>Here is bug in SQL 2000. In linked server, SQL query such
>as "Select * from openquery (<LinkName>, 'Select * from
><Table name> where <key> in (-- list of key values --
>--)')" works well as long as number of items in key
>list are limited to 300 or so. In enterprise environment
>where one deals with large data, this openquery fails
>when items in key list are more then 1000.
>Plain query with more then 1000 items in key list works
>well against table in SQL server database and other
>company product database (which is linked in above case)
>when run separately.
>In above case, other company database is linked with
>provider as "Microsoft OLE DB Provider for ODBC" and data
>source is a User DSN in ODBC.
>Any comments...
>

---

|||How about telling us what error you get?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Shirish Kokate" <anonymous@.discussions.microsoft.com> wrote in message
news:08c401c3da54$ce1693c0$a101280a@.phx.gbl...
Four part naming does not work and gives error.

quote:

---

>--Original Message--
>Have you tried using 4-part naming:
>
>select
> *
>from
> LinkedServer.MyDB.dbo.MyTable
>where
> PK in (1, 2, 3, 4, 5)
>
>--
>Tom
>----

---

--

quote:

---

>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Shirish Kokate" <kokateshirish@.hotmail.com> wrote in

---

message

quote:

---

>news:029601c3d6f2$13589cf0$a401280a@.phx.gbl...
>Here is bug in SQL 2000. In linked server, SQL query such
>as "Select * from openquery (<LinkName>, 'Select * from
><Table name> where <key> in (-- list of key values --
>--)')" works well as long as number of items in key
>list are limited to 300 or so. In enterprise environment
>where one deals with large data, this openquery fails
>when items in key list are more then 1000.
>Plain query with more then 1000 items in key list works
>well against table in SQL server database and other
>company product database (which is linked in above case)
>when run separately.
>In above case, other company database is linked with
>provider as "Microsoft OLE DB Provider for ODBC" and data
>source is a User DSN in ODBC.
>Any comments...
>

---