• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

How to use UPDATETEXT function

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
Motaz
Asked:
Motaz
  • 9
  • 7
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MotazAuthor Commented:
What is binary(16)?
I want it to be unlimited size.

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

Cheers
0
Technology Partners: 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!

 
MotazAuthor Commented:
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
 
MotazAuthor Commented:
Also I tried this but I get the same error:

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

Motaz
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MotazAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MotazAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MotazAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
MotazAuthor Commented:
Thanks, it works!!
0
 
MotazAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see you understand the TEXT data type manipulations now :-)

Glad i could help.
CHeers
0
 
MotazAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now