(Error) Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Below is the error I get when trying to convert a Visual FoxPro memo field to a DT_WSTR (4000 ) in a SQL table. It does not let me convert a DT_TEXT to a DT_WSTR.

Thank in advance

TITLE: Editing Component

The component is not in a valid state. The validation errors are:

Error at NMF [Data Conversion [6328]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Do you want the component to fix these errors automatically?

BUTTONS:

&Yes

&No

Cancel

Long Object types such as DT_TEXT have limited conversion support. Since DT_TEXT is non-unicode, likely you would have to convert in two steps:

DT_TEXT -> DT_STR using same codepage

DT_STR -> DT_WSTR.

|||

Mark's spot on of course. Stick the following expression into a Derived Column Component

(DT_WSTR, 4000) (DT_STR, 4000, 1252) [ColumnName]

-Jamie

((cdate("1/1/2001")+30) as task_due_date (Not working)

I have the below function written in VB, and everything works fine
EXCEPT that the sql code is not executing correctly. The date field is
in the table always shows "12/1/1899 11:59:17 PM" no other date. I'm
should have the date of the input into the function + or - the integer
in the task_due_days field.

for example (cdate("1/1/2002")+30) as task_due_date

What am I doing wrong?

Function SetTasks(trans_id As Long, trans_type As Integer, event_date
As Date)

Dim task As String

task = "Insert into tbl_tasks
(trans_id,task_name,Task_due_date,comments) SELECT (" & trans_id & ")
as trans_id,task_name,(cdate(" & event_date & ")+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=" &
trans_type

Debug.Print task

DoCmd.RunSQL (task)

End Function

the actual SQL code is....

Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

ANY HELP IS GREATLY APPRECIATED!(stoppal@.hotmail.com) writes:
> the actual SQL code is....
> Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
> SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
> task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

Apparently you are not using SQL Server, as there is no cdate function
in SQL Serever.

I can tell what the problem is though: 1/1/2001 = 0 with integer division,
and with floating-point division you get 0.0005. Since you got
11:59:17, I guess that in whatever you are using, you have floating-
point division. (In SQL Server you would get integer division here.)

So you need to delimit the date string. In SQL Server that would be
'1/1/2001'. But it looks a bit likely you are using Access, in which
case maybe ## is better. But you better ask in comp.databases.ms-access
it you are uncertain.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

"Error converting data type nvarchar to real" using LIKE operator on CHAR column

View1 has a CHAR column of CharCol1. While using LIKE operator on this column, I receive below error.

Select * from View1 Where CharCol1 Like '%77%'

Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to real.

But all of below syntax works:

Select * from View1 Where CharCol1 like N'%77%'

Select * from View1 Where CharCol1 Like '77'

Select * from View1 Where CharCol1 = '77'

Why does SQL 2005 treat '%77%' as real? This used to be working in SQL 2000.

It works for me in SQL 2005. Are you sure your view's CharCol1 is really a character column, and hasn't been casted as real?|||

Actually the view was being used in SQL 2000 and after SQL 2005 upgrade, this error has started.

Last view used a view and that view also uses another view. There are three levels. But one of the views used in the first view is then joined in the last view which gives this error. Of course, this is not good programming but it was working in the previous version. Now, I have removed this join and carried the necessary columns from the first level thus I eliminated double joining of same view at the last level. This solved the problem.

Actually, the problem is that, I had tested this view and they were ok. But once I give a Where clause using a column from the double joined view, I get this error. Unfortunately, we encountered this error after upgrade and on the live system we had to fix it.

What I understand is that somehow in SQL 2005, we can get errors if the coding is not good and the views should be tested by giving "Where" clauses using all possible columns.

Hope this is clear.

Does anyone have comment on this?

' in the output of XML Explicit

Hi,
I have got a piece of XML genereated from XML Explicit like below which
should display an apostrophe instead of '
<ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
I tried to add xml directive but seems it only works for element but not
attribute.
Could somebody help me out?
Thanks very much!
Cloudcloudx wrote:
> Hi,
> I have got a piece of XML genereated from XML Explicit like below which
> should display an apostrophe instead of '
> <ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
When processed, this is exactly equivalent to a unidirectional
(typewriter-style) apostrophe. What's the problem? (Apart from the fact
that your example isn't well-formed: it needs quotes in the id="1".)
///Peter|||
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>|||Hi Peter,
Thanks for your reply. To clearly explain the issue I am having, here is a
piece of SQL code that returns a string of XML.
Select 1 as Tag,
NULL as Parent,
NULL as [PRODUCT!1],
NULL as [ITEM_DESCRIPTION!2!id],
NULL as [ITEM_DESCRIPTION!2!desc]
union
select 2,
1,
'PRODUCT'
, null
, null
union
select 2,
1,
null,
1,
'super wine 12''s'
for xml explicit
The output is:
<PRODUCT><ITEM_DESCRIPTION id="1" desc="super wine
12's"/><ITEM_DESCRIPTION/></PRODUCT>
I would like to get rid of ' and show the original '.
Thanks!
Cloud
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>|||You can use the !xml directive
Change
NULL as [ITEM_DESCRIPTION!2!desc]
to
NULL as [ITEM_DESCRIPTION!2!desc!xml]|||Thanks for your reply. With xml directive it only gives me desc element but
I
want desc attribute.
To further explain my issue:
I understand there is no issue of displaying the XML with ' in
iexploere as it will be converted to real apostrophe, however the real issue
is that if I look at the output in say Textpad the ' is there and when
I
try to validate the output against the schema, the ' occupies 6
characters and cause the maximum length exceed.
e.g. the desc is defined as 15 characters in the schema which just fits
"super wine 12's", however it will fail on "super wine 12's".
That's the issue!
Please help!
Thanks!
Cloud
"markc600@.hotmail.com" wrote:

> You can use the !xml directive
> Change
> NULL as [ITEM_DESCRIPTION!2!desc]
> to
> NULL as [ITEM_DESCRIPTION!2!desc!xml]
>|||AFAIK, SQL Server 2000 won't let you generate unencoded
attributes (only unencoded elements using the !xml directive).|||The validation error looks like a bug in the validator.
XML requires/allows encoding apostrophe as '. The information set value
is exactly the same as the apostrophe itself (and the validator should
accept it as such).
Having said that, SQL Server 2005 is actually returning the apostrophe as is
(and not entitized) for your query below. So you could upgrade from 2000 to
2005 ;-)
Best regards
Michael
"cloudx" <cloudx@.discussions.microsoft.com> wrote in message
news:8D8E014E-4DAB-4BAF-9D21-4C3413154B65@.microsoft.com...
> Thanks for your reply. With xml directive it only gives me desc element
> but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real
> issue
> is that if I look at the output in say Textpad the ' is there and
> when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
> Please help!
> Thanks!
> Cloud
> "markc600@.hotmail.com" wrote:
>|||cloudx wrote:
> Thanks for your reply. With xml directive it only gives me desc element bu
t I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real iss
ue
> is that if I look at the output in say Textpad the ' is there and whe
n I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
You've got some broken software. As I said, the ' is exactly
equivalent to "'", and any XML software which treats it otherwise
is almost certainly wrong. A max length should be applied *after*
entity resolution, not before.
///Peter
--
XML FAQ: http://xml.silmaril.ie/

' in the output of XML Explicit

Hi,
I have got a piece of XML genereated from XML Explicit like below which
should display an apostrophe instead of '
<ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
I tried to add xml directive but seems it only works for element but not
attribute.
Could somebody help me out?
Thanks very much!
Cloud
cloudx wrote:
> Hi,
> I have got a piece of XML genereated from XML Explicit like below which
> should display an apostrophe instead of '
> <ITEM_DESCRIIPTION id=1 desc1="Super Wine 12's"/>
When processed, this is exactly equivalent to a unidirectional
(typewriter-style) apostrophe. What's the problem? (Apart from the fact
that your example isn't well-formed: it needs quotes in the id="1".)
///Peter
|||"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>
|||Hi Peter,
Thanks for your reply. To clearly explain the issue I am having, here is a
piece of SQL code that returns a string of XML.
Select 1as Tag,
NULLas Parent,
NULLas [PRODUCT!1],
NULLas [ITEM_DESCRIPTION!2!id],
NULLas [ITEM_DESCRIPTION!2!desc]
union
select 2,
1,
'PRODUCT'
, null
, null
union
select 2,
1,
null,
1,
'super wine 12''s'
for xml explicit
The output is:
<PRODUCT><ITEM_DESCRIPTION id="1" desc="super wine
12's"/><ITEM_DESCRIPTION/></PRODUCT>
I would like to get rid of ' and show the original '.
Thanks!
Cloud
"Peter Flynn" wrote:

> cloudx wrote:
> When processed, this is exactly equivalent to a unidirectional
> (typewriter-style) apostrophe. What's the problem? (Apart from the fact
> that your example isn't well-formed: it needs quotes in the id="1".)
> ///Peter
>
|||You can use the !xml directive
Change
NULL as [ITEM_DESCRIPTION!2!desc]
to
NULL as [ITEM_DESCRIPTION!2!desc!xml]
|||Thanks for your reply. With xml directive it only gives me desc element but I
want desc attribute.
To further explain my issue:
I understand there is no issue of displaying the XML with ' in
iexploere as it will be converted to real apostrophe, however the real issue
is that if I look at the output in say Textpad the ' is there and when I
try to validate the output against the schema, the ' occupies 6
characters and cause the maximum length exceed.
e.g. the desc is defined as 15 characters in the schema which just fits
"super wine 12's", however it will fail on "super wine 12's".
That's the issue!
Please help!
Thanks!
Cloud
"markc600@.hotmail.com" wrote:

> You can use the !xml directive
> Change
> NULL as [ITEM_DESCRIPTION!2!desc]
> to
> NULL as [ITEM_DESCRIPTION!2!desc!xml]
>
|||AFAIK, SQL Server 2000 won't let you generate unencoded
attributes (only unencoded elements using the !xml directive).
|||The validation error looks like a bug in the validator.
XML requires/allows encoding apostrophe as '. The information set value
is exactly the same as the apostrophe itself (and the validator should
accept it as such).
Having said that, SQL Server 2005 is actually returning the apostrophe as is
(and not entitized) for your query below. So you could upgrade from 2000 to
2005 ;-)
Best regards
Michael
"cloudx" <cloudx@.discussions.microsoft.com> wrote in message
news:8D8E014E-4DAB-4BAF-9D21-4C3413154B65@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply. With xml directive it only gives me desc element
> but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real
> issue
> is that if I look at the output in say Textpad the ' is there and
> when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
> Please help!
> Thanks!
> Cloud
> "markc600@.hotmail.com" wrote:
|||cloudx wrote:
> Thanks for your reply. With xml directive it only gives me desc element but I
> want desc attribute.
> To further explain my issue:
> I understand there is no issue of displaying the XML with ' in
> iexploere as it will be converted to real apostrophe, however the real issue
> is that if I look at the output in say Textpad the ' is there and when I
> try to validate the output against the schema, the ' occupies 6
> characters and cause the maximum length exceed.
> e.g. the desc is defined as 15 characters in the schema which just fits
> "super wine 12's", however it will fail on "super wine 12's".
> That's the issue!
You've got some broken software. As I said, the ' is exactly
equivalent to "'", and any XML software which treats it otherwise
is almost certainly wrong. A max length should be applied *after*
entity resolution, not before.
///Peter
XML FAQ: http://xml.silmaril.ie/

#Re: manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]