January 1, 2008 at 10:31 pm
hi guys i managed to create a linked server between my test server and an excel file but i cant export data from my table to the excel file anyone know the script for this?? thanks in advance and happy new year to all...
"-=Still Learning=-"
Lester Policarpio
January 2, 2008 at 2:47 am
It should be possible. You don't quite explain why you can't do it. You'll need to tell us because otherwise we have to guess. Have you looked at ...
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
...
the most common error is that people leave the Excel file open in Excel application (to see what's going on) and lock out the SQL Server from being able to access it.
Best wishes,
Phil Factor
January 2, 2008 at 6:14 pm
Wow!!!!! great article Phil this is very informative.. I will try to learn this I think my question will be answered in this article thanks..
"-=Still Learning=-"
Lester Policarpio
January 2, 2008 at 6:58 pm
One question.. is there an existing script that we can use to automatically resize the excel cells and automatically put a border to an excel file so that when i automatically send an email report to my superior with an excel attachment it is already with borders and spacing??
"-=Still Learning=-"
Lester Policarpio
January 3, 2008 at 2:02 am
I expect there is some VBscript code somewhere that shows how to do it, but I don't know of any existing TSQL that does it
There should be enough examples in the article to get you started. I'd see what there is on MSDN or one of the Excel technical sites, and then translate what they do into TSQL OLE automation. You'll need to develop the final example of the article rather than try to use the linked server approach. Once one gets beyond the simple reading and writing of data one has to use more powerful magic.
For a regular report, I agree that it is worth 'going the extra mile'. One thought, though: could one merely write into a pre-prepared excel file, all already nicely formatted, with the data range pre-defined? I haven't tried it but I don't see why not.
Best wishes,
Phil Factor
January 3, 2008 at 5:50 am
Excel provides some wonderful options to get data from SQL Server (or any ODBC compliant source) -- external data source, external query, pivot from external source, etc. Because you can set the option to refresh the data on worksheet open, it is often easier to create the initial workbook in Excel and save the programming on the server side. (When your only tool is a hammer...)
January 3, 2008 at 5:09 pm
Thanks for the responses really this article is great i just adapted the sp_makewebtask and it works perfectly..
"-=Still Learning=-"
Lester Policarpio
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply