0

I need to write a stored procedure in SQL Server which can store Excel sheet into a database (maybe using blob) and then another procedure to retrieve the file back.

For inserting I tried 

CREATE TABLE #ORStable (doclen BIGINT, doc VARBINARY(MAX))

INSERT INTO #ORStable
    SELECT LEN(bulkcolumn), *
    FROM OPENROWSET(BULK 'C:\Test\Test1.xlsx', SINGLE_BLOB) AS r

For retrieving:

DECLARE @SQLcommand NVARCHAR(4000)

SET @SQLcommand = 'bcp "SELECT doc FROM #ORStable " queryout "C:\test3.xlsx" -n -c ' 

EXEC xp_cmdshell @SQLcommand

The file must be stored completely with its formatting as well so when retrieved the exact file with formatting must be downloaded. This storing and retrieving must happen within SQL Server only and no application/code must be used.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nikhil
  • 51
  • 5
  • Neither of those commands will preserve Excel formatting. Those are very specific requirements. I would say what you ask is impossible. It's generally not a good idea to store binary objects in a SQL database. – Nick.Mc Apr 14 '19 at 12:51
  • This explains how to do it: https://www.jitendrazaa.com/blog/sql/sqlserver/export-documents-saved-as-blob-binary-from-sql-server/ OR this SO post: https://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk – Alex Apr 14 '19 at 17:55

0 Answers0