How to store a zip in a sql server 2005 only with sql?

Question

I'm using SQL Server 2005 and I only can use Management Studio (it's a staging/production server). I need to save a zip file from local machine (server) into a blob field.

I do this:

create table CFile (id int, thefile varbinary(max))

INSERT INTO CFile
    (id, thefile)
    SELECT 1, 
        BulkColumn FROM OPENROWSET(
            Bulk 'C:\test.zip', SINGLE_BLOB) AS BLOB

and was ok, but then when:

SELECT * FROM CFile

I saw the row but I can't see the data in thefile field, and is not null (checked also with ISNULL function)

So there is the data but I'cant read it? why? How i retrieve it from Management Studio again, at least as varchar?

I try with

INSERT INTO CFile (id, thefile) VALUES (3,cast('asasdfadsfadsfdsafadsfasdfadsfasdfd' as varbinary))

and it works, I can see the content of the field.

Any help or tip in this will be preciated.

Thanks

score 2 · Accepted Answer · edited Jan 03 '12 at 16:02

2

I'm pretty sure SQL management viewer does not have a BLOB viewer. You will have to save it back to a file.

You can check with DATALENGTH(thefile) to test if the field was loaded.

edited Jan 03 '12 at 16:02

Leandro Bardelli

10,561
15
79
116

answered Jan 03 '12 at 15:38

Neil N

24,862
16
85
145

so did u think in any way to verified that the file was suscefully loaded? The only way that I've to check that is management because i don't have really access to the server (it's trought a third-company deployment) Thanks :) – Leandro Bardelli Jan 03 '12 at 15:40
1

Thanks Neil N !! Yep, with DATALENGTH() works! U've all right :) – Leandro Bardelli Jan 03 '12 at 15:45
1

perhaps there is a way to run a MD5 sum on the server? – Neil N Jan 03 '12 at 15:45
that's an excellent idea, cause also i can check the integrity. I'll try it – Leandro Bardelli Jan 03 '12 at 15:47
1

This questions might be helpful: http://stackoverflow.com/questions/910617/is-it-possible-to-calculate-md5-hash-directly-in-t-sql-language – Neil N Jan 03 '12 at 16:04

How to store a zip in a sql server 2005 only with sql?

1 Answers1