Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
How to Print a Document from SQL Stored Procedure using OLE Automation and T-SQL
(using SQL Server OLE Methods: sp_OACreate sp_OAMethod sp_OADestroy sp_OAGetErrorInfo)
  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.
  At First, let's build a simple VB DLL (ActiveX component) that would habndle 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.

  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



	
Side Note:
OLE Automation Objects in Transact-SQL
The OLE Automation - T-SQL includes several system stored procedures that allow OLE Automation objects to be referenced in Transact-SQL batches, stored procedures, and triggers. . These system stored procedures run as extended stored procedures, and the OLE Automation objects that are executed through the stored procedures run in the Microsoft® SQL Server™ address space in the same way that an extended stored procedure runs. The OLE Automation stored procedures allow Transact-SQL batches to reference SQL DMO objects and custom OLE Automation objects, such as objects that expose the IDispatch interface. A custom in-process OLE server created using Microsoft Visual Basic® must have an error handler (specified with the On Error GoTo statement) for the Class_Initialize and Class_Terminate subroutines. The error handlers prevent unhandled errors from occurring in these subroutines. Unhandled errors in the Class_Initialize and Class_Terminate subroutines can cause unpredictable errors, such as an SQL Server access violation. Error handlers for other subroutines are also recommended.
The first step when using an OLE Automation object in Transact-SQL is to call the sp_OACreate system stored procedure to create an instance of the object in the SQL Server address space.
    After an instance of the object has been created, call these stored procedures to work with the properties, methods, and error information related to the object:
  • sp_OAGetProperty obtains the value of a property.
  • sp_OASetProperty sets the value of a property.
  • sp_OAMethod calls a method.
  • sp_OAGetErrorInfo obtains the most recent error information.

When there is no more need for the object, call sp_OADestroy to deallocate the instance of the object created with sp_OACreate. OLE Automation objects return data through property values and methods. sp_OAGetProperty and sp_OAMethod return these data values in the form of a result set. The scope of an OLE Automation object is a batch. All references to the object must be contained in a single batch, stored procedure, or trigger.


for more OLE Automation samples see article How to Create Stored procedure to convert Excel file into SQL table using OLE Automation and T-SQL

Article keywords: sp_OACreate, sp_OAMethod, sp_OADestroy, sp_OAGetErrorInfo, ActiveX, On Error GoTo, Word.Application, Word.Document, CREATE procedure, set nocount, Activate, Word.Document.PrintOut


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.