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

Frontend
1

I need help with storing a JPG image file directly into my SQL Server 2000 database table using only SQL commands. I don’t want to use any frontend application for this task.

I’m trying to use the OPENROWSET function with BULK option to read the image file and insert it into my table. Here’s what I’ve been attempting:

INSERT INTO employee_records
                  (fullname, location, emp_id, qr_code, ProfilePic)
SELECT     'john_doe', 'chicago', 250, 'employee_qr_data', BulkColumn
FROM         OPENROWSET(BULK 'd:\profile.jpg', Single_Blob) AS empPhoto

However, I keep running into errors when executing this query. My table has a column called ProfilePic with image data type to store the binary data.

Can someone guide me on the correct approach to accomplish this task? What could be causing the issue with my current query structure?

2

what error messages are you seeing? that’d help figure out what’s wrong. also, have you tested with a smaller file first? large jpgs can timeout in sql server 2000. how big is your profile.jpg?

3

yup, sounds like permissions might be your prob. sql server 2000 can be finicky with file access. ensure the sql service acct has read access to D:\ and that jpg file. also, double-check if your ProfilePic column is set to image or varbinary.

4

Your SQL syntax looks right for SQL Server 2000. The problem’s probably with OPENROWSET setup. Check if Ad Hoc Distributed Queries is enabled - it’s turned off by default for security. You can flip it on through Enterprise Manager or with sp_configure. Also make sure your file path uses forward slashes or escaped backslashes properly. I hit this same issue years back and found it helps to test with a simple text file first. That way you can tell if it’s an OPENROWSET problem or something specific to binary data. SINGLE_BLOB should work fine with image datatypes in SQL Server 2000, but try varbinary(max) instead if your version supports it.