Copy a BLOB field in a stored-procedure from

Hello
I try to run the following query:
Update DestTable
set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID = 123)
Where DestTableID = 321
I always get the error message that this is not possible with image data.
Does anyone know a solution on how to copy a BLOB from one table to another?
I tried with WRITETEXT but this does not work either.
Many thanks
Thomas (thomas.steiner@.novaesprit.ch)Can you post your WRITETEXT code please.
Rick
"Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> Hello
> I try to run the following query:
> Update DestTable
> set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID =123)
> Where DestTableID = 321
> I always get the error message that this is not possible with image data.
> Does anyone know a solution on how to copy a BLOB from one table to
another?
> I tried with WRITETEXT but this does not work either.
> Many thanks
> Thomas (thomas.steiner@.novaesprit.ch)|||Here it is:
Declare @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(content)
FROM SambaReports_Test.dbo.Documents
Where DocumentID = 171
WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
RequestDataBody from RequestData where RequestID = 2328)
This results in a 'missing datastream' error. When I put a text-string
instead of the select-statement it works.
Thomas
"Rick Sawtell" wrote:
> Can you post your WRITETEXT code please.
> Rick
>
> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> > Hello
> > I try to run the following query:
> >
> > Update DestTable
> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID => 123)
> > Where DestTableID = 321
> >
> > I always get the error message that this is not possible with image data.
> > Does anyone know a solution on how to copy a BLOB from one table to
> another?
> > I tried with WRITETEXT but this does not work either.
> >
> > Many thanks
> >
> > Thomas (thomas.steiner@.novaesprit.ch)
>
>|||AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
to copy text data between tables. If your data type is ntext, you'll need
to add calculations to allow 2 bytes per character.
DECLARE @.ptrval binary(16),
@.StartIndex int,
@.RequestDataBodyLength int,
@.RequestDataBody varchar(8000)
--clear any existing data and init text pointer
UPDATE Documents
SET content = NULL
WHERE DocumentID = 171
SET @.StartIndex = 0
--get length of data to copy
SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
FROM RequestData
WHERE RequestID = 2328
--get text pointer
SELECT @.ptrval = TEXTPTR(content)
FROM Documents
WHERE DocumentID = 171
--copy data in chunks of 8000
WHILE @.StartIndex < @.RequestDataBodyLength
BEGIN
SELECT @.RequestDataBody = SUBSTRING(RequestDataBody, @.StartIndex, 8000)
FROM RequestData
WHERE RequestID = 2328
UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> Here it is:
> Declare @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(content)
> FROM SambaReports_Test.dbo.Documents
> Where DocumentID = 171
> WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> RequestDataBody from RequestData where RequestID = 2328)
> This results in a 'missing datastream' error. When I put a text-string
> instead of the select-statement it works.
> Thomas
> "Rick Sawtell" wrote:
>> Can you post your WRITETEXT code please.
>> Rick
>>
>> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
>> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
>> > Hello
>> > I try to run the following query:
>> >
>> > Update DestTable
>> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
>> > =>> 123)
>> > Where DestTableID = 321
>> >
>> > I always get the error message that this is not possible with image
>> > data.
>> > Does anyone know a solution on how to copy a BLOB from one table to
>> another?
>> > I tried with WRITETEXT but this does not work either.
>> >
>> > Many thanks
>> >
>> > Thomas (thomas.steiner@.novaesprit.ch)
>>|||Hi Dan
It works fine! Thanks a lot.
However I had to change the line:
SUBSTRING(RequestDataBody, @.StartIndex, 8000)
to
SUBSTRING(RequestDataBody, @.StartIndex+1, 8000)
Best regards
Thomas
"Dan Guzman" wrote:
> AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
> to copy text data between tables. If your data type is ntext, you'll need
> to add calculations to allow 2 bytes per character.
>
> DECLARE @.ptrval binary(16),
> @.StartIndex int,
> @.RequestDataBodyLength int,
> @.RequestDataBody varchar(8000)
> --clear any existing data and init text pointer
> UPDATE Documents
> SET content = NULL
> WHERE DocumentID = 171
> SET @.StartIndex = 0
> --get length of data to copy
> SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
> FROM RequestData
> WHERE RequestID = 2328
> --get text pointer
> SELECT @.ptrval = TEXTPTR(content)
> FROM Documents
> WHERE DocumentID = 171
> --copy data in chunks of 8000
> WHILE @.StartIndex < @.RequestDataBodyLength
> BEGIN
> SELECT @.RequestDataBody => SUBSTRING(RequestDataBody, @.StartIndex, 8000)
> FROM RequestData
> WHERE RequestID = 2328
> UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
> SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
> news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> > Here it is:
> >
> > Declare @.ptrval binary(16)
> >
> > SELECT @.ptrval = TEXTPTR(content)
> > FROM SambaReports_Test.dbo.Documents
> > Where DocumentID = 171
> > WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> > RequestDataBody from RequestData where RequestID = 2328)
> >
> > This results in a 'missing datastream' error. When I put a text-string
> > instead of the select-statement it works.
> >
> > Thomas
> >
> > "Rick Sawtell" wrote:
> >
> >> Can you post your WRITETEXT code please.
> >>
> >> Rick
> >>
> >>
> >> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> >> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> >> > Hello
> >> > I try to run the following query:
> >> >
> >> > Update DestTable
> >> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
> >> > => >> 123)
> >> > Where DestTableID = 321
> >> >
> >> > I always get the error message that this is not possible with image
> >> > data.
> >> > Does anyone know a solution on how to copy a BLOB from one table to
> >> another?
> >> > I tried with WRITETEXT but this does not work either.
> >> >
> >> > Many thanks
> >> >
> >> > Thomas (thomas.steiner@.novaesprit.ch)
> >>
> >>
> >>
>
>