We tend to create a job queue and have a special user, with the appropriate permissions, on the Task Scheduler, to do anything that involves OLE automation or .Security precautions can get quite complex, but they are outside the scope of the article.Some of what we illustrate can be done using DTS or SSIS.
This will enable you to do anything you can do via keystrokes, and allow you to generate full Excel reports with pivot tables and Graphs. If you have your security wide open, it is not just you who would be able to write out data as a spreadsheet.
Using this technique, you should be able to populate the data, or place data in particular calls or ranges. An intruder would be able to do it with that list of passwords or credit-card numbers.
In a production system, this sort of operation needs to be properly ring-fenced.
Phil and I have teamed up on this workbench, which demonstrates how to create, read, update and delete information in Excel using T-SQL, from SQL Server.
As always, the workbench is structured so that it can be pasted into Query Analyser and SSMS, and the individual examples executed – you can download the from the “Code Download” link at the bottom of the article, load it up and start experimenting!
We start by showing you how to create an Excel Spreadsheet from SQL Server in TSQL(Transact SQL), create a worksheet, attach to it as a linked server, write to it, read from it, update it as if it was an ordinary SQL Server Database table, and then synchronise the data in the worksheet with SQL Server.
We also illustrate the use of ) to create and populate an Excel spreadsheet from Transact SQL.
Chatting Flirtatiously Knowing What to Say Getting Dates Online Community Q&A Want to get your flirt on via chat? You can learn to improve your flirtation and conversation skills online, as well as how to get dates and make connections on the Internet to give yourself the best chance of meeting someone.
The need to produce Excel reports from SQL Server is very common.
Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL.
The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on.