Strange bug in linked servers

3 Aug 2006 10:33 AM

Panda

hi all

In my SQLServer 2000, i have a linked server "lnksrv" to another SQLServer
2000.
Into lnksrv there is a table "test" with the fields:
- I_ID int (prymary key)
- I_Integer int
- T_Text text

When i try the query:
SELECT isnull(I_Integer, 0) as I_Integer, T_Text
FROM lnksrv.database.dbo.test
ORDER BY I_Integer

i receive the error message:
Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or image
columns. The query processor produced a query plan that required a text,
ntext, or image column in a work table.

but the query:
SELECT isnull(I_Integer, 0) as anotheralias, T_Text
FROM lnksrv.database.dbo.test
ORDER BY I_Integer

works great.

I must use the first form because the query is automatically generated.
Can anybody help me?

3 Aug 2006 11:01 AM

Uri Dimant

Panda
Do you have SP3 or SP4 installed on server? What is the size of tempdb
database?

I did just some testing on my computer SS2000,ENT,SP3 and it worked just
fine

Show quote

"Panda" <giuntoli@despammed.com> wrote in message
news:ol5ly4ci66ou.9qe4xeh7vt2b.dlg@40tude.net...
> hi all
>
> In my SQLServer 2000, i have a linked server "lnksrv" to another SQLServer
> 2000.
> Into lnksrv there is a table "test" with the fields:
> - I_ID int (prymary key)
> - I_Integer int
> - T_Text text
>
> When i try the query:
> SELECT isnull(I_Integer, 0) as I_Integer, T_Text
> FROM lnksrv.database.dbo.test
> ORDER BY I_Integer
>
> i receive the error message:
> Server: Msg 8626, Level 16, State 1, Line 1
> Only text pointers are allowed in work tables, never text, ntext, or image
> columns. The query processor produced a query plan that required a text,
> ntext, or image column in a work table.
>
> but the query:
> SELECT isnull(I_Integer, 0) as anotheralias, T_Text
> FROM lnksrv.database.dbo.test
> ORDER BY I_Integer
>
> works great.
>
> I must use the first form because the query is automatically generated.
> Can anybody help me?

3 Aug 2006 12:04 PM

Panda

In data Thu, 3 Aug 2006 14:01:15 +0300, Uri Dimant ha scritto:

> Panda
> Do you have SP3 or SP4 installed on server? What is the size of tempdb
> database?

I have SP4. tempdb is 19Mb with 16Mb Free.

3 Aug 2006 11:24 AM

Razvan Socol

Hi, Panda

Let's suppose that you run these queries directly on the other server:

SELECT isnull(I_Integer, 0) as I_Integer, T_Text
FROM database.dbo.test
ORDER BY I_Integer

SELECT isnull(I_Integer, 0) as anotheralias, T_Text
FROM database.dbo.test
ORDER BY I_Integer

You will get different results: the first query sorts on the alias
I_Integer (i.e. on the expression "isnull(I_Integer, 0)") whereas the
second query sorts on the I_Integer column from the table. If I_Integer
are greater than zero, the results will be the same, but if you have
negative values, the first query will sort them before the nulls
(because they are converted to zeros before sorting) and the second
query will sort them after the nulls (because it sorts on the original
column and nulls are always first).

Razvan