error message: The text, ntext, or image data type cannot be selected as DISTINC

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!

ThanksQuery 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:

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|||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.

Error message when trying to save modified maintenance plan?

I have a maintenance plan created that have been running for a few months.
Now I want to modify the plan and selected Modify on the plan and got into
an editor with the different steps. I selected "Edit" on the Backup step and
changed the name of the destination folder and clicked OK in that dialog.
When I try to save the modified maintenance plan, I only get an error dialog
with "No description found".
What is the problem?
OlavHi
I am not sure about the error, you may want to use SQL Profiler to find the
statement causing the error and try to run this manually. The quickest
solution may be to drop the maintenance plan and re-create it from scratch.
John
"Olav" wrote:

> I have a maintenance plan created that have been running for a few months.
> Now I want to modify the plan and selected Modify on the plan and got into
> an editor with the different steps. I selected "Edit" on the Backup step a
nd
> changed the name of the destination folder and clicked OK in that dialog.
> When I try to save the modified maintenance plan, I only get an error dial
og
> with "No description found".
> What is the problem?
> Olav
>
>|||I have the same problem when I try to modify a maintenance plan and then sav
e
the changes I get an error message saying "No description found" - this even
happens when I try to rename the maintenance plan. The changes are not save
d
and the maintenance plan executes ok.
I can create new plans using the wizard but not from scratch. I have
upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
Recreating maintenance plans each time I want to modify them is not an optio
n.
Does any one have a solution ?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure about the error, you may want to use SQL Profiler to find th
e
> statement causing the error and try to run this manually. The quickest
> solution may be to drop the maintenance plan and re-create it from scratch
.
> John
> "Olav" wrote:
>|||Hi
Are you seeing this error? http://tinyurl.com/mj88y
John
"Bridgepark" wrote:
[vbcol=seagreen]
> I have the same problem when I try to modify a maintenance plan and then s
ave
> the changes I get an error message saying "No description found" - this ev
en
> happens when I try to rename the maintenance plan. The changes are not sa
ved
> and the maintenance plan executes ok.
> I can create new plans using the wizard but not from scratch. I have
> upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP
1)
> Recreating maintenance plans each time I want to modify them is not an opt
ion.
> Does any one have a solution ?
> "John Bell" wrote:
>|||I see the "No description found" message but I have plans without Cleanup
tasks in them. I tried creating a new plan without using the wizard and whe
n
I try and save it I get the error. If I create a plan using the Wizard its
fine (even if it has a clean up task) However if I then try and alter the
plan I get the message.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Are you seeing this error? http://tinyurl.com/mj88y
> John
> "Bridgepark" wrote:
>|||Hi
You may have to log a call with PSS.
John
"Bridgepark" wrote:
[vbcol=seagreen]
> I see the "No description found" message but I have plans without Cleanup
> tasks in them. I tried creating a new plan without using the wizard and w
hen
> I try and save it I get the error. If I create a plan using the Wizard it
s
> fine (even if it has a clean up task) However if I then try and alter the
> plan I get the message.
> "John Bell" wrote:
>

Error message when trying to save modified maintenance plan?

