0

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

Patrick Sasquatch
  • 33
  • 8
  • 1
    That would depend, at least in part, on what database you are using and how you are accessing it. – Scott Hunter Jan 05 '22 at 15:44
  • @ScottHunter Updated description and tags. Microsoft SQL Server is what I'm using and I'm hitting the DB through a third party program that lets me execute raw SQL. Unfortunately this third party program has issues processing more than 1000 rows so I was looking for other ways of returning the data – Patrick Sasquatch Jan 05 '22 at 15:54
  • 3
    You could use [`FOR XML`](https://learn.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-ver15) or [`FOR JSON`](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15) to squeeze the result in a XML or JSON respectively. Or simply use another client without such limits... – sticky bit Jan 05 '22 at 15:58
  • 1
    You can master a request which produces XML formatted for EXCEL, OOXML. – Serg Jan 05 '22 at 16:00
  • @stickybit Thanks! I wish I could use something else, trust me... – Patrick Sasquatch Jan 05 '22 at 16:08
  • @Serg Thank you! – Patrick Sasquatch Jan 05 '22 at 16:08

2 Answers2

0

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.

AmilaMGunawardana
  • 1,604
  • 2
  • 13
  • 32
0

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
MikeY
  • 21
  • 3