Solved

UPDATEing a ROW

Posted on 2004-08-10
26
397 Views
Last Modified: 2008-02-01
I have two rows in a table. The value of ID1 in first row is identical to value of ID2 in second row. My VB application insert first row into this table. I want to make a copy of this row in the same table. But somehow RTF fields of second row are not populated. I want to write SP to insert RTF fields of second row by using where clause (ID1 with ID2)

In query analyzer, when I run this code
insert from tablename select * from tablename where id1 = id2, I still dont see any values inserted into RTF fields.

I dont know where lies the problem.
0
Comment
Question by:Vasi04
  • 14
  • 11
26 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11760199
I assume that with RTF you mean TEXT, NTEXT, IMAGE etc data type.
For those values, the normal INSERT simply doesn't work, you have to use another technique: UPDATETEXT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_wa-wz_2odw.asp

USE <yourdatabase>
GO
EXEC sp_dboption '<yourdatabase>', 'select into/bulkcopy', 'true'
GO
DECLARE @source binary(16)
DECLARE @dest binary(16)

SELECT @source = TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id = <id1>
SELECT @dest= TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id = <id2>

UPDATETEXT <tablename>.<yourRTFfield> @destination NULL NULL <tablename>.<yourRTFfield> @source
GO
EXEC sp_dboption '<yourdatabase>', 'select into/bulkcopy', 'false'
GO

CHeers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11760216
ps: note that the destination needs to have a NON-NULL value for the UPDATETEXT to work (btw, also the source), so when you copy:
* check the source,
    # if NULL -> do nothing
    # otherwise -> update destionation column with some dummy data before getting the pointer (TEXTPTR)...

CHeers
0
 

Author Comment

by:Vasi04
ID: 11760224
Thanks. will try out and get back
0
 

Author Comment

by:Vasi04
ID: 11760347
I have four RTF rows to update/insert at one shot.
do I have to write procedure for each update.
SOrry I am a newbie to db programming
0
 

Author Comment

by:Vasi04
ID: 11760382
Further, I need to compare columns values(IDs) of the same table to update RTF fields in the second row. That means, I may not want to value of id1 in the where clause:
"SELECT @source = TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id = <id1>"

Compare ID1 and ID2, if identical, copy RTF fields from row1 and update row2.

I just wanted to be more clear. Hope its ok
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11760504
Sorry, but i lost you there in the explanations.
Can you please provide some sample table data and explain what exactly you try to achieve in which case...

When I read ID, this is for me the primary key field (value) to identity the row, which does not seem to be the case for you, so also please explain what you mean by ID, and how you actually identity your rows (which will be mandatory)

Cheers
0
 
LVL 5

Expert Comment

by:amit1978
ID: 11760649
Hello  Vasi04

As u said Value of ID1 is store in ID2 there for u have to use the self join or

insert into  tablename select * from  tablename as a,  tablename as b
where a.ID1 = b.ID2

Sub Query
insert into  tablename select * from  tablename as a where a.ID1 in (select b.ID2 from tablename as b )


Amit Jain
0
 

Author Comment

by:Vasi04
ID: 11760737
Hi AngelIII, I have tried to explain this way:

Tablename = SourceTAB
Column names: ID1, ID2, status, changed_date, changed_time, change_by, Approach, Future_Approach, Next, Remarks, etc...........

