Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    9

    Unanswered: error message: The text, ntext, or image data type cannot be selected as DISTINCT.

    I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

    I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird---xml is not complete(cut in the middle).

    I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: error message: The text, ntext, or image data type cannot be selected as DISTINCT.

    Query Analyzer will not return complete fields beyond a certain length (even in "results in text" mode). The only way I know of to overcome this limitation is to:

    Code:
    Declare @Foo varchar(8000)
    
    SELECT @Foo = TextColumn FROM MyTable
    
    PRINT @Foo
    You can also use VB, VBScript or VBA and use the Debug.Print functionality (or write the results to a file).

    Regards,

    Hugh Scott

    Originally posted by heidil90
    I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

    I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird---xml is not complete(cut in the middle).

    I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

    Thanks

  3. #3
    Join Date
    Mar 2003
    Posts
    9

    Thumbs up Thanks!

    Thanks so much for helping me! Yesterday I figured out how to show the complete XML in Microsoft Internet Explore by running the utility configure SQL server XML support in IIS.

    I am still wondering why I use CDATA directive in my FOR XML EXPLICIT for a field that has a text datatype can work in the first level of hierarchy, but not in the second or third level of hierarchy. Do you have any idea? Thanks in advance!


    OK version:
    select 1 as tag,
    null as parent,
    Employees.EmployeeID as [Employee!1!EmployeeID],
    Employees.Notes as [Employee!1!!CDATA]
    from Employees

    for xml explicit
    **************
    Query batch completed.


    =================================
    ==================================
    wrong version:

    select 1 as tag,
    null as parent,
    Employees.EmployeeID as [Employee!1!EmployeeID],
    null as [Employee!2!!CDATA]
    from Employees

    union

    select 2,1,
    null,
    Employees.Notes

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •