Very Computer

1. Odd problem with datatype text

Here's the query in question:

SELECT  distinct(new.accountid), new.batchid, act.lastname, act.firstname,
act.initial, SUBSTRING(act.notes, 1, 240) notes
FROM import_new_records new
JOIN billing.billing.dbo.accounts act
ON new.accountid = act.accountid
WHERE new.status = 'insert'
AND new.process = 'yes'

billing is a linked server. act.notes is declared as a text field, and text
fields cannot be used with a distinct keyword in the SELECT statement.  The
above query returns one of two things for notes.  If notes is NULL, it
returns NULL.  If not NULL, then it returns the empty string ('').

However, the following query executes perfectly:

SELECT  distinct(new.accountid), new.batchid, act.lastname, act.firstname,
act.initial, SUBSTRING(act.notes, 1, 240) notes
FROM import_new_records new
JOIN billing.billing.dbo.accounts act
ON new.accountid = act.accountid
WHERE new.status = 'insert'
AND new.process = 'yes'
AND new.accountid = 11783

It correctly returns the notes field.

What's up?  Any solutions?  Thanks -- I don't have much time to hang in the

appreciated.

2. Owner name errors in ODBC!

3. Conversion of Text datatype to Image datatype

4. Jaguar CTS ship date???

5. convert image datatype to text datatype

6. ADO record number

7. All text datatypes vs. more specific datatypes

8. DBCC DBREINDEX uses multiple processors?

9. text datatype and char datatype

10. datatype text length problem

11. TEXT DATATYPE PROBLEM

12. "Text" datatype problem