How to copy the image field

Hi,
I have to write a procedure which copies some data within and between
tables, including image fields of unknown size. Those image fields contain
documents in various formats and can vary from couple of kilobytes to couple
of megabytes. How can I do it? My first instinct was to look at READTEXT,
WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
recommend them any more and is going to drop them altogether. Can anyone
direct me to some materials that would show how to do it?
Thanks,
MiroslawMPA (miroslaw_pa@.pf.pl) writes:
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields
> contain documents in various formats and can vary from couple of
> kilobytes to couple of megabytes. How can I do it? My first instinct was
> to look at READTEXT, WRITETEXT and UPDATETEXT functions, but it seems
> Microsoft does not recommend them any more and is going to drop them
> altogether. Can anyone direct me to some materials that would show how
> to do it?
You are correct that these operations are depreacated, but so is the
image data type.
The preferred data type for image data in SQL 2005 is varbinary(MAX). If
your data type is image, and you can change that, then you probably
have to use WRITETEXT and UPDATETEXT.
varbinary(MAX) works very much like the regular varbinary(n) data type,
but you can work with chunks with the .write mutator, and this way
update only a part of the column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||MPA wrote:
> Hi,
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields contai
n
> documents in various formats and can vary from couple of kilobytes to coup
le
> of megabytes. How can I do it? My first instinct was to look at READTEXT,
> WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
> recommend them any more and is going to drop them altogether. Can anyone
> direct me to some materials that would show how to do it?
> Thanks,
> Miroslaw
You can use the regular UPDATE statement to copy data between IMAGE
columns. However, in SQL Server 2005 the IMAGE type exists for
backwards compatibility only. Microsoft recommends that you use
VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
also deprecated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The customers have different servers. The first one has SQL Server 2000, so
it looks like I have to use READTEXT and UPDATETEXT. I just wanted first to
make a test to copy the small piece of data from one row of the table to
another, but it does not seem to work. It runs like this:
DECLARE @.ptrval varbinary(16)
DECLARE @.ptrval2 varbinary(16)
SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
and doc_zkz_id=35)
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
doc_zkz_id=35
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
I was trying here to read 16 first bytes of DOC_BLOB column from the first
row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
After the operation the written 16 bytes are completely different than the
data in the first row. On the other hand I was able to update a second row
with the statement
like:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 'abcdef"
What I am missing?
Thanks,
Miroslaw
Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> napisal w
wiadomosci news:1147621152.612045.54660@.j55g2000cwa.googlegroups.com...
> MPA wrote:
contain
couple
READTEXT,
> You can use the regular UPDATE statement to copy data between IMAGE
> columns. However, in SQL Server 2005 the IMAGE type exists for
> backwards compatibility only. Microsoft recommends that you use
> VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
> also deprecated.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||MPA (miroslaw_pa@.pf.pl) writes:
> The customers have different servers. The first one has SQL Server 2000,
> so it looks like I have to use READTEXT and UPDATETEXT. I just wanted
> first to make a test to copy the small piece of data from one row of the
> table to another, but it does not seem to work. It runs like this:
> DECLARE @.ptrval varbinary(16)
> DECLARE @.ptrval2 varbinary(16)
> SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
> and doc_zkz_id=35)
> READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
> SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
> doc_zkz_id=35
> WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
> I was trying here to read 16 first bytes of DOC_BLOB column from the first
> row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
> After the operation the written 16 bytes are completely different than the
> data in the first row. On the other hand I was able to update a second row
> with the statement
The statement
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
does not assign @.ptrval. Rather @.ptrval is a pointer to the blob data
in the column. The 16 bytes gets returned to the client. So when you
say:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
you are just writing that pointer to the blob.
You can copy blob data with UPDATETEXT. But please don't ask me for an
example. I very rarely work with any of the text operations, and when I
do, I need to study Books Online carefully.
Now, you mentioned that the source and target tables were on different
servers. This is going to make it even more painful.
I would try a straight UPDATE across the linked server. If this breaks
down, I would try to use BCP to bring the source table over the target
server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx