TEXT Datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TEXT Datatype

Author

Topic

vk59
Starting Member

38 Posts

Posted - 2004-04-19 : 06:45:58

When I try to build an UPDATE SQL statement from a row in pubs.dbo.pub_info table.I get the following error. "Invalid operator for data type. Operator equals add, type equals text".

Below is my query

SELECT 'UPDATE pub_info SET pr_info='+
CASE
WHEN pr_info IS NULL THEN 'NULL'
ELSE ''''+pr_info+''''
END + ' WHERE pub_id=0736'
FROM pub_info WHERE pub_id=0736

Can't i build a query which has text columns?

Thanks in advance
vk59

`

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-19 : 06:58:29

You might get this error if pr_info is a numeric datatype.

If this is the case then you should try converting it to a "string datatype" first.

eg( else '''' + str(pr_info, 8) + ''''

Duane.

vk59
Starting Member

38 Posts

Posted - 2004-04-19 : 07:02:16

pr_info is a text datatype column

vk59

quote:
Originally posted by ditch

You might get this error if pr_info is a numeric datatype.

If this is the case then you should try converting it to a "string datatype" first.

eg( else '''' + str(pr_info, 8) + ''''

Duane.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-19 : 07:07:35

[code]
SELECT 'UPDATE pub_info SET pr_info=' +
CASE
WHEN convert(varchar(8000),pr_info) IS NULL THEN 'NULL'
ELSE convert(varchar(8000),pr_info) END + ' WHERE pub_id=0736'
FROM pub_info WHERE pub_id=0736
[/code]
Text and image data acts differently from other datatypes, only use text if you need something over 8000 characters... Look up UPDATETEXT in BOL for more on it...

vk59
Starting Member

38 Posts

Posted - 2004-04-19 : 07:13:31

Yeah I do store large huge data in text columns.Something like 100K characters. So converting text datatype to varchar(8000) wouldnt help me.

VK59

quote:
Originally posted by RickD

SELECT 'UPDATE pub_info SET pr_info=' +
CASE
WHEN convert(varchar(8000),pr_info) IS NULL THEN 'NULL'
ELSE convert(varchar(8000),pr_info) END + ' WHERE pub_id=0736'
FROM pub_info WHERE pub_id=0736

Text and image data acts differently from other datatypes, only use text if you need something over 8000 characters... Look up UPDATETEXT in BOL for more on it...

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-19 : 07:23:30

As I said, UPDATETEXT may help you then...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-19 : 07:43:36

Am I missing something here or is the resultant sql statement that is being generated going to update the column pr_info with the same value that it already has?

Duane.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-19 : 07:48:04

Yeah, it will try to do that by the looks of it, but I was thinking vk59 was just trying to get a statement working so he/she could apply it to his/her actual code...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-19 : 07:55:21

I see,
If that is the case then I would like to suggest that the statement should update the column by referencing a column name rather than a value in the column.
This way there won't be a problem of exceeding 8000 characters.

eg set pr_info = a.pr_info

Duane.

vk59
Starting Member

38 Posts

Posted - 2004-04-20 : 10:19:47

Hi all,
Thanks RickD and Duane for your responses. UPDATETEXT is working.

`

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources