Scheduling crystal Reports uisng DTS
-
Hi
Can anyone let me know how to schedule crystal reports usng DTS.I have seen some VB script for scheduling access reports.Lookng for similar kind of script.Any input is appreciated.
Thanks
Nanda
-
Your going to have to use ActiveScripting and the VBScript examples you saw. We have something doing this now and I can share the code just email me, however keep in mind Crystal has to be installed on the server or location that will start the DTS package.
-
this may not be what your looking for, but I use the crystal report exporter, write a batch file and call it with the windows scheduler. You need .net framework on the box but that is it. Its a freebie available here http://www.rainforestnet.com/crexport-download.htm
Good Luck
Brian
-
Would you be generous to add me to share the code. It is just what we were discussing in
our weekly group meeting. TIA, Joe..... krzemijm@nv.doe.gov
-
-
Note first I did have to fiddle with Crystal Runtime an job as SQLAgent couldn't use Cyrstal under system account. I set to use a admin account and it worked great.
Anyway I didn't expect to see so many folks needing this sort of thing but I go a good half dozen requests for the code. Would have put out yesturday but we had to find the origianl code which I
was told never worked on the export piece and was canned. Anyway I went over it and found it was becaue the other developer tried useing the named constants for defined data types. I used VB to get
the values and added a const section ad all worked.
So for those who asked for it, here is what I did. Hope it helps all and I may have to add an article on this myself.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
' Format types others have to be defined, you can use VB to find out the constant value if not here.
' Note: some types not valid with some crytal runtimes.
Const crEFTExcel80 = 29
Const crEFTWordForWindows = 14
Const crEFTText = 8
Const crEFTCommaSeparatedValues = 5
Const crEFTExactRichText = 35
Const crEFTPortableDocFormat = 31
' Destination Types, again you can use VB to get other values for const def if you need to.
Const crEDTDiskFile = 1
Dim Conn 'ADO Connection
Dim Comm 'ADO Command
Dim Rcdset 'ADO Recordset
Dim App 'Crystal Application
Dim Report 'Crystal Report
Dim DB 'Crystal Database
Dim Tables 'Crystal DatabaseTables
Dim Table1 'Crystal DatabaseTable
'On Error Resume Next ' This example was designed without error handling you need to add your own.
' -------------------- Build and get our recordset for our report. --------------------
Set Conn = CreateObject("ADODB.Connection")
Set Comm = CreateObject("ADODB.Command")
Set rcdset = CreateObject("ADODB.Recordset")
Conn.Open "Provider=SQLOLEDB.1;Initial Catalog=DBNAME;App=Crystal Export;Data Source=Server", "Username", "Password" ' You're database connection
Comm.ActiveConnection = Conn
Comm.CommandText = "ipMySPWith1InputForTesting '20040101'" 'Your sql string or SP.
Set Rcdset = Comm.Execute
' Process for no records to be exported.
If Rcdset.EOF Then
' -------------------- Clean up all our objects we created so far. --------------------
' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing
Main = DTSTaskExecResult_Success ' Set as success as I don't need to know for my needs, set to fail it you do.
Exit Function
End If
' -------------------- On to processing a crystal report. --------------------
' Set our application runtime environment for this process. Make sure you set this to the right runtime value.
'Set App = CreateObject("CrystalDesignRuntime.application.8.5") ' This is on a machine with Crystal 8.5
Set App = CreateObject("crystaldesignruntime2.application.net") ' This is on a machine I installed VB.NET on.
' Open our report.
Set Report = App.OpenReport("C:\CrystalExports\Reports\test.rpt") ' The path of your crystal report.
' Discard any existing data that was saved with the report.
Report.DiscardSavedData
' Set our base objects for processing this report.
Set DB = Report.Database
Set Tables = DB.Tables
Set Table1 = Tables.Item(1)
' Set our data set to the recordset we pulled.
Table1.SetPrivateData 3, Rcdset
' Read the records in.
Report.ReadRecords
' Set our export options
Report.ExportOptions.DestinationType = crEDTDiskFile
Report.ExportOptions.FormatType = crEFTPortableDocFormat
Report.ExportOptions.DiskFileName = "C:\CrystalExports\diditwork.pdf" ' Path to where to drop PDF.
Report.ExportOptions.PDFExportAllPages = True
' Export report without user interaction.
Report.Export false
' -------------------- Clean up all our objects we created. --------------------
' Crystal Objects
Set Table1 = nothing
Set Tables = nothing
Set DB = nothing
Set Report = nothing
Set App = Nothing
' ADO Objects
Rcdset.Close
Set Rcdset = Nothing
Set Comm = Nothing
Conn.Close
Set Conn = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Hi,
I am trying to adapt the code above to my requirements and have put it in a DTS package inside ActiveX script. However I dont seem to be able to get it to work.
i am using SQL Server 2000 and Crystal 8.5.
I am getting an error message saying that I am unable to create the crystal object. i have looked this on the internet and installed/registered the required dll:-
regsvr32 c:\windows\system32\cdo32.dll
If anyone has any ideas or can point me to a good website??
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply