| 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
|
|
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky � 2006