Solved

How to use UPDATETEXT function

Posted on 2002-04-08
16
641 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]
Comment Utility
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
Comment Utility
What is binary(16)?
I want it to be unlimited size.

Motaz
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 7

Author Comment

by:Motaz
Comment Utility
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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, it works!!
0
 
LVL 7

Author Comment

by:Motaz
Comment Utility
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]
Comment Utility
I see you understand the TEXT data type manipulations now :-)

Glad i could help.
CHeers
0
 
LVL 7

Author Comment

by:Motaz
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

771 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

11 Experts available now in Live!

Get 1:1 Help Now