Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

UPDATEing a ROW

Posted on 2004-08-10
26
Medium Priority
?
406 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
26 Comments
 
LVL 143

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 143

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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 143

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 143

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 143

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 143

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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 143

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 143

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 143

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

719 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