Click here to Skip to main content
15,867,278 members
Articles / Programming Languages / SQL

How to Print a Document from SQL Stored Procedure

Rate me:
Please Sign up or sign in to vote.
3.29/5 (11 votes)
6 Feb 2007CPOL 74.3K   38   8
Print a document from a stored procedure using a SQL OLE and VB DLL

Introduction

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:

VB.NET
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.Activate
    'objWord.PrintOut FileName:=DocumentFileName
    Dim objDocument As Word.Document

    Set objDocument = objWord.Documents.Open(DocumentFileName)

    objDocument.Activate

    objDocument.PrintOut

    objDocument.Close

    objWord.Quit

Exit_Procedure:
    Set objDocument = Nothing
    Set objWord = Nothing

    Exit Sub

Err_Error:

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

    If Not objWord Is Nothing Then
        objWord.Quit
    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:

SQL
CREATE procedure sp_Print_Letter_File
(
    @file_name varchar(333)
    ,@debug_mode char(1)=''
)
as
    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!
    begin
           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
    end

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

    exec @hr = sp_OAMethod @print_document, 'PrintDocumentFromWord',_
	null,@file_name, @debug_mode
    if @hr <> 0
    begin
           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
        begin
            exec sp_OAGetErrorInfo @print_document, @src out, @desc out
            select 'Destroing Instance',hr=convert(varbinary(4),@hr), _
		source=@src, description=@desc
            --return
        end
        set @return = -2
        goto error

    end

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

error:

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

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

set nocount off

GO   

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.

History

  • 6th February, 2007: Initial post

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 1 Pin
Dave Kreskowiak15-Feb-10 9:28
mveDave Kreskowiak15-Feb-10 9:28 
QuestionHow to Print a Document from SQL Stored Procedure Pin
Carlos Davila12-Nov-09 5:32
Carlos Davila12-Nov-09 5:32 
QuestionHow to Print a Document from page Pin
JC.KaNNaN16-Apr-09 22:37
JC.KaNNaN16-Apr-09 22:37 
QuestionHow to Print Pin
adam amin29-Oct-08 1:05
adam amin29-Oct-08 1:05 
Hi, can anyone help me out with a correction or an alternative? Im using VB6.0 for my library Program and Im having difficulties in linking communication with my Ms Access Database and my printer, it displays printer 'run-time error 482' . I have the following code.

Private Sub CmdPRINT_Click()
If Opt1(0).Value = True Then
' Use 1 inch margins.
Const TOP_MARGIN = 1440
Const LEFT_MARGIN = 1440
Dim bottom_margin As Single
Dim mydb As Database
Dim qdef As QueryDef
Dim mytable As Recordset

MousePointer = vbHourglass
DoEvents

' Open the database.
Set mydb = Workspaces(0).OpenDatabase(App.Path & "\amisoft.mdb")
Set mytable = mydb.OpenRecordset("books", dbOpenTable)

Set qdef = mydb.CreateQueryDef("", _
"SELECT accession, author, isbn, title, Year FROM books WHERE section = 'Circulation'")
Set mytable = qdef.OpenRecordset(dbOpenSnapshot)

' Read the data and print it.
bottom_margin = Printer.ScaleTop + _
Printer.ScaleHeight - 1440
mytable.MoveFirst

Printer.CurrentY = TOP_MARGIN
Do While Not mytable.EOF
' Use rs!FieldName to get the data for
' the field named FieldName.
Printer.CurrentX = LEFT_MARGIN
Printer.Print

' See if we have filled the page.
If Printer.CurrentY >= bottom_margin Then
Printer.NewPage
Printer.CurrentY = TOP_MARGIN
End If
mytable.MoveNext
Loop
mytable.Close
mydb.Close

' Finish printing.
Printer.EndDoc

MousePointer = vbDefault
End If
End Sub
QuestionCan already call COM objects in SQL 2000 Pin
trevorde stickman14-Feb-07 13:31
trevorde stickman14-Feb-07 13:31 
AnswerRe: Can already call COM objects in SQL 2000 Pin
aleksisa15-Feb-07 8:31
aleksisa15-Feb-07 8:31 
GeneralLicencing issue Pin
trevorde stickman14-Feb-07 13:23
trevorde stickman14-Feb-07 13:23 
GeneralRe: Licencing issue Pin
aleksisa15-Feb-07 8:29
aleksisa15-Feb-07 8: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.