Click here to Skip to main content
13,290,729 members (76,013 online)
Click here to Skip to main content
Add your own
alternative version


38 bookmarked
Posted 6 Feb 2007

How to Print a Document from SQL Stored Procedure

, 6 Feb 2007
Rate this:
Please Sign up or sign in to vote.
Print a document from a stored procedure using a SQL OLE and VB DLL


There are many different ways of pulling data out of the database and then format/print it according to your needs. The code below allows you to print a document from a stored procedure using a SQL OLE and VB DLL. The original article can be found at Siccolo Articles archive.

Step 1

At first, let's build a simple VB DLL (ActiveX component) that would handle the actual printing using Microsoft Word:

In ActiveX component, add a method that would print a document using Microsoft Word:

Public Sub PrintDocumentFromWord(ByVal DocumentFileName As String)

    On Error GoTo Err_Error

    Dim MethodName As String
    MethodName = ".PrintWebDocumentFromWord()"

    Dim strMessage As String

    Dim blnResult As Boolean

    Dim objWord As New Word.Application
    'Dim objWord As Object
    'Set objWord = CreateObject("Word.Application")

    Const wdAlertsNone = 0
    objWord.DisplayAlerts = wdAlertsNone

    'objWord.PrintOut FileName:=DocumentFileName
    Dim objDocument As Word.Document

    Set objDocument = objWord.Documents.Open(DocumentFileName)





    Set objDocument = Nothing
    Set objWord = Nothing

    Exit Sub


    'handle error here
    Call HandleError("PrintWebDocument", ApplicationName, MethodName, VBA.Error)

    If Not objWord Is Nothing Then
    End If

    Resume Exit_Procedure

End Sub

Compile and build ActiveX component. ActiveX component will reside on the same machine where SQL Server instance is running.

Step 2

Next, create a stored procedure that calls ActiveX component and passes file name of the document to be printed:

CREATE procedure sp_Print_Letter_File
    @file_name varchar(333)
    ,@debug_mode char(1)=''
    set nocount on

    declare @return int

    declare @print_document int
    declare @hr int
    declare @src varchar(255), @desc varchar(255)

    exec @hr = master.dbo.sp_OACreate 'PrintDocument.clsPrintDocument', _
	@print_document OUT
    if @hr <> 0    -- error creating instance!
           exec master.dbo.sp_OAGetErrorInfo  @print_document, @src out, @desc out
        select 'Creating Instance', hr=convert(varbinary(4),@hr), _
		source=@src, description=@desc
        set @return = -1
        goto error

if @debug_mode<>''
    print '1. created'

    exec @hr = sp_OAMethod @print_document, 'PrintDocumentFromWord',_
	null,@file_name, @debug_mode
    if @hr <> 0
           exec sp_OAGetErrorInfo @print_document, @src OUT, @desc OUT
        select 'Call to Method', hr=convert(varbinary(4),@hr), _
	Source=@src, Description=@desc

        exec @hr = sp_OADestroy @print_document
        if @hr <> 0
            exec sp_OAGetErrorInfo @print_document, @src out, @desc out
            select 'Destroing Instance',hr=convert(varbinary(4),@hr), _
		source=@src, description=@desc
        set @return = -2
        goto error


if @debug_mode<>''
    print '2. send to object'


    exec @hr = sp_OADestroy @print_document
    if @hr <> 0
        exec sp_OAGetErrorInfo @print_document, @src out, @desc out
        select 'Destroing Instance',hr=convert(varbinary(4),@hr), _
		source=@src, description=@desc
        set @return = -3

if @debug_mode<>''
    print '3. done!'

set nocount off


And that's it. Don't forget to register your ActiveX component (I usually do it with Component Services MMC snap-in).

If you would like to read more on this story - please take a look at Siccolo - Free Mobile Management Tool For SQL Server and more articles at Siccolo articles.


  • 6th February, 2007: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralLicencing issue Pin
trevorde stickman14-Feb-07 14:23
membertrevorde stickman14-Feb-07 14:23 
Hi I'm fairly certain that the licencing terms for MS Word would not allow it to be used from a service ie SQL server. However, the code does illustrate calling an ActiveX control from a SQL stored procedure quite well.

Kind regards
Trevor D'Arcy-Evans
GeneralRe: Licencing issue Pin
aleksisa15-Feb-07 9:29
memberaleksisa15-Feb-07 9:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171207.1 | Last Updated 6 Feb 2007
Article Copyright 2007 by aleksisa
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid