Solved

UPDATEing a ROW

Posted on 2004-08-10
26
400 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 28
This query failed in sql 2014 5 29
SQL Server Designer 19 39
How to create a unique rule restriction on a table for two fields 16 75
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

815 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

11 Experts available now in Live!

Get 1:1 Help Now