Sample values:
SourceROW:
ID1 =  B5AI3FQH6
ID2 =
Status = Open
changed_date = 10.08.2003
changed_time = 10:05
change_by = vasi04
Approach =
{\rtf1\ansi\ansicpg1252\deff0\deflang1031{\fonttbl{\f0\fnil\fprq5\fcharset0 Arial;}}\viewkind4\uc1\pard\b\f0\fs20approach\par }
Future_Approach =
{\rtf1\ansi\ansicpg1252\deff0\deflang1031{\fonttbl{\f0\fnil\fprq5\fcharset0 Arial;}{\f1\fnil\fprq5\fcharset0 Arial;}}\viewkind4\uc1\pard\b\f0\fs20approach\f1\fs20 \par }
Next =
{\rtf1\ansi\ansicpg1252\deff0\deflang1031{\fonttbl{\f0\fnil\fprq5\fcharset0 Arial;}{\f1\fnil\fprq5\fcharset0 Arial;}}\viewkind4\uc1\pard\b\f0\fs20next\f1\fs20 \par }
Remarks =
{\rtf1\ansi\ansicpg1252\deff0\deflang1031{\fonttbl{\f0\fnil\fprq5\fcharset0 Arial;}{\f1\fnil\fprq5\fcharset0 Arial;}}\viewkind4\uc1\pard\b\f0\fs20remarks\f1\fs20 \par }
..

Fields in DestRow are filled by same values from SourceROW. The ID1 is copied as ID2 in DestRow and ID1 will have new unique value in DestRow. as in this exmaple

DestRow:
ID1 =  B5OQ3FUVC
ID2 = B5AI3FQH6
Status = Open
changed_date = 10.08.2004
changed_time = 10.05
change_by = vasi04
Approach =
Future_Approach =
Next =
Remarks =

But the RTF/Text fields are not populated in DestRow!

Thanks for your help

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11760788
Ok, I understand.

say, <id_value> stands for B5OQ3FUVC, then use this part slighly modified from my previous example:

SELECT @source = TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id1 = <id_value>
SELECT @dest= TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id2 = <id_value>

Again, use the UPDATETEXT after you copied all the other rows, to the destination row exists. UPDATE will not work for TEXT fields, you need to use the UPDATETEXT method

CHeers
0
 

Author Comment

by:Vasi04
ID: 11760848
Do I have to physical write id_value ("B5OQ3FUVC") in my procedure?
If yes, then I need to change this value in my proc everytime when I have new row in the table?

Can I have it this way?
...
..
SELECT @source = TEXTPTR(<yourRTFfield>) FROM <tablename> WHERE id1 in (select id2 FROM <tablename>)

-----
Amith, thanks for your reply, but INSERT, Update doesnt work for Text fields. I have to use either UPDATETEXT or WRITETEXT.
0
 

Author Comment

by:Vasi04
ID: 11760854
AngelIII, will try your code and get back, thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11760884
Of course, you can query the value of id1 like you described, the SQL there is basic sql, using 1 special function (TEXTPTR)...
CHeers
0
 

Author Comment

by:Vasi04
ID: 11761939
I used Query Analyzer to run this query

USE IRMA
GO
EXEC sp_dboption 'IRMA', 'select into/bulkcopy', 'true'
GO

DECLARE @SourceRow binary(16)
DECLARE @DestRow binary(16)

SELECT @SourceRow = TEXTPTR(APPROACH) FROM RMP WHERE _id2 in (select _id FROM RMP)
SELECT @DestRow = TEXTPTR(APPROACH) FROM RMP WHERE _id2 in (select _id FROM RMP)

UPDATETEXT RMP.APPROACH @DestRow NULL NULL RMP.APPROACH @SourceRow

GO
EXEC sp_dboption 'IRMA', 'select into/bulkcopy', 'false'
GO
----
I get this message in the result window:
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------

The RTF field is not filled!
Any suggestion? Thanks a lot

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11761998
Yes, because your source and destination are the same !!!

what about this:
SELECT @SourceRow = TEXTPTR(APPROACH) FROM RMP WHERE _id1 in (select _id FROM RMP)
SELECT @DestRow = TEXTPTR(APPROACH) FROM RMP WHERE _id2 in (select _id FROM RMP)

CHeers
0
 

Author Comment

