Error msg - The text, ntext, or image data type cannot be selected as DISTINCT

Problem :

SELECT id,remarks from table1
UNION
SELECT id,remarks from table2
note: the REMARKS field type is TEXT

I want to query the distinct records so i dont want to use the UNION ALL but I'm receiving the error msg below

Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

Solution:

1) give up
If you can’t change data type and your data length are likely more than 4000, give up.

2) using convert function
using convert in select statement, but this will omitted the data behind length 4000. eg. select convert( nvarchar(4000), fieldname ) as alias from …

3) change data type
change data type from ntext to nvarchar(4000).

SELECT id,cast(remarks as nvarchar(400)) from table1

UNION

SELECT id,cast(remarks as nvarchar(400)) from table2

More Information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=530757&SiteID=1

Moving MS SQL Databases To A New Location

Method 1
1. Create new MS SQL data location (E:\MSSQL\data\)
2. Stop MS SQL server
3. Move all databases to a new location (move *.mdf and *.ldf files)
4. Create junction link between old and new MS SQL data folders
5. Start MS SQL server
In case you have some databases with different from default locations, perform:
1. Detach these databases
2. Copy these databases from old location to a new location
3. Attach these databases from a new location

Method 2
1. Go to MS SQL Enterprise Manager
2. Choose the MS SQL server Properties option. For this, go to Expand SQL Server Group->MS SQL server

3. On the Database Settings tab, change New database location and set the path to:
Default data directory, i.e. a new logical disk (E:\MSSQL\DATA\)
Default log directory (E:\MSSQL\DATA\)

4. Create the following folder E:\MSSQL\DATA\
5. Set the same NTFS permissions as in the folder [drive]:\Program Files\Microsoft SQL\Server\MSSQL\DATA (the path where DB's are located ).
6. Go to MS SQL Enterprise Manager->Databases and right click on the Necessary database->All tasks->Detach Database with option Update statistics prior detach. Make sure to check database and database log files locations before detaching a database.

7. Go to [drive]:\Program Files\Microsoft SQL Server\MSSQL\DATA and copy Detached DB files (*.mdf and *.ldf) to a new folder E:\MSSQL\DATA\
8. Go to MS SQL Enterprise Manager->Databases and right click on Databases->Attach Database->.

9. Put the path to the necessary database (E:\MSSQL\DATA\) and select hsadmin in Specify database owner field.

10. Repeat steps 6-8 for the rest of databases.