SQL Server 2008 R2 storing multimedia

Question

When I store mp3 files, or images (JPEG mainly) into a table the data seems to get altered. When I load the contents of the table back onto my local machine and do a diff between the original data it is apparent that the files are not the same. Also the files are not viewable/playable on my local machine.

Any ideas what could be causing this? Is it issues with the varchar data type?

I'm retrieving the data via the "save results as" option, and selecting all files, and applying the correct extension.

Also it's for a school project and the restrictions are not flexible (has to be completely implemented in SQL)

One more thing, what about storing a pointer to the multimedia? How would I go about doing that?

Here is my code:

USE master
----created in master schema

drop table blobtable

CREATE TABLE blobtable
  (BLOBData varchar(MAX))

BULK INSERT blobtable
FROM 'C:\Murach\SQL Server 2008\test.mp3'

select * from blobtable 

Answer 1

You shouldn't store binary data in a VARCHAR column. At the very least you should use the VARBINARY data type, or if you can, use a FILESTREAM. You mention that this is for class and your options are limited but it turns out you can enter binary data from a T-SQL statement:

CREATE TABLE EmployeeProfile ( 
  EmpId INT, 
  EmpName VARCHAR(50) not null, 
  EmpPhoto VARBINARY(max) not null
)
GO 

INSERT EmployeeProfile (EmpId, EmpName, EmpPhoto) 
SELECT 1001, 'Vadivel', 
BulkColumn from Openrowset( Bulk 'C:\Blue Lace 16.bmp', Single_Blob) as EmployeePicture

(from windowsclient.net/blogs/zuker)