by:Vasi04
ID: 11762308
I am getting same message :(
0
 

Author Comment

by:Vasi04
ID: 11762449
Hi
I need to write such four SELECT commans to update four Text fields then. Like:

SELECT @SourceRow1 = TEXTPTR(APPROACH) FROM RMP WHERE _id1 in (select _id FROM RMP)
SELECT @SourceRow2  = TEXTPTR(FUTURE_APPROACH) FROM RMP WHERE _id1 in (select _id FROM RMP)
....
....


SELECT @DestRow1 = TEXTPTR(APPROACH) FROM RMP WHERE _id2 in (select _id FROM RMP)
SELECT @DestRow2 = TEXTPTR(FUTURE_APPROACH) FROM RMP WHERE _id2 in (select _id FROM RMP)
...
...

UPDATETEXT RMP.APPROACH @DestRow NULL NULL RMP.APPROACH @SourceRow
UPDATETEXT RMP.FUTURE_APPROACH @DestRow NULL NULL RMP.FUTURE_APPROACH@SourceRow
...
...

am I right?

I can use this code in manage triggers option in SQL EM? right?

Thanks a million

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11762990
You might group the selects like this:
instead of this:
SELECT @SourceRow1 = TEXTPTR(APPROACH) FROM RMP WHERE _id1 in (select _id FROM RMP)
SELECT @SourceRow2  = TEXTPTR(FUTURE_APPROACH) FROM RMP WHERE _id1 in (select _id FROM RMP)

Do it like this:
SELECT @SourceRow1 = TEXTPTR(APPROACH) ,
            @SourceRow2  = TEXTPTR(FUTURE_APPROACH),
            ...
FROM RMP WHERE _id1 in (select _id FROM RMP)

similary for the @DestRowX variables...

UPDATETEXT will need to be run 4 times indeed.

Regarding triggers, I am not sure, because the following part will not be allowed inside the trigger:
EXEC sp_dboption '<yourdatabase>', 'select into/bulkcopy', 'true'
GO

This means that you need that option to be active on your database all the time.

Actually, If you have strong backup/restore mechanism active for the production environment, you need to think to split all the TEXT fields into tables in a second database, linked to the tables in the main database by 1 primary key. But that might be worthful discussing in another thread.

Cheers



0
 

Author Comment

by:Vasi04
ID: 11763201
If I update one RTF field, then I get above msg in the result window that field is updated though!
 
When I try to update more one field then I get below msg:

Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 7102, Level 20, State 99, Line 15
SQL Server Internal Error. Text manager cannot continue with current statement.
[Microsoft][ODBC SQL Server Driver]Communication link failure

Connection Broken
0
 

Author Comment

by:Vasi04
ID: 11763219
coo, will try it at once. Thanks
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 11763413
It could be that you indeed need to update 1 field after the other, as maybe the TEXTPTR function might return other values after the first UPDATETEXT ...
As I never had more than 1 TEXT field until now in any table i ever created, I can't confirm this to be the problem, but worth trying...
CHeers
0
 

Author Comment

by:Vasi04
ID: 11770103
Hi AngelIII
I was successful in updating all four RTF fields, the SQL-code  worked!! thanks.
But I get this message in the result window:
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
--------
Also, I tried using the code in a trigger (upon insert to the table), without GO key word, syntax works fine. But I get message like....I can not use sp_dboption within a transaction.
Any idea?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11770195
The messages are expected, you do not need to worry about the message itself. It would be good to understand however what it means "Checkpointing database that was changed".

>>I can not use sp_dboption within a transaction
This is what I mentionned, you cannot use it in trigger. You will need to have your database full-time in this settings: -> Database Properties -> Recovery Mode: bulk-insert etc

CHeers
0
 

Author Comment

by:Vasi04
ID: 11770502
I am stuck, code works fine with QA. I need to find a way around to have this in my trigger.
Can I start this topic as  new thread?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11770534
You might try to create a stored procedure, with uses EXEC ('exec sp_db_option ...' ) and also the remaining sql, and use this procedure from the trigger.
Hope this helps
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 11770538
... but I doubt that it will work, because it will still be inside a transaction which cannot apply inside a trigger
0
 

Author Comment

by:Vasi04
ID: 11770847
:( will try it out
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now