March 11, 2015 at 8:55 am
Hello,
How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)
Thanks
March 11, 2015 at 9:05 am
do you mean dynamically select a query and result set, and use SSRS to export it to doc/docX? if that's the question, i don't believe you can. i'm awful sure SSRS requires the report to be defined first, but i'd love to be wrong.
you can call a report via TSQL, and if it has a subscription that sends a specific report to disk, that can be done via TSQL.
for adhoc/programmatic queries, i use various CLR's i wrote to export to disk in html and other text-based formats. pdf/word/true excel are a lot harder.
Lowell
March 11, 2015 at 9:14 am
n.oruc (3/11/2015)
Hello,How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)
Thanks
**This assumes you already have the report in place**
I personally would create a report subscription that would export the report to a fileshare.
Once this is done you can then execute the job using EXEC ReportServer.dbo.AddEvent or execute to SQL Server job it creates
Credit to Sankar Reddy[/url] for the script below
[font="Verdana"]SELECT CAST(b.name AS UNIQUEIDENTIFIER) AS JobName ,
e.Name ,
e.Path ,
d.Description ,
a.SubscriptionID ,
LastStatus ,
EventType ,
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS NVARCHAR(50)) = CAST(b.name AS NVARCHAR(50))
JOIN ReportServer.dbo.ReportSchedule c ON CAST(b.name AS NVARCHAR(50)) = CAST(c.ScheduleID AS NVARCHAR(50))
JOIN ReportServer.dbo.Subscriptions d ON CAST(c.SubscriptionID AS NVARCHAR(50)) = CAST(d.SubscriptionID AS NVARCHAR(50))
JOIN ReportServer.dbo.Catalog e ON CAST(d.Report_OID AS NVARCHAR(50)) = CAST(e.ItemID AS NVARCHAR(50))[/font]
March 11, 2015 at 9:28 am
I want genereting report in word format and send by mail.
I thing.
First prepared report in SSRS.(I done)
Second write stored procedure (T-SQL) once a day this stored procedure will execute by JOB
Procedure must execute and export this report to word format in file system (Can not do it help please)
Third with sp_send_dbmail send this file by mail(I done)
March 11, 2015 at 9:34 am
Yes i want call a report and export this report to word format in file system via TSQL and then send this file by mail:-)
March 11, 2015 at 9:37 am
sql_lock (3/11/2015)
n.oruc (3/11/2015)
Hello,How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)
Thanks
**This assumes you already have the report in place**
I personally would create a report subscription that would export the report to a fileshare.
Once this is done you can then execute the job using EXEC ReportServer.dbo.AddEvent or execute to SQL Server job it creates
Credit to Sankar Reddy[/url] for the script below
[font="Verdana"]SELECT CAST(b.name AS UNIQUEIDENTIFIER) AS JobName ,
e.Name ,
e.Path ,
d.Description ,
a.SubscriptionID ,
LastStatus ,
EventType ,
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS NVARCHAR(50)) = CAST(b.name AS NVARCHAR(50))
JOIN ReportServer.dbo.ReportSchedule c ON CAST(b.name AS NVARCHAR(50)) = CAST(c.ScheduleID AS NVARCHAR(50))
JOIN ReportServer.dbo.Subscriptions d ON CAST(c.SubscriptionID AS NVARCHAR(50)) = CAST(d.SubscriptionID AS NVARCHAR(50))
JOIN ReportServer.dbo.Catalog e ON CAST(d.Report_OID AS NVARCHAR(50)) = CAST(e.ItemID AS NVARCHAR(50))[/font]
How i can call a report and export this report to word format in file system via TSQL [Smile]
March 11, 2015 at 9:38 am
Lowell (3/11/2015)
do you mean dynamically select a query and result set, and use SSRS to export it to doc/docX? if that's the question, i don't believe you can. i'm awful sure SSRS requires the report to be defined first, but i'd love to be wrong.you can call a report via TSQL, and if it has a subscription that sends a specific report to disk, that can be done via TSQL.
for adhoc/programmatic queries, i use various CLR's i wrote to export to disk in html and other text-based formats. pdf/word/true excel are a lot harder.
Yes i want call a report and export this report to word format in file system via TSQL and then send this file by mail [Smile]
March 11, 2015 at 9:43 am
has the report already been created within SSRS and are you wanting to call directly from TSQL or the OS?
March 11, 2015 at 9:55 am
sql_lock (3/11/2015)
has the report already been created within SSRS and are you wanting to call directly from TSQL or the OS?
I am manually greate report in visual studio then i want execute(call) this report and export(save) report to(in) word format in file system via T-SQL from stored procedure
I can execute and export to word via PHP but i do this work via t-SQL
This is code via PHP
...........
define("REPORT", "/KADR/".$repname);
require_once 'SDKforPHP/bin/SSRSReport.php';
define("SERVICE_URL", "http://10.0.100.47/ReportServer/");
try
{
$ssrs_report = new SSRSReport(new Credentials('*******', '********'), SERVICE_URL);
if (isset($_REQUEST['rs:ShowHideToggle']))
{
$ssrs_report->ToggleItem($_REQUEST['rs:ShowHideToggle']);
}
else
{
$ssrs_report->LoadReport2(REPORT, NULL);
}
$per_id=$_POST['per_id'];
$executionInfo = $ssrs_report->LoadReport2(REPORT, NULL);
$parameters = array();
$parameters[0] = new ParameterValue();
$parameters[0]->Name = "per_id";
$parameters[0]->Value = $per_id;
$ssrs_report->SetExecutionParameters2($parameters);
$ssrs_report->LoadReport2(REPORT, NULL);
$renderAsWORD = new RenderAsWORD();
$result_WORD = $ssrs_report->Render2($renderAsWORD,
PageCountModeEnum::$Estimate,
$Extension,
$MimeType,
$Encoding,
$Warnings,
$StreamIds);
if (!$handle = fopen("Report/$file_name.doc", 'wb'))
{
echo "Cannot open file for writing output";
exit;
}
if (fwrite($handle, $result_WORD) === FALSE)
{
echo "Cannot write to file";
exit;
}
fclose($handle);
//echo "<center><a id='b' class='openme' href='Report/test.doc'>Hesabati WORD s?klind? a?</a>";
print "Report/test.doc";
}
catch (SSRSReportException $serviceException)
{
print("<pre>");
print_r($serviceException);
print("</pre>");
}
function getPageURL()
{
$PageUrl = $_SERVER["HTTPS"] == "on"? 'https://' : 'http://';
$uri = $_SERVER["REQUEST_URI"];
$index = strpos($uri, '?');
if($index !== false)
{
$uri = substr($uri, 0, $index);
}
$PageUrl .= $_SERVER["SERVER_NAME"] . ":" . $_SERVER["SERVER_PORT"] . $uri;
return $PageUrl;
}
March 11, 2015 at 10:09 am
there is no ability to directly render as any format (ie $renderAsWORD) from within SQL server; those functionalite are all possible with an exdtrnal resource.
you can do html, fixed width, csv or other delimited formats, but that's the only thing natively available.
html is just plain text, anyway.
here's a simple example of embedding the data as html in an email;
Declare @HTMLBody varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +
'<td align=center><b>Product</b></td>' +
'<td align=center><b>Provider</b></td>' +
'<td align=center><b>Data Source</b></td>' +
'<td align=center><b>Is Linked?</b></td></tr>';
Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @TableHead + @HTMLBody + @TableTail
-- output that is used for the body of an email
Select @HTMLBody
--this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:
/*
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name='My DBMail Profile',
@recipients='lowell@somedomain.com',
@subject = 'Simple Email Example',
@body = @HTMLBody,
@body_format = 'HTML'
--@body_format = 'TEXT'
Lowell
March 11, 2015 at 10:38 am
Lowell (3/11/2015)
there is no ability to directly render as any format (ie $renderAsWORD) from within SQL server; those functionalite are all possible with an exdtrnal resource.you can do html, fixed width, csv or other delimited formats, but that's the only thing natively available.
html is just plain text, anyway.
here's a simple example of embedding the data as html in an email;
Declare @HTMLBody varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +
'<td align=center><b>Product</b></td>' +
'<td align=center><b>Provider</b></td>' +
'<td align=center><b>Data Source</b></td>' +
'<td align=center><b>Is Linked?</b></td></tr>';
Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @TableHead + @HTMLBody + @TableTail
-- output that is used for the body of an email
Select @HTMLBody
--this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:
/*
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
*/
EXEC msdb.dbo.sp_send_dbmail
@profile_name='My DBMail Profile',
@recipients='lowell@somedomain.com',
@subject = 'Simple Email Example',
@body = @HTMLBody,
@body_format = 'HTML'
--@body_format = 'TEXT'
A lot off thanks i understand. I have another some question.
1)Can i call same php or javascript code via T-SQL?
2)What another way resolve my problem i must do it.
March 11, 2015 at 5:13 pm
You could just set up a subscription for the report and have that send the email or is that not suitable?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 13, 2015 at 1:20 am
mister.magoo (3/11/2015)
You could just set up a subscription for the report and have that send the email or is that not suitable?
Thanks all suitable.A don't knew about it.Now i understand.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply