2

I did an intensive search and could not seem to find an answer.

I want to export SQL queries to Excel and return it (the excel) to the client application or to my web-service. (think about a similar concept like FOR XML but instead return binary Excel data)

I tested this (after a long struggle) and it finally works fine:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=D:\1.xls;', 
'SELECT * FROM [Sheet1$]') 
SELECT TOP 5 ID, ItemName FROM dbo.MyItems

And return it to the clients:

SELECT * FROM OPENROWSET(BULK N'D:\1.xls', SINGLE_BLOB) rs

BUT, The file D:\1.xls must exists already in advanced. I have read this accepted answer: T-SQL: Export to new Excel file

4 If you dont want to create an EXCEL file in advance and want to export data to it, use EXEC sp_makewebtask

sp_makewebtask is deprecated.

Is there a way I could do this task without having an already existing file? or to at least dynamically create a suitable file before the export? or even better: not use any files at all?

Thanks.

zig
  • 4,524
  • 1
  • 24
  • 68
  • 1
    Take a look at this post: https://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ – Jacob H Jun 29 '17 at 13:10
  • 1
    SSIS comes handy in such places. data can be put up on excel on the go. – Prabhat G Jun 29 '17 at 13:32
  • 1
    @PrabhatG, can you please provide an example? – zig Jun 30 '17 at 12:06
  • What is the original reason for using the Excel file? If you're looking for a method that doesn't use files this is *way* easier – Rominus Aug 03 '17 at 15:29
  • @Rominus, I want to export SQL queries to Excel file and return it (the excel) to the client in my client application, instead of generating the Excel on the client side. – zig Aug 08 '17 at 11:06

2 Answers2

0

Maybe you can try to do something like this before your call :

xp_cmdshell "echo . > D:\1.xls"
Cyril Rebreyend
  • 310
  • 2
  • 6
0

It looks like the purpose of your code is to move the top 5 Item Names to another table? If that's the case all you need is into

SELECT TOP 5 ID, ItemName 
INTO dbo.Othertable
FROM dbo.MyItems
Rominus
  • 1,181
  • 2
  • 14
  • 29
  • The TOP 5 was just an example. I want to export SQL queries to Excel file and return it (the excel) to the client in my client application, instead of generating the Excel on the client side. – zig Aug 08 '17 at 11:08