Exporting report to WordML

I have been trying to use the XML export along with an XSL file to try and
generate WordML files from reporting services, and so far have had no real
success. I have tried using the XSLT Inference Tool and following the VERY
limited documentation that exists for it, but it just does not want to work
for me.
So, I have 2 questions:
1) Is there any good information/examples of using Word 2003, the XSLT
Inference Tool, and/or Visual Studio to get RS to output WordML files?
2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
can understand not having one for the old formats since MS is trying to phase
them out, but they have an XML renderer, and they are pushing the WordML
format, so it would seem to make sense to release a WordML renderer...Anyone have any thoughts/Hints/Tips for this?
"jwattsCOL" wrote:
> I have been trying to use the XML export along with an XSL file to try and
> generate WordML files from reporting services, and so far have had no real
> success. I have tried using the XSLT Inference Tool and following the VERY
> limited documentation that exists for it, but it just does not want to work
> for me.
> So, I have 2 questions:
> 1) Is there any good information/examples of using Word 2003, the XSLT
> Inference Tool, and/or Visual Studio to get RS to output WordML files?
> 2) Why hasn't Microsoft released a WordML renderer for Reporting Services? I
> can understand not having one for the old formats since MS is trying to phase
> them out, but they have an XML renderer, and they are pushing the WordML
> format, so it would seem to make sense to release a WordML renderer...
>

exporting from SQL to an Outlook group or Dist List?

I need to export a list of name and email address fields from a SQL
table, in some format so that a user can take the file I generate and
Import it into Outlook and it will keep all the names together in a
Group or Distribution List rather than dumping them into the user's
main address book. Any ideas?

Francesco"Francesco" <francesco@.neurodesign.com> wrote in message
news:8b6199d0.0307310928.73359d1e@.posting.google.c om...
> I need to export a list of name and email address fields from a SQL
> table, in some format so that a user can take the file I generate and
> Import it into Outlook and it will keep all the names together in a
> Group or Distribution List rather than dumping them into the user's
> main address book. Any ideas?
> Francesco

Assuming you have SQL2000, you can use BCP or DTS to create a CSV file, or a
file in some other delimited format. As to how you import that into Outlook,
you will get a better response posting in an Outlook newsgroup - there are
several microsoft.public.outlook.* groups.

Simon

Exporting data as XML file

To get the data from the database table as an XML file we will use " for xml" clause in sql statement in SQLSERVER.

But i am not able to generate the following things in that XML file.

please let me know how to get the following details in XML file using sql statement?

<?xml version="1.0" encoding="UTF-8" ?>

- <listOfIDXRefData xmlns=some url here>

Try the T-SQL forum http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

-Jamie

Export XML data - stored procedure

I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...
Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...

e.g Sample XML file required...
<Person>
<Name>Raymond</Name>
<NickName>The Legend</NickName>
</Person>
<Person>
<Name>Peter</Name>
<NickName>The King</NickName>
</Person>

sp_configure'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure'Web Assistant Procedures', 1;
GO
RECONFIGURE
GO
sp_makewebtask @.outputfile='C:\MyExportFile.xml',
@.query='SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS',
@.templatefile='C:\Template.tpl'

Try these two links for code samples including generating the XML file from a database table, this is assuming you are using SQL Server 2000. If you are using SQL Server 2005 all the code you need is in the BOL (books online) because XML is native to SQL Server 2005. Hope this helps.

http://forums.asp.net/1026295/ShowPost.aspx

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

export to unicode textfile with tsql

Hello,
is it possible to write the he result of query to a unicode textfile instead
of an ansi textfile. i need this because i want to generate udl files on the
fly.
any help appriciated.
GerbenHi
You don't say how the file is created but...
osql has a -u parameter that creates a unicode file.
John
"gerben" wrote:

> Hello,
> is it possible to write the he result of query to a unicode textfile inste
ad
> of an ansi textfile. i need this because i want to generate udl files on t
he
> fly.
> any help appriciated.
> Gerben

Export to multiple PDFs on-demand?

