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.
3. Conversion of Text datatype to Image datatype
5. convert image datatype to text datatype
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