MS SQL Server DTS - Printing in DTS Activex script

Author

Printing in DTS Activex script

etcarine via SQLMonster.com

2005-07-19, 8:24 pm

I have a package that runs every day and prints out a series of reports. The
reports are generated into text files and printed by feeding the file name to
a Word application object and then using the PrintOut method to print the
file. I also select the printer. On occasion, say once or twice a month, I
get the following error:
Error Description: The remote server machine does not exist or is unavailable:

'appWord.Documents'
Error on Line 162:
<<Set objWord = appWord.Documents.Open(strFileName, False, True, , , , , , , ,

"Plain Text")>>
I can't tell what the message means, as the file (strFileName) and the dts
package are on the same computer. (the printer is on a remote computer).

How can I make it more reliable?

The VB source code of the print function is below:

'===================
====================
=====
Function mcPrint(strFileName,
strPrinterName)
'===================
====================
=====
Const wdOrientLandscape = 1
Const wdDoNotSaveChanges = 0
Const wdLineSpaceExactly = 4
Const wdLineSpaceAtLeast = 3

Dim FileName
Dim aRange
Dim thatTime
Dim thisTime
Dim strCurrentPrinter
Const acPRDPVertical = 2

mcPrint = DTSTaskExecResult_Su
ccess
If strPrinterName = "" Then
WriteToLog strFileName & " NOT printed.", 0
Exit Function
End If

Set objWord = appWord.Documents.Open(strFileName, False, True, , , , , , ,
, "Plain Text")
Set aRange = appWord.ActiveDocument.Range
aRange.WholeStory
aRange.Font.Name = DTSGlobalVariables("Font").value
aRange.Font.Size = CInt(DTSGlobalVariab
les("Size").value)

appWord.ActiveDocument.Paragraphs.LineSpacingRule = wdLineSpaceExactly
appWord.ActiveDocument.Paragraphs.LineSpacing = CInt(DTSGlobalVariab
les
("Size").value)

appWord.ActiveDocument.PageSetup.Orientation = wdOrientLandscape
appWord.ActiveDocument.PageSetup.TopMargin = CInt(DTSGlobalVariab
les
("TopMargin").value)
appWord.ActiveDocument.PageSetup.BottomMargin = CInt(DTSGlobalVariab
les
("BottomMargin").value)
appWord.ActiveDocument.PageSetup.LeftMargin = CInt(DTSGlobalVariab
les
("LeftMargin").value)
appWord.ActiveDocument.PageSetup.RightMargin = CInt(DTSGlobalVariab
les
("RightMargin").value)
appWord.Options.PrintBackground = False

iSpot = InStr(1, strCurrentPrinter, strPrinterName)

objWord.PrintOut
WriteToLog "Printed " & strFileName & " on " & strPrinterName, 0

mcPrint = DTSTaskExecResult_Su
ccess

End Function 'mcPrint

Darren Green

2005-07-20, 8:24 pm

In message < 5190F80E83C0D@webser
vertalk.com>, etcarine via webservertalk.com
<forum@webservertalk.com> writes
>
>I have a package that runs every day and prints out a series of reports. The
>reports are generated into text files and printed by feeding the file name to
>a Word application object and then using the PrintOut method to print the
>file. I also select the printer. On occasion, say once or twice a month, I
>get the following error:
>Error Description: The remote server machine does not exist or is unavailable:
>
>'appWord.Documents'
>Error on Line 162:
><<Set objWord = appWord.Documents.Open(strFileName, False, True, , , , , , , ,
>
>"Plain Text")>>
>I can't tell what the message means, as the file (strFileName) and the dts
>package are on the same computer. (the printer is on a remote computer).
>
>How can I make it more reliable?
>

Don't use Office automation, it is just not designed for unattended use
like this.

Have you tried searching the MS KB for the error message. There are a
couple of articles that mention it, nothing matching, but gives you a
feel.

I'd suggest that your lack of clean-up code could cause issues, with old
instances floating about. You really should be use the Close/Quit type
methods on all automation objects such as this. Also Set to Nothing when
done.

You may still see winword.exe processes floating about though. (Have you
looked?). I have used scheduled jobs with the kill command to ensure I
clean up fully after such jobs.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

etcarine via SQLMonster.com

2005-07-21, 9:23 am

Darren,
Good thinking. The surrounding code that calls mcPrint looks like this:
<<
DTSGlobalVariables("InitialPrinter").value = appWord.ActivePrinter
ConnectToPrinter (DTSGlobalVariables(
"Printer").value)
appWord.ActivePrinter = DTSGlobalVariables("Printer").value
WriteToLog "Changed Active Printer from " & DTSGlobalVariables
("InitialPrinter").value & " to " & DTSGlobalVariables("Printer").value, 1

mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
("FileName_1").value, DTSGlobalVariables("Printer").Value
mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
("FileName_2").value, DTSGlobalVariables("Printer").Value
mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
("FileName_3").value, DTSGlobalVariables("Printer").Value
mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
("FileName_4").value, DTSGlobalVariables("Printer").Value