Is there an easy way to export a single report to multiple PDF files
on demand? I know that I can use a Data-Driven Subscription to
generate multiple files, but if I want to give this ability to a user
to look at the report, decide that it's good, and then save as PDF to
multiple files, can I do that?
Scenario: We have an invoicing system. The report generates invoices
for all of our customers at one time (we have the option to do
individual, but as the number of customers grows, this could get
tedious). After verifying that we've applied all credits or factored
in extra charges, we want to generate the reports. Currently, we
export to a PDF and manually split out each invoice into its own
file. I figure there's got to be an easier way to do this on demand,
but don't quite know where to start.
We're running SQL RS 2005 - SP2+hotfixes at this point. Any
suggestions on how to move forward would be appreciated.
Thanks.
-Pete SchottOn May 10, 12:53 pm, paschott <pasch...@.gmail.com> wrote:
> Is there an easy way to export a single report to multiple PDF files
> on demand? I know that I can use a Data-Driven Subscription to
> generate multiple files, but if I want to give this ability to a user
> to look at the report, decide that it's good, and then save as PDF to
> multiple files, can I do that?
> Scenario: We have an invoicing system. The report generates invoices
> for all of our customers at one time (we have the option to do
> individual, but as the number of customers grows, this could get
> tedious). After verifying that we've applied all credits or factored
> in extra charges, we want to generate the reports. Currently, we
> export to a PDF and manually split out each invoice into its own
> file. I figure there's got to be an easier way to do this on demand,
> but don't quite know where to start.
> We're running SQL RS 2005 - SP2+hotfixes at this point. Any
> suggestions on how to move forward would be appreciated.
> Thanks.
> -Pete Schott
As far as I know, this functionality does not exist (outside a custom
application). That said, you could group by customer and select 'Page
break at end' as part of the grouping properties of table/matrix
report controls. Also, you could include a subreport based on the
customer; however, these options only create separate pages. Sorry
that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Export to Fixed Length Format text file without header row.

I am trying to generate a fixed-length format text file from a parameterised
SSRS report for my client and the only way that comnes close seems to be
to save as CSV with a single column query.
I have created a large, parameterised, UNIONed SQL statement to generate
the lines with the correct padding and alignment. This also concatenates
the columns so that the query effectively only returns a single column which
I can then Save As... CSV format. The problem with this is that I always
get a header row. (I concatenated in the query columns to avoid the commas
and quotes that come with CSV exports.)
I have experimented with the Noheader switch on the URL but the effectiveness
of this seems to disappear once the report a) stops for parameter input and
then b) renders to HTML before allowing me to save to CSV. I believe the
NoHeader option will only be effective if I determine my parameters ahead
of time and incorporate them into the URL also and render to CSV on that
URL as well.
All this combines to make for a complicated URL, certainly not something
my client would expect to create manually. Now I could write a small application
to generate offer them available parameters and then to this URL but I am
unsure if this effort is the way to go.
Options appear to be:
* bcp
* DTS
* application to allow users to input params and then generates the URL
* application to simply write the file and remove SSRS from the equation
* custom rendering extension
* purchase some kind of solution
It seems a lot of effort when I could just get them to manually deleted the
header row, however this approach is too unprofessional for my liking.
Ideally I would like to not have my client reliant on my code for new formats
or for when this format changes, hence a report writer based solution would
be great.
What is the best way to export to a fixed-length format text file from a
parameterised SSRS report?
cheers,
Paul.Unfortunately, as far as I am aware, there is no built in feature that
allows you to supress headers when exporting as csv.
As far as reporting services is concerned, the options are like you
said:
a) to build a custom renderer
b) to build a custom interface instead of using the report manager web
application
Both of these will require considerable effort to ultimately remove a
header row from a csv file
c) you could include a text box on the report with a hyperlink to
navigave to the required url (go to properties -> advanced ->
navigate). The user would then have to click on this text box to
download the report as csv with the appropriate noheaders tag. The
navigation url could be built dynamically using the report and
parameter information available from the formula fields, along with
the export format and noheaders tag appended on the end.
I have never tested this solution but it looks feasable if you are
happy for your users to click on a hyperlink to download.
Regards,
Rowen|||One suggestion is that
1. just make one more copy of the same report name it differently
2. In the copy of the report remove the header and keep the detail alone.
3. In the original report give a hyperlink using "Action" and name it
"Export to CSV" (or whater you like)
4. in the textbox ->Action, refer the copy of the report which is without
header using URL.
So what happens is users can also see the report with header and the link
will create the csv without header.
Amarnath
"Paul Ritchie" wrote:
> I am trying to generate a fixed-length format text file from a parameterised
> SSRS report for my client and the only way that comnes close seems to be
> to save as CSV with a single column query.
> I have created a large, parameterised, UNIONed SQL statement to generate
> the lines with the correct padding and alignment. This also concatenates
> the columns so that the query effectively only returns a single column which
> I can then Save As... CSV format. The problem with this is that I always
> get a header row. (I concatenated in the query columns to avoid the commas
> and quotes that come with CSV exports.)
> I have experimented with the Noheader switch on the URL but the effectiveness
> of this seems to disappear once the report a) stops for parameter input and
> then b) renders to HTML before allowing me to save to CSV. I believe the
> NoHeader option will only be effective if I determine my parameters ahead
> of time and incorporate them into the URL also and render to CSV on that
> URL as well.
> All this combines to make for a complicated URL, certainly not something
> my client would expect to create manually. Now I could write a small application
> to generate offer them available parameters and then to this URL but I am
> unsure if this effort is the way to go.
> Options appear to be:
> * bcp
> * DTS
> * application to allow users to input params and then generates the URL
> * application to simply write the file and remove SSRS from the equation
> * custom rendering extension
> * purchase some kind of solution
> It seems a lot of effort when I could just get them to manually deleted the
> header row, however this approach is too unprofessional for my liking.
> Ideally I would like to not have my client reliant on my code for new formats
> or for when this format changes, hence a report writer based solution would
> be great.
> What is the best way to export to a fixed-length format text file from a
> parameterised SSRS report?
> cheers,
> Paul.
>
>
>
>

