How to Print a Document from SQL Stored Procedure

Introduction

There're many different ways of pulling data out of the database and then format/print it according to your needs. The code bellow allows you to print a document from a stored procedure using a SQL OLE and VB DLL. ( Original Article - At Siccolo Articles archive

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.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.

---

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)=''
)
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

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 6 of 6 (Total in Forum: 6) (Refresh) FirstPrevNext

How to Print a Document from page m2kannan 23:37 16 Apr '09   hi, i am new in vb.net i did my billing project. how to print sales page entry without go report? tat mean page itself take print out? thx in advance "Success is When Ur Signature Becomes An Autograph" foreverkans@gmail.com Sign In·View Thread·PermaLink How to Print adam amin 2:05 29 Oct '08   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 Sign In·View Thread·PermaLink Can already call COM objects in SQL 2000 trevorde stickman 14:31 14 Feb '07   Hi there are already functions in SQL 2000 for calling COM objects. The advantage of this approach is that it would allow you to separately develop/test/debug a single COM object using VB rather than a stored procedure. Kind regards Trevor D'Arcy-Evans Sign In·View Thread·PermaLink Re: Can already call COM objects in SQL 2000 aleksisa 9:31 15 Feb '07   right... Got SQL Server? Manage it with Siccolo! Sign In·View Thread·PermaLink 1.00/5 (1 vote) Licencing issue trevorde stickman 14:23 14 Feb '07   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 Sign In·View Thread·PermaLink Re: Licencing issue aleksisa 9:29 15 Feb '07   Thank you for your warm comments. Article shows two things - "unlimited" capabilities of SQL Server and how to use SQL Server to automate various business tasks, such as printing Instead of MS Word, as in this article, you can use a different product. Got SQL Server? Manage it with Siccolo! Sign In·View Thread·PermaLink 1.00/5 (1 vote)

Last Visit: 3:16 9 Nov '09     Last Update: 3:16 9 Nov '09 1