Ok, so it makes sense:

create table #myTable
(myColumn varchar(10),
textColumn text)

insert into #myTable
select distinct someValue, ''
from someTable

While this will work in 7.0, it does not work in 2000.
Since the second column inserted is always an emptry
string, The workaround is to use:

Insert into (myColumn) Values
Select distinct someValue
from someTable

I'm trying to avoid changing code with a SQL 2000 upgrade,
that's why I'm hoping there was a database setting that is
different. I still haven't found one...

Thanks all for your replies,
Adam
>-----Original Message-----
>You are inserting into a text column so SQL Server is
treating the value as
>a potential text datatype. Can you show the real example
since your simple
>one makes no sense. Why use Distinct for something like
this.
>
>You can try something like this:
>
>
> insert into #myTable (mycolumn,textcolumn)
> select distinct 'Test', CAST('Test' AS VARCHAR(20))
>
>
>--
>
>Andrew J. Kelly
>SQL Server MVP
>
>
>"Adam Nester" <adam.nesterwarnerbros.com> wrote in
message
>news:11e601c33d08$63772050$a401280aphx.gbl...
>> In SQL 7.0, the following T-SQL works fine:
>>
>> create table #myTable
>> (myColumn varchar(10),
>> textColumn text)
>>
>> insert into #myTable
>> select distinct 'Test', 'Test'
>>
>> However if I run this on a SQL 2000 database, I get the
>> following message: "Server: Msg 8163, Level 16, State 3,
>> Line 5 The text, ntext, or image data type cannot be
>> selected as DISTINCT."
>>
>> However, I AM NOT SELECTING ANY TEXT COLUMNS! Is there
>> some setting I'm missing? This is very frustrating, any
>> help on this would be greatly appreciated.
>>
>> Thanks,
>> Adam Nester
>> [email]adam.nesterwarnerbros.com[/email]
>
>
>.
>