Export to Excel 2000 format with Reporting services 2000 SP1

Hi,
I am clueless on how to generate the report in Excel 2000 format. i have
installed reporting services 2000 with service pack 2 in this machine. the
resultant Excel file format is in version 2003. i cant view that in Excel
2000. your feedback is highly appreciated.
Following is my code,
string report = m_ReportNameEXCEL;
string path = m_TargetReportLocation + report;
Byte[] bytes;
string format = "EXCEL";
string historyid = null;
string deviceInfo = null;
ReportServer.ParameterValue[] parameters;
DataSourceCredentials[] credentials = null;
string showhide = null;
string encoding = null;
string mimetype = null;
ParameterValue[] parameterused = null;
Warning[] warnings = null;
string[] streamIDs = null;
-
-
-
bytes = reportService.Render(
path,
format,
historyid,
deviceInfo,
parameters,
credentials,
showhide,
out encoding,
out mimetype,
out parameterused,
out warnings,
out streamIDs);
FileStream fs = File.Create(m_OutputPath + iYear.ToString()+ @."\" +
fileName);
fs.Write(bytes,0,bytes.Length);
fs.Close();
}
Thanks,
PradeepI have just looked it up in a Book. Only Excel 2002 or 2003 is supported. The
Data is rendered into MTHML with Mime Type ms-excel internally and brought to
Excel after that.
BUT: I am also using EXCEL 2000 on my machine and the exported Reports can
be opened without problems... (I have to say that I had Office 2003 on it
before, uninstalled it and installed OIffice 2000, God knows if there are any
DLLs left...?)
Maybe you are using anything inside your Report that needs a newer version?
Did you try a very simple Report?
There is the possibility to create your ow new Export formats. To do this
you have to program a File Rendering Extension. This is quite complex to
realize. You should get a good book to realize that.
Markus Poehler
netpoint-edv gmbh
"msnews.microsoft.com" wrote:
> Hi,
> I am clueless on how to generate the report in Excel 2000 format. i have
> installed reporting services 2000 with service pack 2 in this machine. the
> resultant Excel file format is in version 2003. i cant view that in Excel
> 2000. your feedback is highly appreciated.
> Following is my code,
> string report = m_ReportNameEXCEL;
> string path = m_TargetReportLocation + report;
> Byte[] bytes;
> string format = "EXCEL";
> string historyid = null;
> string deviceInfo = null;
> ReportServer.ParameterValue[] parameters;
> DataSourceCredentials[] credentials = null;
> string showhide = null;
> string encoding = null;
> string mimetype = null;
> ParameterValue[] parameterused = null;
> Warning[] warnings = null;
> string[] streamIDs = null;
> -
> -
> -
>
> bytes = reportService.Render(
> path,
> format,
> historyid,
> deviceInfo,
> parameters,
> credentials,
> showhide,
> out encoding,
> out mimetype,
> out parameterused,
> out warnings,
> out streamIDs);
>
> FileStream fs = File.Create(m_OutputPath + iYear.ToString()+ @."\" +
> fileName);
> fs.Write(bytes,0,bytes.Length);
> fs.Close();
> }
>
> Thanks,
> Pradeep
>
>|||SP1 and greater definitely creates native Excel format (that can be opened
in Excel 2000). To make sure the problem isn't your code, open up a report
in report manager and export it to Excel and see if you can open in Excel
2000. Note that when you upgrade to SP1 or SP2 it is both a report designer
upgrade and a RS server upgrade.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:BB9415C5-7400-4C88-9001-5964112E55EC@.microsoft.com...
>I have just looked it up in a Book. Only Excel 2002 or 2003 is supported.
>The
> Data is rendered into MTHML with Mime Type ms-excel internally and brought
> to
> Excel after that.
> BUT: I am also using EXCEL 2000 on my machine and the exported Reports can
> be opened without problems... (I have to say that I had Office 2003 on it
> before, uninstalled it and installed OIffice 2000, God knows if there are
> any
> DLLs left...?)
> Maybe you are using anything inside your Report that needs a newer
> version?
> Did you try a very simple Report?
> There is the possibility to create your ow new Export formats. To do this
> you have to program a File Rendering Extension. This is quite complex to
> realize. You should get a good book to realize that.
> Markus Poehler
> netpoint-edv gmbh
> "msnews.microsoft.com" wrote:
>> Hi,
>> I am clueless on how to generate the report in Excel 2000 format. i have
>> installed reporting services 2000 with service pack 2 in this machine.
>> the
>> resultant Excel file format is in version 2003. i cant view that in Excel
>> 2000. your feedback is highly appreciated.
>> Following is my code,
>> string report = m_ReportNameEXCEL;
>> string path = m_TargetReportLocation + report;
>> Byte[] bytes;
>> string format = "EXCEL";
>> string historyid = null;
>> string deviceInfo = null;
>> ReportServer.ParameterValue[] parameters;
>> DataSourceCredentials[] credentials = null;
>> string showhide = null;
>> string encoding = null;
>> string mimetype = null;
>> ParameterValue[] parameterused = null;
>> Warning[] warnings = null;
>> string[] streamIDs = null;
>> -
>> -
>> -
>>
>> bytes = reportService.Render(
>> path,
>> format,
>> historyid,
>> deviceInfo,
>> parameters,
>> credentials,
>> showhide,
>> out encoding,
>> out mimetype,
>> out parameterused,
>> out warnings,
>> out streamIDs);
>>
>> FileStream fs = File.Create(m_OutputPath + iYear.ToString()+ @."\" +
>> fileName);
>> fs.Write(bytes,0,bytes.Length);
>> fs.Close();
>> }
>>
>> Thanks,
>> Pradeep
>>

