Solved

How to use UPDATETEXT function

Posted on 2002-04-08
16
646 Views
Last Modified: 2012-05-05
I have this stored procedure:

CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As
    Update [XMaC].[dbo].[Jobs] Set LastUpdatedTime = GetDate(), StatusID= @StatusID,
         JobResult =  IsNull( Cast(JobResult as VarChar(8000)), '') + Cast(@JobResult as VarChar(8000)),
-- Temporary solution
                          ResultSize = ResultSize + Len(Cast(@JobResult as VarChar(8000)))
    Where JobID= @JobID
GO


I want to concatinate two Text values into Text field, but above procedure append only up to 8000 bytes text, I need this function to concatinate unlimited size of Text to that field.
I asked this question befor and someone tell me to use UpdateText function, but I don't know how to use it.

Motaz
0
Comment
Question by:Motaz
  • 9
  • 7
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6925172
If you look into the books online, you should find this:


UPDATE [XMaC].[dbo].[Jobs]
  Set LastUpdatedTime = GetDate(),
      StatusID= @StatusID,
      JobResult =  '0'
      ResultSize = DataLength(@JobResult)
  Where JobID= @JobID


DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(JobResult )
  FROM [XMaC].[dbo].[Jobs]
 WHERE JobID= @JobID

UPDATETEXT [XMaC].[dbo].[Jobs] @ptrval NULL, NULL, @JobResult  

Cheers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6927548
What is binary(16)?
I want it to be unlimited size.

Motaz
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6927718
the binary(16) is only a pointer to the data, and is 16 bytes. The data itself can be up to 2GB.

Cheers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6927971
It compiles succesfully, but I get this error when I run the procedure:


(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure UpdateJobResult, Line 27
Invalid object name 'XMaC.dbo'.

Modified procedure:
CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As
DECLARE @ptrval binary(16)
--     Update [XMaC].[dbo].[Jobs] Set LastUpdatedTime = GetDate(), StatusID= @StatusID,
--          JobResult =  IsNull( Cast(JobResult as VarChar(8000)), '') + Cast(@JobResult as VarChar(8000)), -- Temporary solution
--                           ResultSize = ResultSize + Len(Cast(@JobResult as VarChar(8000)))
--     Where JobID= @JobID
--GO

UPDATE Jobs
 Set LastUpdatedTime = GetDate(),
     StatusID= @StatusID,
     JobResult =  '0',
     ResultSize = DataLength(@JobResult)
 Where JobID= @JobID



SELECT @ptrval = TEXTPTR(JobResult )
 FROM Jobs
WHERE JobID= @JobID


UPDATETEXT [XMaC].[dbo].[Jobs] @ptrval NULL NULL @JobResult
GO

Motaz
0
 
LVL 7

Author Comment

by:Motaz
ID: 6927975
Also I tried this but I get the same error:

UPDATETEXT [dbo].[Jobs] @ptrval NULL NULL @JobResult
GO

Motaz
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6930507
Sorry, i overlooked that UPDATETEXT only works for the current database:

UPDATETEXT [dbo].[Jobs] @ptrval NULL NULL @JobResult
or even
UPDATETEXT [Jobs] @ptrval NULL NULL @JobResult

CHeers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6930566
Angellll your first line does not compile at all,
your second line generate the same error, and I try it befor.
Moreover I'm using the same database.

Motaz
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6930573
Really sorry, but for me it's "monday morning"...

UPDATETEXT syntax:
UPDATETEXT table.column ptr offset length data

thus this should work:
UPDATETEXT [Jobs].[JobResult] @ptrval NULL NULL @JobResult

CHeers
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Author Comment

by:Motaz
ID: 6930959
Angelll, I guess that we go to the right solution, it compiles successfully, and it runs without the previous run-time error, but the problem now is that it overwrite the previous data and offcourse I want to append to the existing data:

CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As
DECLARE @ptrval binary(16)

UPDATE Jobs
Set LastUpdatedTime = GetDate(),
    StatusID= @StatusID,
    JobResult =  '0',
    ResultSize = DataLength(@JobResult)
Where JobID= @JobID

SELECT @ptrval = TEXTPTR(JobResult )
FROM Jobs
WHERE JobID= @JobID


UPDATETEXT [Jobs].[JobResult] @ptrval NULL NULL @JobResult


GO
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6930960
To append, use the offset parameter of the UPDATETEXT function:

CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As
DECLARE @ptrval binary(16)
DECLARE @offset int

UPDATE Jobs
Set LastUpdatedTime = GetDate(),
   StatusID= @StatusID,
   JobResult =  '0',
   ResultSize = DataLength(@JobResult)
Where JobID= @JobID

SELECT @ptrval = TEXTPTR(JobResult),
       @offset = DATALENGHT(JobResult)
FROM Jobs
WHERE JobID= @JobID


UPDATETEXT [Jobs].[JobResult] @ptrval @offset NULL @JobResult

CHeers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6931000
There is one problem remaining which I'm looking for now,
the data still overwritten because this line:

  JobResult =  '0'

one you set job result by '0' then you remove already exists data.

Motaz
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 6931012
sorry, i forgot to remove that :-)

CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As
DECLARE @ptrval binary(16)
DECLARE @offset int

UPDATE Jobs SET
  LastUpdatedTime = GetDate(),
  StatusID= @StatusID,
  ResultSize = DataLength(@JobResult)
Where JobID= @JobID

SELECT
  @ptrval = TEXTPTR(JobResult),
  @offset = DATALENGHT(JobResult)
FROM Jobs
WHERE JobID= @JobID

UPDATETEXT [Jobs].[JobResult] @ptrval @offset NULL @JobResult

CHeers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6931023
Thanks, it works!!
0
 
LVL 7

Author Comment

by:Motaz
ID: 6931024
I do some modifications:

CREATE PROCEDURE UpdateJobResult
@JobID Int,
@StatusID TinyInt,
@JobResult Text
As

DECLARE @ptrval binary(16)
DECLARE @offset int

UPDATE Jobs
Set LastUpdatedTime = GetDate(),
  StatusID= @StatusID,
  ResultSize = DataLength(@JobResult) + ResultSize
Where JobID= @JobID

update Jobs
set JobResult = '' where
JobID = @JobID and JobResult is null

SELECT @ptrval = TEXTPTR(JobResult),
      @offset = DATALENGTH(JobResult)
FROM Jobs
WHERE JobID= @JobID


UPDATETEXT [Jobs].[JobResult] @ptrval @offset NULL @JobResult
GO

Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6931025
I see you understand the TEXT data type manipulations now :-)

Glad i could help.
CHeers
0
 
LVL 7

Author Comment

by:Motaz
ID: 6931058
Yes I understand it and now I can answer questions of this topic:)
Thanks too much angelll, I hope that I can help you in the future.

Motaz
0

Featured Post

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.

Question has a verified solution.

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

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

930 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

13 Experts available now in Live!

Get 1:1 Help Now