Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to use UPDATETEXT function

Posted on 2002-04-08
16
Medium Priority
?
661 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
[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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

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
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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