Export To Excel

Hi,

I'm trying to use the export to Excel (Excel Destination) however, I wish to supply a dynamic piece of SQL to generate the data.

When I set the properties I just get an option to enter "Table or View", "Table or View as Variable" or "SQL Command".

The issue is that the table does not exist until the SSIS job is run and the columns will not always be the same anyway.

How can I use a simple dynamic piece of SQL (or stored proc) to get a recordset that is simply dumped out to excel - rows... columns... nothing more ?.

Help very much appreciated - Paul.

I am afraid you cannot get a generic 'export to excel' utility using SSIS. The dataflow metadata can not be generated/changed dynamically at run time.

The closest thing you can get is to have a package per every source/destination; creating the excel sheet at run time is not a problem. The problem is creating the source destination mappings.

|||

Yeah... I was worried someone would say as much !

What on earth is wrong with sending a dataset of *unknown* columns in a tabular form to an Excel spreadsheet ?...

Why must everything be so complicated and convoluted ?... Its a SIMPLE export job....

Seriously - I give up...

Thanks for the reply

Export Stored Procedure SQL Script

I just use the 'Generate SQL Script' from EM to export stored procedure.
How do I export the Stored Procedure SQL Script in to a file so in an order
so that they can be created successfully ?
I got an error when execute the scripts:
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp1'. The stored procedure will still be
created.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp2'. The stored procedure will still be
created.
...............
...............
How do I get around that ?We usually keep an "installation " table , when we save out stored
procedures they are ordered by the stated execution order. (customised
script)
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
> I just use the 'Generate SQL Script' from EM to export stored procedure.
> How do I export the Stored Procedure SQL Script in to a file so in an
order
> so that they can be created successfully ?
> I got an error when execute the scripts:
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp1'. The stored procedure will still be
> created.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'sp2'. The stored procedure will still be
> created.
> ...............
> ...............
> How do I get around that ?
>|||Thanks.
I reordered some of the sp based on the depenancies.
However, I still got error in one of the sp in which a recursion occurrs:
CREATE PROCEDURE dbo.recur_sp
@.parent_id int
as
........
if exists(select 1 from category where parent_id=@.id)
exec recur_sp @.id
select @.row = @.row + 1
end
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'recur_sp'. The stored procedure will still be
created.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:ELCdnem5EM1sDgLZRVnyvQ@.bt.com...
> We usually keep an "installation " table , when we save out stored
> procedures they are ordered by the stated execution order. (customised
> script)
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
>> I just use the 'Generate SQL Script' from EM to export stored procedure.
>> How do I export the Stored Procedure SQL Script in to a file so in an
> order
>> so that they can be created successfully ?
>> I got an error when execute the scripts:
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp1'. The stored procedure will still be
>> created.
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp2'. The stored procedure will still be
>> created.
>> ...............
>> ...............
>> How do I get around that ?
>>
>|||That "error" is normal for recursive procs. It's just a warning and can be
safely ignored.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eSD7sVYmGHA.4816@.TK2MSFTNGP03.phx.gbl...
Thanks.
I reordered some of the sp based on the depenancies.
However, I still got error in one of the sp in which a recursion occurrs:
CREATE PROCEDURE dbo.recur_sp
@.parent_id int
as
........
if exists(select 1 from category where parent_id=@.id)
exec recur_sp @.id
select @.row = @.row + 1
end
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'recur_sp'. The stored procedure will still be
created.
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:ELCdnem5EM1sDgLZRVnyvQ@.bt.com...
> We usually keep an "installation " table , when we save out stored
> procedures they are ordered by the stated execution order. (customised
> script)
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:eXOzROPmGHA.4164@.TK2MSFTNGP03.phx.gbl...
>> I just use the 'Generate SQL Script' from EM to export stored procedure.
>> How do I export the Stored Procedure SQL Script in to a file so in an
> order
>> so that they can be created successfully ?
>> I got an error when execute the scripts:
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp1'. The stored procedure will still be
>> created.
>> Cannot add rows to sysdepends for the current stored procedure because it
>> depends on the missing object 'sp2'. The stored procedure will still be
>> created.
>> ...............
>> ...............
>> How do I get around that ?
>>
>

