Av rating:
Total votes: 40
Total comments: 19

 CODE DOWNLOAD
 send to a friend
 printer friendly version

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:

• SpWord_Document_content – extracts the text from an MS Word file into a table of paragraphs, along with the name of the style of the original paragraph
• spExportToWord – creates a new Word file, and writes results from the SQL expression to a table in Word, with a heading if required.

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.

Reading from Word paragraphs to SQL Server

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.

drop table #MyContent
Create table #MyContent (
   Paragraph_ID int,
   Style varchar(20),
   Content Varchar(8000))
Insert into #myContent
   Exec spWord_Document_content @documentFile ='c:\FooBar.doc'

In case you are wondering whether it reads any tables and other content that happens to be in the way; it doesn't. It only reads the words of the paragraphs. The difficulties involved in providing a generic Word reader multiply at the point when one considers tables, and it is far easier to automate the saving of the file as text, and then pulling into SQL Server via the automation of Windows.FileSystemObject.

Exporting from SQL Server to Word

The spExportToWord T-SQL stored procedure creates a new Word file, and writes results to a Word table in that file. This allows you to autoformat the results in any of the standard ways that Word allows.

I decided to use ADODB, mainly in order to get the field-names of any result. However, the other advantage was that I could use it to connect to servers anywhere (I administer database-driven websites) without permanent links.

NOTE:
I also had the ambitious idea of allowing you to write several tables, using multiple recordsets from the one query. Sorry. Next time maybe

All you need to run this is to have Word installed on your server, and have configured SQL Server to allow OLE Automation. If you have a trusted connection, and the query is in your current database, then the syntax is simple:

Execute spExportToword @QueryText='MyQuery',
   @documentFile='C:\foobar.doc',@Tableformat='Colourful 1'

Or, if you have SQL Server security then you need to specify the password:

Execute spExportToword @QueryText='MyQuery',
   @SourceUID ='MyUserID', @SourcePWD='MyPassword'
   @documentFile='C:\foobar.doc',
   @Tableformat='Colourful 1'

If you don't like the 'professional' auto format that I chose by default ('colourful 1' is just one of the many built-in auto formats you can see in Word), then try this:

create table sample(
   [ ] varchar(80),
   [Software Sales] varchar(80),
   [Hardware Sales] varchar(80),
   [Consultancy] varchar(80),
   [Total] varchar(80))
insert into sample

   select 'First Quarter','£1940','£567','£765','£3272'
insert into sample
   select 'Second Quarter','£15960','£3685','£34000','£53645'
insert into sample 
   select 'Third Quarter','£39480','£5000','£23000','£67480'
insert into sample 
   select 'Fourth Quarter','£23960','£3549','£3470','£30979'
insert into sample 
   select 'Total','£81340','£12801','£61235','£155376'
execute spExportToword @QueryText='Select * from sample',
   @documentFile='C:\report.doc',@Tableformat='Grid 6'

This should result in the following table:

You can also provide a heading to the table to explain what the data is about.

execute spExportToword @QueryText='Select * from sample',
   @documentFile='C:\report.doc',@Tableformat='Grid 6',
   @tableHeading='2006 Earnings. The Kamakaze Laxative Company'

If you have a slightly more complex access requirement then you can specify the server, database, userid and password

spExportToword 
@Sourceserver='MyServer',
@SourceDatabase='MyDatabase',
@SourceUID='MyUserID',
@SourcePWD='MyPassword',
@QueryText='Select * from MyTable',
   @documentFile='C:\MyDocument.doc'

You can, of course, get serious about your connection string to get a report from a server managing a distant ecommerce site.

spExportToword @QueryText='spWho',
   @documentFile='C:\remotestuff.doc',
   @ConnectionString='Driver={SQL Server};
                      Server=xxx.xxx.xxx.xxx;
                      Address=xxx.xxx.xxx.xxx,1433;
                      Network=DBMSSOCN;
                      Database=myDatabaseName;
                      Uid=myUsername;
                      Pwd=myPassword'

Or, if you wish, you can connect to any ODBC source you can think of! The secret is in the connection string.

A few words of caution

Something like this, which is perfectly serviceable for small-scale applications, may not necessarily scale-up – please bear that in mind if you are considering putting this sort of code into a production server.

Whereas Excel is relatively well-behaved, MS Word is notorious for hitting a bug, forgetting that it is being automated as a background task without a user-interface, and popping up a system modal dialog box.

Also, bear in mind that OLE Automation has to be done by a process with extensive login roles. You can't give these sorts of roles to the ordinary user. It is much better to queue up jobs like this and have a scheduled process on SQL Server Agent to execute them, using a login with the correct roles.

Summary

There is much more that can be done, of course. It would not be a big problem to read from Word tables, and I'd have done it for you if I'd been able to think of a useful application. It would also be reasonably easy to write out a mixture of tables and paragraphs into a word file too. It all depends on the requirements of your application. Hopefully, armed with the code I include, it may give you the impetus to try things out.

However get in plenty of sandwiches and Jack Daniels before doing so, as this sort of task is not for the faint-hearted.