Insert or update into a Image column causes data to double in size

We are experiencing problems inserting or updating image fields from
one table to another in SQL Server.

When we do this what ever size of file we insert is doubled in size
when it is inserted into the destination table.

This happens in insert and update queries, and if we use DTS.

Any help would be greatly appreciatedskyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are experiencing problems inserting or updating image fields from
> one table to another in SQL Server.
> When we do this what ever size of file we insert is doubled in size
> when it is inserted into the destination table.
> This happens in insert and update queries, and if we use DTS.
> Any help would be greatly appreciated

I'm afraid that it's difficult to help, since you don't disclose very
many details. Could you provide the code you use to copy the fields?

What is the typical size of the data you copy?

What you mean by "file ... is doubled in size"? What file? The database
file? But then you talk about inserting the file? How do you know
that it doubles in size?

Sorry for asking so many questions, but while you know very well what is
going on at your site, I know next to nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are storing documents such as PDFs, word documents etc in a object
field in a table. When we transfer the the contents from one table to
another using even a simple

INSERT INTO tblTest (ObjectField)
SELECT ObjectField
FROM tblTest2

The data in the destination is exactly doubled in size when you load
the document again ie when you save a 1Mb document into the table
transfer it to the second table it becomes 2Mb.

Hope this makes it more clear.|||skyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are storing documents such as PDFs, word documents etc in a object
> field in a table. When we transfer the the contents from one table to
> another using even a simple
> INSERT INTO tblTest (ObjectField)
> SELECT ObjectField
> FROM tblTest2
> The data in the destination is exactly doubled in size when you load
> the document again ie when you save a 1Mb document into the table
> transfer it to the second table it becomes 2Mb.

How do you deduce that?

It would help if you could provide a complete script that demonstrates
the problem. That would be a script that creates the table, populates
the first, and the loads the data into other, and then somehow gets
the size of the documents. I realise that including a 1MB object in
a script is not realistic, but the table pub..pub_info includes an
image column. Maybe you can build a repro script from that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp