February 23, 2012 at 8:40 am
Hi,
I'm trying to set up a process by which I can automate saving down the results of SQL Server queries as Excel spreadsheets.
I've perused the internet for a while looking for a solution.
One option seems to be to use SQL Agent to automate tasks. Unfortunately I seem to not have access/permissions to this.
Another alternative would be to automate the task from within Excel using VBA. However this is not ideal as it would require leaving a PC (probably mine), logged on overnight with the sheet.
Any suggestions?
February 23, 2012 at 8:50 am
you could take a look at ssis, to export data overnight from SQL to excel files, or use excel and embed a query into the spreadsheet so it can be refreshed at will, no need for it to be logged in overnight
February 23, 2012 at 8:57 am
Hi
Thanks for your speedy reply!
Unfortunately it looks like I don't have access to SSIS either, unless i'm missing something obvious.
The data I'm retrieving on SQL will change every day, so I do need to automate it to download the data and save it as a spreadsheet every day.
This is something I'd ideally to automate to run in my absence without someone else being responsible for running the queries.
February 23, 2012 at 9:16 am
in excel you can give it a connection which connects to sql and runs a query, this can be saved and refreshed with up to date data as and when required
so for example you can do
SELECT * FROM table1
which will give you all the results, you then save the excel file with that result set, then tomorrow come in open the excel file, re-run the query and it gives you any changes in data and anything else which was inserted
this is good if your saving everything to the same excel work book, if you want to do
SELECT * FROM table1 WHERE date = getdate()
and save a set of data to a particular file, your best off using SSIS, to use SSIS you need business intelligence development studio to develop dtsx files which will pull the data from SQL to excel
February 23, 2012 at 9:18 am
Powershell + windows scheduler?
__________________________
Allzu viel ist ungesund...
February 23, 2012 at 10:27 am
Thank you both for your replies.
I'm looking for something which takes away the manual process of running the excel query every day. There will hopefully be quite a few of these reports generated, and i'd also like them to run in my absence.
Unfortunately I don't have Business Intelligence development studio.
Mr Holio, i do seem to have access to Powershell but what i've read is somewhat ambiguous with regard to whether it works with the 2005 version of SQL Server.
I do not currently have access to task scheduler, but it might well be something i can get IT to activate. However, wouldn't I still need to be logged in for it to run?
February 23, 2012 at 10:34 am
Something like this should work for you..
$con = "server=localhost;database=YourDatabase;Integrated Security=sspi"
$cmd = "select * from dbo.YourTable"
$path = "c:\Rpt\MyReport_" + $(get-date -f yyyyMMdd) + ".csv"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$dt | Select-Object | Export-Csv $path -NoTypeInformation
__________________________
Allzu viel ist ungesund...
February 24, 2012 at 1:51 am
Do you have any DBA's or BI developers (forgive me if you are one, but not having BIDS usually suggests that your not as its a key tools in a DBA or BI's tool set). If so you could ask them to develop the SSIS packages for you and get them scheduled to run nightly.
If you already have the T-SQL to get the data it will help them out.
February 24, 2012 at 2:21 am
Thank you both again for your replies - it is very much appreciated!
Mr Holio, i've never used Powershell but i presume that is some kind of command set to run the SQL and save down the file.
I then can put this into windows scheduler to schedule to run? Do i need to be logged in for this to work?
Anthony - you're quite right, i dont work in BI, I work in a reporting function for a bank.
I could ask them to schedule my SQL code, but it's something i'd like to have more autonomy over (it can take a while to get things done inter-departmentally and my BI department is located in another country).
If it was something like Windows scheduler that i could gain permission to access and then use going forward that'd be perfect, but i have a suspicion i'd need to be logged in to use this.
Alex
Group: General Forum Members
Last Login: Today @ 8:48:55 AM
Points: 1,087, Visits: 1,090
Do you have any DBA's or BI developers (forgive me if you are one, but not having BIDS usually suggests that your not as its a key tools in a DBA or BI's tool set). If so you could ask them to develop the SSIS packages for you and get them scheduled to run nightly.
If you already have the T-SQL to get the data it will help them out.
February 24, 2012 at 2:28 am
Ah banking, now thats a whole different kettle of fish, you might have some problems with this due to the nature of the banking industry due to security regulations.
I would check with the powers that be, that what your doing will not affect the company in anyway and that things like powershell and windows task scheduler can actually be used as I'm not from a banking background.
February 24, 2012 at 4:02 am
Yep cheers,
I'll check with the powers that be to see if i can have access. I can see that task scheduler can be run without being logged in, so this would be a possible solution if they're willing to give me access.
Other departments already do the process i'm describing, but they probably have access to SSIS or development studio.
Thanks for your help - I may be back here to check on the mechanics of using the Powershell/Scheduler combination(!)
February 24, 2012 at 4:39 am
Just keep in mind that the default PS execution policy is restricted so you will need to get a sys admin to change it for you. Also, to create a task I believe you need to have admin rights so get the sys admin to sort this out as well...then he can set the job to run under your account and add you as a member of one of the policies (forgot which one) that allows you to run the job.
__________________________
Allzu viel ist ungesund...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply