How to store JPG files in SQL Server 2000 using T-SQL commands only?

Frontend
1

I’m trying to save a JPG picture file directly into my SQL Server 2000 database using just a regular INSERT statement. No front-end application involved.

I found a solution online and adapted it for my needs:

INSERT INTO employee_records
    (full_name, home_address, emp_id, qr_code, ProfileImage)
SELECT 'john_doe', 'main_street', 250, 'encoded_qr_data', BulkColumn
FROM OPENROWSET(BULK 'd:\profile.jpg', Single_Blob) AS emp_photo

But when I run this query I keep getting an error message. My database table has these columns: full_name (varchar), home_address (varchar), emp_id (int), qr_code (varchar), and ProfileImage (image datatype).

The error seems related to the OPENROWSET function but I can’t figure out what’s wrong with my syntax. Has anyone successfully inserted image files this way in SQL Server 2000? What am I missing here?

2

what error are you seeing exactly - a specific code or just a generic failure? have you tried textcopy instead? it handles binary data way better in sql 2000. can you paste the actual error message?

3

OPENROWSET with SINGLE_BLOB wasn’t available until SQL Server 2005 - that’s why your query’s failing on 2000. You’ll need to convert your JPG to hex format first, then use a regular INSERT statement. Open the image in a hex editor or use a command-line tool to get the hex values. Your INSERT should look like: INSERT INTO employee_records VALUES (‘john_doe’, ‘main_street’, 250, ‘encoded_qr_data’, 0xFFD8FFE0…). Make sure the hex string starts with the JPG header bytes. This approach works reliably with SQL Server 2000’s image datatype.

4

hey, just a heads up, openrowset with single_blob isn’t supported in SQL Server 2000. you might wanna try using the bcp utility or look into a frontend app for handling binary data more smoothly. options are pretty limited without those.