Run a crystal report from TSQL
-
I need to email a statement to our clients that is printed by a crystal report. I thought about using xp_sendmail @query=sp_proc where sp_proc would execute the report receiving the clientid as a parameter. I have no idea on how to run a crystal report from T-SQL.
Other idea is, create the statement for all clients in a temp table and generate pdf files for each that would be attached to each client again using xp_sendmail. Problem is SQL generated statements don't like as fancy as crystal reports. Is it possible to add images, like a logo, to the temp table. I could use a blob as the first record and then print it to the temp as the X line for each statement. Sounds doable.
If anyone has a better idea, I would appreciate it, so I don't waste timetrying to invent something that might be already done.
Thanks
-
Raul,
Would not it be easier just to schedule Crystal Reports? If you don't have email functionality for Crystal then output files as PDF and write a short VBscript that will send email with pdf reports as attachments. We have another report tool that don't send emails. We output report results as PDF files to the share and then have a couple of VB scripts in the same folder that send emails with attachments. These scripts are just scheduled in Windows Task Scheduler for the time about 30 min later after the report tool runs reports and put them on the share.
Regards,Yelena Varsha
-
Or you could just use Reporting Services which has email delivery built-in
--------------------
Colt 45 - the original point and click interface
-
I guess the reporting services option should be one to consider.
The idea of using xp_sendmail and send the crystal as an attachment would be good if I could make crystal generate a file for each of our 3000 clients with active emails. Is it possible to run a report that would export a different file for each clientid? I think not. It would probably had to be generated from Visual. But I was looking for an option in T-SQL to be able to schedule it as a job or as a DTS package.
Any ideas how?
-
I created the next SP
Notes: All coments are in Spanish. I hope you can understand.
You need to register Crystal's Activex.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
Fecha: 17-Ene-2003
Creado por Carlos Fidel Restrepo
Se utiliza para generar reportes Crystal a disco o imprimirlos
NO SE PUEDEN GENERAR REPORTES EN SERIE (UNO TRAS OTRO) YA QUE EL SISTEMA NO ES CAPAZ DE PROCESARLOS.
Para imprimir se coloca en el segundo parámetro "@ArchSalida" "PRN_" + Nombre de impresora
La impresora debe estar creada en el server_2000 (Ya que este es el que envía la impresión) y no
la estaci+on de trabajo
*/
ALTER PROCEDURE dbo.DurSP_Crystal
-- input params . . .
@NobrRep VarChar(100), -- Nombre del reporte
@ArchSalida VarChar(100) Out, -- Nombre el archivo de salida (con su extensión)
@P1 VarChar(50) = '', -- 8 parámetros (deben ser caracter)
@P2 VarChar(50) = '',
@P3 VarChar(50) = '',
@P4 VarChar(50) = '',
@P5 VarChar(50) = '',
@P6 VarChar(50) = '',
@P7 VarChar(50) = '',
@P8 VarChar(50) = ''
As
-- declare variables . . .
Declare @oCR int,
@oRpt int,
@intResult Int,
@oDB Int,
@ocDBT Int,
@oDBT Int,
@ocParm Int,
@oParm Int,
@oExp Int,
@Falso Bit,-- Para poder enviar falso como parámetro
@NumPara SmallInt,-- Almacena el número de parámetros
@I SmallInt,-- contador
@TipoDoc SmallInt,-- Tipo de documento a exportar
@RutaRep VarChar(80),-- Ruta del reporte
@Param VarChar(50),-- Almacena el valor de los parámetros
@Impresora VarChar(50)-- Nombre de la impresora (sí se requiere)
Set @Falso = 0
Set @RutaRep = '\\Server_2000\Sifci\durespo\RepCrystal\'
-- Seleciona el tipo de documento a exportar
Set @TipoDoc = Case When Left(@ArchSalida,4) = 'PRN_' Then 222 -- Impresora
Else
Case Right(RTrim(@ArchSalida),3)
When 'PDF' Then 31
When 'XLS' Then 27
When 'DOC' Then 14
Else -1
End End
If @TipoDoc < 0 -- Some error
Begin
Select @ArchSalida + ' No válido' As Error
Return
End
-- Create MailMan COM object. . .
Execute @intResult = sp_OACreate 'CrystalRuntime.Application', @oCR Out
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
-- add smtp host
Set @NobrRep = @RutaRep + @NobrRep
Execute @intResult = sp_OAMethod @oCR, 'OpenReport', @oRpt Out, @NobrRep
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
-- Create the Database object
Execute @intResult = sp_OAMethod @oRpt, 'Database', @oDB Out
-- Get a references to the DatabaseTables collection
Execute @intResult = sp_OAMethod @oDB, 'Tables', @ocDBT Out
-- Get a reference to the DatabaseTable object for table 1
Execute @intResult = sp_OAMethod @ocDBT, 'Item', @oDBT Out, 1
-- Set the location
--.SetLogOnInfo('server_2000','durlocal','mac','mak') Conección directa
Execute @intResult = sp_OAMethod @oDBT, 'SetLogOnInfo', Null, 'Macola','Durespo','Mac','Mak'
-- Get the Special Message Parameter
Execute @intResult = sp_OAMethod @oRpt, 'ParameterFields', @ocParm Out
Execute @intResult = sp_OAMethod @ocParm, 'Count', @NumPara Out
-- Asigna los parámetros
Set @I = 1
While @I <= @NumPara
Begin
Select @Param = Case @I
When 1 Then @P1
When 2 Then @P2
When 3 Then @P3
When 4 Then @P4
When 5 Then @P5
When 6 Then @P6
When 7 Then @P7
When 8 Then @P8
End
Execute @intResult = sp_OAMethod @ocParm, 'Item', @oParm Out, @I
If @intResult 0 -- Some error
Begin
Select @I,@Param,@NobrRep,1
Execute sp_OAGetErrorInfo
Return
End
Execute @intResult = sp_OAMethod @oParm, 'SetCurrentValue', Null, @Param
If @intResult 0 -- Some error
Begin
Select @I,@Param,@NobrRep,2
Execute sp_OAGetErrorInfo
Return
End
Set @I = @I + 1
End
-- Efectúa la exportación
Execute @intResult = sp_OAMethod @oRpt, 'ExportOptions', @oExp Out
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
Execute @intResult = sp_OASetProperty @oExp, 'DestinationType', 1 --&& crEDTDiskFile
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
If @TipoDoc = 222 -- Impresora
Begin
Set @Impresora = Substring(@ArchSalida,5,60)
Set @ArchSalida = @Impresora
-- Selecciona Impresora
Execute @intResult = sp_OAMethod @oRpt, 'SelectPrinter', Null, 'Vacio', @Impresora, 'Vacio'
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
-- Imprime
Execute @intResult = sp_OAMethod @oRpt, 'PrintOut', Null, @Falso
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
-- Archivo impreso
End
Else
Begin
Execute @intResult = sp_OASetProperty @oExp, 'FormatType', @TipoDoc --&& Tipo de documento
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
-- Si se antepone 'PUB_' al nombre del archivo de salida, este se crea en el directorio "PrRemoto"
-- (Queda publico). Además agrega "server process identifier" al nombre del archivo para evitar conflictos de nombres
If Left(@ArchSalida,4) = 'PUB_' And Left(@@SERVERNAME,6) = 'SERVER'
Set @ArchSalida = '\\Server_2000\MacApps\PrRemoto\' + Cast(@@SPID As VarChar(5))
+ substring(@ArchSalida,5,100)
Else
If Left(@ArchSalida,4) = 'HST_' And Left(@@SERVERNAME,6) = 'SERVER' -- Crea el archivo en el historial
Set @ArchSalida = '\\Server_2000\ADatos1\Historial' + substring(@ArchSalida,5,100)
Else
-- Sí no se da una dirección de red el archivo se crea en el disco "C:" del servidor
If Left(@ArchSalida,2) '\\'
Set @ArchSalida = 'C:\Temp\' + @ArchSalida
Execute @intResult = sp_OASetProperty @oExp, 'DiskFileName', @ArchSalida
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
Execute @intResult = sp_OAMethod @oRpt, 'Export', Null, @Falso
If @intResult 0 -- Some error
Begin
Execute sp_OAGetErrorInfo
Return
End
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
I have a VB6 application i use to generate and email PDF attachments for Crystal Reports vsersions 8.5 thru 9.2 - you could bolt on the approperiate dlls for other versions.
The app lets you fill in report parameters, specify the file name for the pdf, and message text, subject and distribution list. I use it to generate scheduled reports and mail them to various distribution lists. It uses SNMP so you don't heed to have outlook configured on the box where it runs.
Send me a note if you're interested.
-
Hi, im trying to use the SP from cfrestrepo to email pdfs generated by crystal reports. But i cant create the crystal Runtime Object
Execute @intResult = sp_OACreate 'CrystalRuntime.Application', @oCR Out
Clase no Registrada
Class not registered
Im using sql2014 and Crystal Reports XI
I tryed to manually registering craxdrt.dll and a lot of other Crystal dll, but still not working
I read that crystal XI deprecated that com activex control so tried to use it with older dlls but nothing
Have you any other solution.
We really need generate crystal pdfs from sqlserver..
THX
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply