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