Phil Factor 6th March 2007
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Having helped Robyn with her Excel Workbench, I couldn’t get out of my head the idea of achieving the same effect with MS Word. After all, from the data viewpoint, MS Word documents are just a series of paragraphs and tables aren’t they? Surely, it should be easy to read and write data between SQL Server and Word.
Robyn backed away nervously at this point, to leave me full rein. There are reams of advice on why it was a very bad idea to do such a thing as read data from Word documents into SQL, and plenty of indignation at the idea of writing to Word. I always smell humbug when I hear this sort of talk. If Microsoft fail to do something properly with a product, one gets quasi-religious pronouncements from everywhere that it is not appropriate to use the product in this way, or it traduces one’s architectural design, or it should be done in C #.
Nevertheless, I quickly realized that there were plenty of obstacles in my way. For a start, I was puzzled by the lack of ODBC drivers for Word. You’d have thought that all one has to do is to indicate which table in the document you want to populate, and send it the result of the SQL. You’d be able to read the document attributes as if it was a built-in table and the contents of the paragraphs as if it were another.
The next indication I had that something was really amiss was when I started trying a few simple automation tasks with Word, using OLE Automation in TSQL. Whenever a mistake happened, a warning sound came through the speakers of the server, scaring the life out of a dozing DBA. It looked as if Word was never intended for such connectivity. This was strange, as Excel is so well-mannered in this respect, a tame creature that handles errors obligingly and does exactly what is said in the documentation. I was not emotionally prepared for recalcitrance on the scale I was confronted with.
Some perfectly reasonable OLE methods, taken from fully operational VBA examples, never worked. I waded through reams of example scripts, noting with some alarm the trepidation expressed by the authors of scripts on Technet (‘Two tables in a document via automation? Dear me’, and ‘we employed a stunt double to do the testing’). I could find almost no successful attempts at OLE automation via TSQL. I just had to design around them. I came close to throwing in the towel, but my stubborn streak took over. Dammit, I was going to succeed.
And here, at last, are two stored procedures, too long to show in the body of this article, but attached nevertheless:
If you want to follow along and test them out, download them now from the CODE DOWNLOAD link above, or from the direct text file links below.
This SpWord_Document_content T-SQL stored procedure is easy to use. It is a pretty straightforward automation of Word, though I’ve seen no stored procedure like it anywhere. Remember that you will need to have MS Word installed on the server you are executing the stored procedure from.
