UpdateText (syntax) - 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

SQL Server Development (2000)

UpdateText (syntax)

Author

Topic

Mithrilhall
Starting Member

13 Posts

Posted - 2004-05-17 : 09:15:11

The syntax checks out fine but I keep getting the following error when trying to run this in Analyzer:

Server: Msg 207, Level 16, State 3, Line 18
Invalid column name 'NewText'.

Line 18 is: set @ntxt = "NewText"

I have two fields, ID and Name.

ID is type int and Name is type varchar(50).

Here is the SQL statement:

declare @otxt varchar(1000)
set @otxt = 'ExistingText'

declare curs cursor local fast_forward
for
select
id,
textptr(Name),
charindex(@otxt, Name)-1
from
tblTest
where
Name
like
'%' + @otxt + '%'

declare @ntxt varchar(1000)
set @ntxt = "NewText"

declare @txtlen int
set @txtlen = len(@otxt)

declare @ptr binary(16)
declare @pos int
declare @id int

open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
updatetext tblTest.Name @ptr @pos @txtlen @ntxt
fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

I'm not sure what I'm doing wrong here.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-17 : 09:29:33

use single quotes, not double as SQL thinks this is a column name rather than a text string...

Mithrilhall
Starting Member

13 Posts

Posted - 2004-05-17 : 09:31:59

Thanks...that got rid of my original error but now I'm getting a new one:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'select'.

Any clues? That is my table name so I'm not sure what's going on.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-17 : 09:39:29

[code]
'%' + @otxt + '%'
[/code]
should be
[code]
'''%' + @otxt + '%'''
[/code]

Mithrilhall
Starting Member

13 Posts

Posted - 2004-05-17 : 09:49:13

I got it working.

I guess I needed spaces after id and txtptr(Name) in the following lines.

id,
textptr(Name),

Thanks for all the help.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-05-17 : 12:43:22

Mithrilhall......... Bunor? Drizit? Wulfgar?

I sense a forgotten Realms reader Here.

Jim
Users <> Logic