appWord.ActivePrinter = DTSGlobalVariables("InitialPrinter").value
WriteToLog "Changed Active Printer back to " & DTSGlobalVariables
("InitialPrinter").value, 1
appWord.Quit wdDoNotSaveChanges
Set objWord = nothing
Set appWord = nothing
Set objNetwork = nothing
WriteToLog "Quit Winword.", 1[color=darkred]
So I do a fair amount of cleaning up. I'll check again to see if any Winword.
exe's are left around, but my guess is that it's ok. If I don't use VBA what
would I do? I need to send the reports to a printer, and the version of SQL
Server/Windows that the production system is running on doesn't even have a
printer object. In fact, if I allow the package to run under the System
session, it can't see the printers at all.
Regards,
Edwin

--
Message posted via http://www.webservertalk.com

Darren Green

2005-07-21, 8:24 pm

In message < 51A6E5A0FFA1A@webser
vertalk.com>, etcarine via webservertalk.com
<forum@webservertalk.com> writes
>
>Darren,
>Good thinking. The surrounding code that calls mcPrint looks like this:
><<
>DTSGlobalVariables("InitialPrinter").value = appWord.ActivePrinter
> ConnectToPrinter (DTSGlobalVariables(
"Printer").value)
> appWord.ActivePrinter = DTSGlobalVariables("Printer").value
> WriteToLog "Changed Active Printer from " & DTSGlobalVariables
>("InitialPrinter").value & " to " & DTSGlobalVariables("Printer").value, 1
>
> mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
>("FileName_1").value, DTSGlobalVariables("Printer").Value
> mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
>("FileName_2").value, DTSGlobalVariables("Printer").Value
> mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
>("FileName_3").value, DTSGlobalVariables("Printer").Value
> mcPrint DTSGlobalVariables("FilePath").Value & DTSGlobalVariables
>("FileName_4").value, DTSGlobalVariables("Printer").Value
>
> appWord.ActivePrinter = DTSGlobalVariables("InitialPrinter").value
> WriteToLog "Changed Active Printer back to " & DTSGlobalVariables
>("InitialPrinter").value, 1
> appWord.Quit wdDoNotSaveChanges
> Set objWord = nothing
> Set appWord = nothing
> Set objNetwork = nothing
> WriteToLog "Quit Winword.", 1
>So I do a fair amount of cleaning up. I'll check again to see if any Winword.
>exe's are left around, but my guess is that it's ok. If I don't use VBA what
>would I do? I need to send the reports to a printer, and the version of SQL
>Server/Windows that the production system is running on doesn't even have a
>printer object. In fact, if I allow the package to run under the System
>session, it can't see the printers at all.
>Regards,
>Edwin
>
>

You probably don't have much choice, as the alternatives would be far
too much work, but the technology is not designed for the job, so it may
have issues from time to time. Watch for the exe's, as I've found that
to be an issue in the past. Rouge instances hang around and confuse
things later. As I mentioned, kill.exe can be used to fix that quite
well.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

etcarine via SQLMonster.com

2005-07-22, 1:23 pm

Darren,
There are no Winword.exe's laying around on the server, at this time.
Ofcourse it hasn't failed since June 30th. Where do I get Kill.exe? And then
I would add a line something like "execute Kill.exe Winword"? To make sure
there are none running when the dts package runs?
Regards,
Ed

Darren Green wrote:
>[quoted text clipped - 31 lines]
>
>You probably don't have much choice, as the alternatives would be far
>too much work, but the technology is not designed for the job, so it may
>have issues from time to time. Watch for the exe's, as I've found that
>to be an issue in the past. Rouge instances hang around and confuse
>things later. As I mentioned, kill.exe can be used to fix that quite
>well.
>

--
Message posted via http://www.webservertalk.com

Darren Green

2005-07-27, 8:24 pm

In message < 51B658CCF45DE@webser
vertalk.com>, etcarine via webservertalk.com
<forum@webservertalk.com> writes
>
>Darren,
>There are no Winword.exe's laying around on the server, at this time.
>Ofcourse it hasn't failed since June 30th. Where do I get Kill.exe?

Windows resource kit. Should be available to download on the MS site.

>And then
>I would add a line something like "execute Kill.exe Winword"? To make sure
>there are none running when the dts package runs?
Add a CmdExec step to the job or a Exec Proc Task to the start (and
maybe end) of the package.

kill winword.exe

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

etcarine via SQLMonster.com

2005-07-29, 11:24 am

Darren,
It failed again yesterday. I checked for leftover winword.exe's and there
weren't any.
the log file reads:

Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: The remote server machine does not exist or is unavailable:
'appWord.Documents'

Error on Line 173
<<Line 173 is the open file line as previously posted>>
Step Error code: 800403FE
Step Error Help File:sqldts.hlp
Step Error Help Context ID:1100

Where can I find the error code and the context ID? This thing prints 28
files a week for 2 or 3 weeks in a row and then fails. There has to be a
reason for it.
Edwin

Darren Green wrote:
>
>Windows resource kit. Should be available to download on the MS site.
>
>Add a CmdExec step to the job or a Exec Proc Task to the start (and
>maybe end) of the package.
>
>kill winword.exe
>

--
Message posted via http://www.webservertalk.com

Sponsored Links