I have a maintenance plan created that have been running for a few months.
Now I want to modify the plan and selected Modify on the plan and got into
an editor with the different steps. I selected "Edit" on the Backup step and
changed the name of the destination folder and clicked OK in that dialog.
When I try to save the modified maintenance plan, I only get an error dialog
with "No description found".
What is the problem?
OlavHi
I am not sure about the error, you may want to use SQL Profiler to find the
statement causing the error and try to run this manually. The quickest
solution may be to drop the maintenance plan and re-create it from scratch.
John
"Olav" wrote:
> I have a maintenance plan created that have been running for a few months.
> Now I want to modify the plan and selected Modify on the plan and got into
> an editor with the different steps. I selected "Edit" on the Backup step and
> changed the name of the destination folder and clicked OK in that dialog.
> When I try to save the modified maintenance plan, I only get an error dialog
> with "No description found".
> What is the problem?
> Olav
>
>|||I have the same problem when I try to modify a maintenance plan and then save
the changes I get an error message saying "No description found" - this even
happens when I try to rename the maintenance plan. The changes are not saved
and the maintenance plan executes ok.
I can create new plans using the wizard but not from scratch. I have
upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
Recreating maintenance plans each time I want to modify them is not an option.
Does any one have a solution ?
"John Bell" wrote:
> Hi
> I am not sure about the error, you may want to use SQL Profiler to find the
> statement causing the error and try to run this manually. The quickest
> solution may be to drop the maintenance plan and re-create it from scratch.
> John
> "Olav" wrote:
> > I have a maintenance plan created that have been running for a few months.
> >
> > Now I want to modify the plan and selected Modify on the plan and got into
> > an editor with the different steps. I selected "Edit" on the Backup step and
> > changed the name of the destination folder and clicked OK in that dialog.
> >
> > When I try to save the modified maintenance plan, I only get an error dialog
> > with "No description found".
> >
> > What is the problem?
> >
> > Olav
> >
> >
> >|||Hi
Are you seeing this error? http://tinyurl.com/mj88y
John
"Bridgepark" wrote:
> I have the same problem when I try to modify a maintenance plan and then save
> the changes I get an error message saying "No description found" - this even
> happens when I try to rename the maintenance plan. The changes are not saved
> and the maintenance plan executes ok.
> I can create new plans using the wizard but not from scratch. I have
> upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> Recreating maintenance plans each time I want to modify them is not an option.
> Does any one have a solution ?
> "John Bell" wrote:
> > Hi
> >
> > I am not sure about the error, you may want to use SQL Profiler to find the
> > statement causing the error and try to run this manually. The quickest
> > solution may be to drop the maintenance plan and re-create it from scratch.
> >
> > John
> >
> > "Olav" wrote:
> >
> > > I have a maintenance plan created that have been running for a few months.
> > >
> > > Now I want to modify the plan and selected Modify on the plan and got into
> > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > changed the name of the destination folder and clicked OK in that dialog.
> > >
> > > When I try to save the modified maintenance plan, I only get an error dialog
> > > with "No description found".
> > >
> > > What is the problem?
> > >
> > > Olav
> > >
> > >
> > >|||I see the "No description found" message but I have plans without Cleanup
tasks in them. I tried creating a new plan without using the wizard and when
I try and save it I get the error. If I create a plan using the Wizard its
fine (even if it has a clean up task) However if I then try and alter the
plan I get the message.
"John Bell" wrote:
> Hi
> Are you seeing this error? http://tinyurl.com/mj88y
> John
> "Bridgepark" wrote:
> > I have the same problem when I try to modify a maintenance plan and then save
> > the changes I get an error message saying "No description found" - this even
> > happens when I try to rename the maintenance plan. The changes are not saved
> > and the maintenance plan executes ok.
> > I can create new plans using the wizard but not from scratch. I have
> > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > Recreating maintenance plans each time I want to modify them is not an option.
> >
> > Does any one have a solution ?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > statement causing the error and try to run this manually. The quickest
> > > solution may be to drop the maintenance plan and re-create it from scratch.
> > >
> > > John
> > >
> > > "Olav" wrote:
> > >
> > > > I have a maintenance plan created that have been running for a few months.
> > > >
> > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > changed the name of the destination folder and clicked OK in that dialog.
> > > >
> > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > with "No description found".
> > > >
> > > > What is the problem?
> > > >
> > > > Olav
> > > >
> > > >
> > > >|||Hi
You may have to log a call with PSS.
John
"Bridgepark" wrote:
> I see the "No description found" message but I have plans without Cleanup
> tasks in them. I tried creating a new plan without using the wizard and when
> I try and save it I get the error. If I create a plan using the Wizard its
> fine (even if it has a clean up task) However if I then try and alter the
> plan I get the message.
> "John Bell" wrote:
> > Hi
> >
> > Are you seeing this error? http://tinyurl.com/mj88y
> >
> > John
> >
> > "Bridgepark" wrote:
> >
> > > I have the same problem when I try to modify a maintenance plan and then save
> > > the changes I get an error message saying "No description found" - this even
> > > happens when I try to rename the maintenance plan. The changes are not saved
> > > and the maintenance plan executes ok.
> > > I can create new plans using the wizard but not from scratch. I have
> > > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > > Recreating maintenance plans each time I want to modify them is not an option.
> > >
> > > Does any one have a solution ?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > > statement causing the error and try to run this manually. The quickest
> > > > solution may be to drop the maintenance plan and re-create it from scratch.
> > > >
> > > > John
> > > >
> > > > "Olav" wrote:
> > > >
> > > > > I have a maintenance plan created that have been running for a few months.
> > > > >
> > > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > > changed the name of the destination folder and clicked OK in that dialog.
> > > > >
> > > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > > with "No description found".
> > > > >
> > > > > What is the problem?
> > > > >
> > > > > Olav
> > > > >
> > > > >
> > > > >