UpdateText Questions - 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

General SQL Server Forums

New to SQL Server Programming

UpdateText Questions

Author

Topic

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 09:19:27

Let me start off by saying that I am a total Newbie to any kind of database administration. I do a Holiday eCard mailing for a Salon client of mine and I noticed this year that of their 5,000 plus clients in their database, they have eMail addresses for approx 750

The software they use does not facilitate batch maintenance type activities and it is clear that their data entry is well unreliable.

So I have been using MSSMSE to access the database and have located a table called tblClients that includes all of the data that they collect on a client. There is a fldPopUp that if populated works as expected on their front desk software and generates a Pop Up when they go to book an appointment for the client. Some of these clients already have data in this field others are NULL and essentially what I would like to do is append the text 'PLEASE Confirm Client Info AND Obtain Email Addr' to each record where the fldEmail value is NULL

I came across the following code which seems like it would do the trick, however I'm not sure how to pass the value for @ID so that it will update each of the records where fldEmail value is NULL

-- =============================================
-- Author: Syed,Razi
-- Description: Appends to the AgNotes field
-- Assumptions: You have a SQL Database table myTable, with
-- field 'ID' that is used to select rows and
-- field 'textField' as a field of data type text
-- =============================================
ALTER PROCEDURE [dbo].[spAppendToTextField]
@TextToAdd nvarchar(255), -- Text to append to the end of the field
@ID int -- Primary ID of the field

AS

BEGIN

DECLARE @myPtr binary(16) -- This will be used to store a TEXTPTR to text field
DECLARE @n int -- This will be used to store the length of the text field

DECLARE myCursor CURSOR FOR
-- Select a text pointer to the text data type field, and get the current length
SELECT TEXTPTR(fldPopUp), DATALENGTH(fldPopUp)
FROM tblClientsEdit
WHERE fldPK = @ID

OPEN myCursor

FETCH NEXT FROM myCursor
INTO @myPtr, @n

WHILE @@FETCH_STATUS = 0
-- Go through each row to update
BEGIN

-- Append the text to the end of currently selected row
-- UpdateText Syntax:
-- UPDATETEXT [table].[field] [TEXTPTR] [Start_Insert_Position] [Num_to_Delete] [TextToInsert]
UPDATETEXT tblClientsEdit.fldPopUp @myPtr @n 0 @TextToAdd

FETCH NEXT FROM myCursor
INTO @myPtr, @n

END

END

CLOSE myCursor
DEALLOCATE myCursor

I've modified the original code to point to the appropriate tables and fields for my db -- I am trying to use the fldPK values from a query as follows to populate the data that the cursor should move through and evaluate / execute on

SELECT fldPK
FROM tblClientsEdit
WHERE fldEmail is NULL
EXEC spAppendToTextField 'PLEASE Confirm Client Info AND Obtain Email Addr', fldPK

Which gives me the result

(4584 row(s) affected)
Msg 8114, Level 16, State 1, Procedure spAppendToTextField, Line 0
Error converting data type nvarchar to int.

I understand that it is expecting an integer value to be passed to the stored procedure but I am not sure how to systematically populate this so that it will run through the list --

The stored procedure will work if I specify a value from the tblClientsEdit fldPK manually but that would defeat the entire purpose of what I am trying to achieve.

Many thanks in advance and help is greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 11:42:08

whats the datatype of fldPopUp field? also why cant you do this using set based soln?

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 12:31:07

data type is TEXT, I'm not sure I understand the second part of your question. Some of the records already have data in fldPopUp so I need to append to what is already there. I have a feeling this is just the first of my hurdles because when the value in fldPopUp is NULL I think from some of what i've read that it cannot append.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 12:41:58

ok . are you by any chance using sql 2005?

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 12:44:25

Yes sir, I apologize for failing to mention which v SQL I am working in.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 13:20:50

cant you use varchar(max) instead of text then?

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 13:28:02

Nearly every other TEXT field uses this data type. I suppose I could try changing the data type. I have a feeling they used TEXT data type because in some records there is a lot of data in this field.

I am much more comfortable manipulating data in excel. In your opinion would I be better off making the changes in excel and then importing it to update the field or would this not work the way I am expecting.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 13:53:16

nope if possible change them to varchar(max). text,ntext etc are deprecated from sql 2005 onwards and should be replaced by nvarchar(max),varchar(max) etc

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 13:56:15

I managed to achieve what I was trying to do by obtaining the unique ID for all clients where fldEmail was NULL and fldPopUp is not null and then concatenated the EXEC statement to make a list with the ID attached to the end and then pasted the result into a query and executing it. It was less than ideal and the join took place in Excel but at the end of the day it worked. I'm now working out what to do with the records where fldEmail is Null and fldPopUp is NULL -- the SP code I used for the first excersise will not work because of the NULL value in the fldPopUp column but I think I can manage a straigh UPDATETEXT command for each of these

I think my problem lays in understanding the cursor function and how it loops through a dataset but I've abandoned that for now to try to get this done.

Thanks very much for your input

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 13:59:36

you can also use .WRITE function. see below

http://jerrytech.blogspot.com/2008/03/can-you-alterappend-text-field-in-sql.html

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-03 : 14:05:05

This update adds the wanted comment to fldPopUp if it is not already there...

update tblClientsEdit
set fldPopUp=convert(varchar(max),isnull(fldPopUp,'')) + ' PLEASE Confirm Client Info AND Obtain Email Addr'
where fldEmail is null
and convert(varchar(max),fldPopUp) not like '%PLEASE Confirm Client Info AND Obtain Email Addr%'

No, you're never too old to Yak'n'Roll if you're too young to die.

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 14:42:23

Thanks to both of you!!

webfred,

I used your code and an interesting thing happened. As is, it affected 0 rows, then I commented out the last line and it affected 4584 (which is exactly the total number of records where fldEmail is null. This is great except that it also affected all of those where data already existed in the fldPopUp but appended it to the end. This is not a problem since I've been working with a backup of the database and not the live data (yes, though I am a newbie I know better than to cause myself any undo stress. I am thinking there should be a space on line 2 between is and null and that might have something to do with it.

But if I'm not mistaken and I restore the database to the raw data as it was before I appended to those that were not null already, then this code would work across the board whether my fldPopUp has already been populated or not because it appends either way.

bankrboy30
Starting Member

7 Posts

Posted - 2010-01-03 : 15:03:14

Ok, I was wrong about the space between is and null on line 2, however when I tested my idea, going back to the original unEdited table and ran just the first three lines of your code, it worked across the board, appending my text to the end of that fld irregardless of whether there was existing data or if it were NULL.

I mis-interpreted "not already there" as meaning that field would be null, however I now understand that it means exactly what you say -- the text I would like to append is not already part of the string that makes up the data in that field. Very clever

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-03 : 15:51:23

Glad I could help

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources