Is it possible to return query results as an Excel/CSV file formatted as a BLOB or base64 encoded st-CodePudding

Limitations on some software I'm using require me to think outside the box, so can you have a query that automatically returns a "file" with the results in it? I imagine it would be as BLOB or a base 64 encoded string or something similar.

Using Microsoft SQL Server

CodePudding user response：

You can use two methods to achieve this

1. Use SQLcmd

SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"
-s "," -o "D:\MyData.csv"

Run this above command in a cmd and achieve the expected result.

2. Use OpenRawset

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName

You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.

limitations:

The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

CodePudding user response：

If you can create objects in the target database or another database on the same server, you could use the stored procedures and functions from my answer to this question: SSMS: Automatically save multiple result sets from same SQL script into separate tabs in Excel?

You would just need to exclude the @OutputFileName parameter to force output as a single column, single row varbinary(max).

select * into #systables from sys.tables;
select * into #syscolumns from sys.columns;
select * into #systypes from sys.types;

exec dbo.GetExcelSpreadsheetData
    @Worksheets = 'sys.tables/#systables/autofilter|sys.columns/#syscolumns/autofilter|sys.types/#systypes'

drop table #systables;
drop table #syscolumns;
drop table #systypes;

Output (truncated):

ExcelSpreadsheetData
-------------------------------------
0x504B0304140000000800DB78295479400A1