Export SQL Role

Hi,
Is there a way to export a database role.
I would like to generate a script that I can apply to
another server.. This script would essentially create this
role for me without me having to manually create it, with
all its permissions.
Thanks.
Yes. Create a DTS Package Using Copy SQL Sever Objects Task. Go to the
Options button on the Copy tab and you'll see it. I've never used it, but
would assume it would copy permissions or else it would be no good IMO.
"niv" wrote:

> Hi,
> Is there a way to export a database role.
> I would like to generate a script that I can apply to
> another server.. This script would essentially create this
> role for me without me having to manually create it, with
> all its permissions.
> Thanks.
>

Export SQL Role

Hi,
Is there a way to export a database role.
I would like to generate a script that I can apply to
another server.. This script would essentially create this
role for me without me having to manually create it, with
all its permissions.
Thanks.Yes. Create a DTS Package Using Copy SQL Sever Objects Task. Go to the
Options button on the Copy tab and you'll see it. I've never used it, but
would assume it would copy permissions or else it would be no good IMO.
"niv" wrote:
> Hi,
> Is there a way to export a database role.
> I would like to generate a script that I can apply to
> another server.. This script would essentially create this
> role for me without me having to manually create it, with
> all its permissions.
> Thanks.
>

Export SQL Role

Hi,
Is there a way to export a database role.
I would like to generate a script that I can apply to
another server.. This script would essentially create this
role for me without me having to manually create it, with
all its permissions.
Thanks.Yes. Create a DTS Package Using Copy SQL Sever Objects Task. Go to the
Options button on the Copy tab and you'll see it. I've never used it, but
would assume it would copy permissions or else it would be no good IMO.
"niv" wrote:

> Hi,
> Is there a way to export a database role.
> I would like to generate a script that I can apply to
> another server.. This script would essentially create this
> role for me without me having to manually create it, with
> all its permissions.
> Thanks.
>