Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

UPDATEing a ROW

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
Vasi04
Asked:
Vasi04
  • 14
  • 11
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
Thanks. will try out and get back
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vasi04Author Commented:
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
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
amit1978Commented:
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
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
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
 
Vasi04Author Commented:
AngelIII, will try your code and get back, thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
I am getting same message :(
0
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
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
 
Vasi04Author Commented:
coo, will try it at once. Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vasi04Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
... but I doubt that it will work, because it will still be inside a transaction which cannot apply inside a trigger
0
 
Vasi04Author Commented:
:( will try it out
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now