CODEWITHSUNDEEP
sqlsql-serverexcelsql-server-2008ssis
Madhukar
Madhukar

Reputation: 1242

Is it possible to transfer MS SQL results to a new MS Excel file?

I am transferring SQL results to an existing Excel file using below query. Is it possible to transfer them to a new excel sheet? Using SSIS we can do this, but I want to know whether there is any possibility to do in SSMS.

SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   

INSERT INTO OPENROWSET 
   ('Microsoft.ACE.OLEDB.12.0', 
   'Excel 8.0;Database=E:\LessThan1300\OutofScope.xlsx;','select * from [Sheet1$]')
   select * from tboutofscope 
   where InflowDate >= CONVERT(date,getdate())

Upvotes: 1

Views: 285

Answers (1)

Pavel Botygin
Pavel Botygin

Reputation: 187

You can create copy of your template file like this:

DECLARE @sqlscript VARCHAR(4000), @pathtotemplatefile VARCHAR(MAX) = 'E:\LessThan1300\template.xlsx', @pathtonewfile VARCHAR(MAX) = 'E:\LessThan1300\OutofScope.xlsx', @xlsxdatabase varchar(4000)
--SET @pathtotemplatefile = 'c:\i\1.xlsx'
--SET @pathtonewfile = 'c:\i\2.xlsx'
SET @sqlscript='copy ' + @pathtotemplatefile + ' ' + @pathtonewfile
EXECUTE master..xp_cmdshell @sqlscript, no_output

set @xlsxdatabase = 'Excel 8.0;Database=' + @pathtonewfile + ';'

INSERT INTO OPENROWSET 
   ('Microsoft.ACE.OLEDB.12.0', @xlsxdatabase,'select * from [Sheet1$]')
   select * from tboutofscope 
   where InflowDate >= CONVERT(date,getdate())

You can dynamically change the name of the new file according to your needs, "create" it and then fill it with expected data.

Upvotes: 2

Related Questions