Solved

How to use UPDATETEXT function

Posted on 2002-04-08
16
653 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 143

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 143

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 143

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 143

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

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 143

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 143

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

831 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