Hi All,
I want to run a sql query on a specific schedule (weekly) and need to capture the output in an excel file as well.
Due to business restrictions we cannot use SQL server jobs and we cannot install Business Intelligence tools too.
Regards, RP
views:
80answers:
4
A:
That pretty much rules out letting SQL Server do this for you. One alternative then is to create a Windows Service that contains a timer that fires on a schedule, that executes the query you need to do.
Randy Minder
2010-03-04 15:20:59
A:
set a Windows schedule task to run the osql Utility, which can fire off your SQL
KM
2010-03-04 15:27:24
Thank you, I tried it but the OSQL was not able to place results in separate columns. Any ideas?
RP
2010-03-04 15:31:43
produce a CSV file with .XLS file extension
KM
2010-03-04 15:34:22
you should actually use `sqlcmd Utility` http://msdn.microsoft.com/en-us/library/ms162773.aspx, don't know what I was thinking, osql is going to be removed, doh!
KM
2010-03-04 15:38:20
Create a linked server to the excel file. Use SQLCMD to put the data in to the correct columns.
G Mastros
2010-03-04 15:39:44
here is a good link: http://mssqltips.com/tip.asp?tip=1202
KM
2010-03-04 15:43:36
Thanks. It is useful and I hope will surely resolve my issue :)
RP
2010-03-04 16:37:44
+2
A:
Convince your company to stop being so obtuse -- you will end up writing a .NET app that acts as a service anyway -- its swings and roundabouts.
Hassan Syed
2010-03-04 15:29:02
A:
Use Windows Scheduled Tasks and a VBScript. The VBScript connects to the database to run the query and populates the Excel file with the results.
The following URL is an example.
http://digiassn.blogspot.com/2006/10/vbscript-populating-excel-file-from.html
Darryl Peterson
2010-03-04 16:19:53