This is an example of a store procedure that reads a VIEW and stores the result into a EXCEL sheet.
This example uses EXCEL of OFFICE 2010.
This new drive that Microsoft produced is not good at all, but if you are using EXCEL 2010 or higher, you have no other option.
Microsoft.ACE.OLEDB.12.0
Something that really got on my nerves was the use of the single quotes.
If you want to use INSERT INTO OPENROWSET with parameters you'll have to be carefull with single quotes.
If you want to write this ', you have to write this ''.
Be careful with the name of the columns of your table or view. If it begins with number, you will have a bad surprise.
EXAMPLE
SELECT 7RUBY FROM MYTABLE
Will produce a lot of 7s instead of the column value.
Don't use columns that begin with numbers.
Have in mind that you will be using linkedserver in background, which has, at least in my experience, not a very good performance.
Maybe is the drive, maybe is the configuration of the server, I don't know. But linked server in my experiment had a bad performance.
Oh, and don't forget to execute these commands
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
But can't I use a programming language to do this, instead of do it directly on SQLServer?
Excellent question!
I tried, but it seems the driver has a very weird behavior.
It seems to be intrinsically asynchronous, no matter what you do.
I tried C#.
The only I could have a reliable solution using this drive of Microsoft was making the process on SQLServer. I know, it seems extreme, but that's what solved my problem.
USE [Nike]
GO
/****** Object: StoredProcedure [dbo].[SP_POPULATE_EXCEL_SHEET] Script Date: 04/01/2015 13:17:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_POPULATE_EXCEL_SHEET]
@FileName varchar(255),
@Date1 varchar(10),
@Date2 varchar(10),
@Date3 varchar(10),
@Date4 varchar(10),
@ShopCode varchar(8)
AS
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'INSERT INTO OPENROWSET' +
'(''Microsoft.ACE.OLEDB.12.0'',' +
'''Excel 12.0;Database=c:\reports\' + @FileName + ';''' + ',' +
'''SELECT FIELD1,FIELD2 FROM [SHEET1$]''' + ')' +
'SELECT CITY, COUNTRY from VIEW_CITY_COUNTRY WHERE ((ProcessDate >= ''' + @Date1 + ''' AND ProcessDate <= ''' + @Date2 + ''' ) OR (ProcessDate >= ''' + @Date3 + ''' AND ProcessDate <= ''' + @Date4 + ''' )) AND IndicatorXXX =''S'' AND ShopCode = ' + @ShopCode
EXEC (@SQL)
GO
