Storing binary files in MS SQL 2000

[Ringo Langly]

Hi folks,

Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a
100K PDF or a 150K word document. Is it possible to store this in a
field in MS SQL and pull it out somehow? We're using ColdFusion on the
server on Apache.

Thanks,

Ringo

[Erland Sommarskog]

Ringo Langly (rl*****@gmail.com) writes:

Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a
100K PDF or a 150K word document. Is it possible to store this in a
field in MS SQL and pull it out somehow? We're using ColdFusion on the
server on Apache.

Yes, it's possible. You would use the image data type for this.
Unfortunately, I no nothing about ColdFusion, so I cannot give any
example of how to this in Cold Fusion. Working with image is however
more complex than other data types in SQL Server.

I should also add that there is a second alternative recommended by
many: store only the file path in SQL Server, and the file on disk.
I'm been proposing this myself at times. The main advantange is that
development becomes a lot simler. These days, however, I lean towards
storing the files in the database, as this is more robust and easier
to maintain. One single backup gets it all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[pb648174]

Doesn't that have performance implications?

Erland Sommarskog wrote:

Ringo Langly (rl*****@gmail.com) writes:

Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a 100K PDF or a 150K word document. Is it possible to store this in a field in MS SQL and pull it out somehow? We're using ColdFusion on the server on Apache.

Yes, it's possible. You would use the image data type for this.
Unfortunately, I no nothing about ColdFusion, so I cannot give any
example of how to this in Cold Fusion. Working with image is however
more complex than other data types in SQL Server.

I should also add that there is a second alternative recommended by
many: store only the file path in SQL Server, and the file on disk.
I'm been proposing this myself at times. The main advantange is that
development becomes a lot simler. These days, however, I lean towards
storing the files in the database, as this is more robust and easier
to maintain. One single backup gets it all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

Doesn't that have performance implications?

To store the documents in the database? Sure, it's more expensive than
to store the documents on disk. Then again, there is some network time
here, and file or database, you need to transfer the file over the
network.

In any case, sacrifcing database consistency for increased performance
is rarely a good trade-off.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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