Motaz
asked on
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
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
ASKER
What is binary(16)?
I want it to be unlimited size.
Motaz
I want it to be unlimited size.
Motaz
the binary(16) is only a pointer to the data, and is 16 bytes. The data itself can be up to 2GB.
Cheers
Cheers
ASKER
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
(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
ASKER
Also I tried this but I get the same error:
UPDATETEXT [dbo].[Jobs] @ptrval NULL NULL @JobResult
GO
Motaz
UPDATETEXT [dbo].[Jobs] @ptrval NULL NULL @JobResult
GO
Motaz
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
UPDATETEXT [dbo].[Jobs] @ptrval NULL NULL @JobResult
or even
UPDATETEXT [Jobs] @ptrval NULL NULL @JobResult
CHeers
ASKER
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
your second line generate the same error, and I try it befor.
Moreover I'm using the same database.
Motaz
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
UPDATETEXT syntax:
UPDATETEXT table.column ptr offset length data
thus this should work:
UPDATETEXT [Jobs].[JobResult] @ptrval NULL NULL @JobResult
CHeers
ASKER
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
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
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
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
ASKER
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
the data still overwritten because this line:
JobResult = '0'
one you set job result by '0' then you remove already exists data.
Motaz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, it works!!
ASKER
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
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
I see you understand the TEXT data type manipulations now :-)
Glad i could help.
CHeers
Glad i could help.
CHeers
ASKER
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
Thanks too much angelll, I hope that I can help you in the future.
Motaz
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