Export valid XML files

Ladies and Gents,
I cannot seem to find an (easily) workable solution anywhere on the web.
All I need to do, is return the contents of some SQL server tables as XML
data sets.
I was able to form "proper" XML files by adding a top-level root element
(still unclear on why that is not built in). Now the issue that remains is
that SQL server will return the results with a ROWGUID and a full line
of undescores (_________________) before the actual XML.
I am trying to keep the process as simple as can get, avoiding SQLXML
and IIS -- I just want to find a solution where I can create a simple (!)
DTS package that will deliver a properly formatted XML file with a top
level element, one that IE will be able to display properly without
any further editing.
1) is this possible (anything is possible -- can this be done via some form
of "FOR XML" statement)
2) if there is no easy solution to 1, what methods would you recommend to
make the process
most maintanable? My requirements are simple -- I need to produce a
"feed" -- a set of properly formatted
XML files, based on many queries -- probably about 15-20 files
alltogether, so I need this to be
as simple as possible.
TIA,
EugeneCould you be more specific? Are you trying to export the database schema or
the data in your tables?
As simple as possible? A FOR XML query in a SQLXML template.
ML|||If you follow the "Microsoft" way, you set up a "web service" using the HTML
capabilities of SQL Server. The template supplies the formatting you need an
d
returns data in the proper format.
I do not have a link, but you can push a root tag into your FOR XML return.
The examples I have seen use the template, as well.
If the return is not valid, you can also throw the data through an XSLT
transform and get it to look however you desire. This is an extra step,
however.
NOTE: I am fairly fond of the whole SOA/web service concept, but it is not
for everyone. For SQL Server 2000, the link into IIS is not the best
implementation (HTTP endpoints in SQL 2005 are much better), but they provid
e
a means of moving towards SOA. SOA is not a silver bullet, but it makes a lo
t
of sense if you can make the paradigm change to working with messages instea
d
of CRUD.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Eugene" wrote:

> Ladies and Gents,
> I cannot seem to find an (easily) workable solution anywhere on the web.
> All I need to do, is return the contents of some SQL server tables as XML
> data sets.
> I was able to form "proper" XML files by adding a top-level root element
> (still unclear on why that is not built in). Now the issue that remains i
s
> that SQL server will return the results with a ROWGUID and a full line
> of undescores (_________________) before the actual XML.
> I am trying to keep the process as simple as can get, avoiding SQLXML
> and IIS -- I just want to find a solution where I can create a simple (!)
> DTS package that will deliver a properly formatted XML file with a top
> level element, one that IE will be able to display properly without
> any further editing.
> 1) is this possible (anything is possible -- can this be done via some for
m
> of "FOR XML" statement)
> 2) if there is no easy solution to 1, what methods would you recommend to
> make the process
> most maintanable? My requirements are simple -- I need to produce a
> "feed" -- a set of properly formatted
> XML files, based on many queries -- probably about 15-20 files
> alltogether, so I need this to be
> as simple as possible.
> TIA,
> Eugene
>
>|||I am trying to export the data.
Something along the lines of :
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||I am trying to export the data. Here's my query
SELECT
1 AS TAG
,NULL AS PARENT
,NULL AS [INSTITUTIONS!1]
,NULL AS [INSTITUTION!2!iconum]
,NULL AS [INSTITUTION!2!cusip]
,NULL AS [INSTITUTION!2!dlr]
,NULL AS [INSTITUTION!2!position]
,NULL AS [INSTITUTION!2!pct_held]
,NULL AS [INSTITUTION!2!position_change]
UNION
SELECT 2 AS TAG
,1 AS PARENT
,NULL AS [INSTITUTIONS!1]
,iconum AS [INSTITUTION!2!iconum]
,cusip AS [INSTITUTION!2!cusip]
,dlr AS [INSTITUTION!2!dlr]
,position AS [INSTITUTION!2!position]
,pct_held AS [INSTITUTION!2!pct_held]
,position_change AS [INSTITUTION!2!position_change]
FROM ##EUGENE_TEMP
ORDER BY [INSTITUTION!2!iconum]
FOR XML EXPLICIT
I get back something like :
XML_F52E2B61...
_____________________________
<INSTITUTIONS>
<INSTITUTION iconum="10134" cusip="151313103" dlr="2005-03-31T00:00:00"
position="22000" pct_held="0.002" position_change="22000"/>
</INSTITUTIONS>
I need to not have the top
XML_F52E2B61...
_____________________________.
SQLXML is not really an option now -- no ISS on the SQL box... Looks like
I'll just have to script a solution, unless
someone has another idea...
Thanks
"ML" <ML@.discussions.microsoft.com> wrote in message
news:13E842CF-133A-42D8-BFCC-81FD7A89D685@.microsoft.com...
> Could you be more specific? Are you trying to export the database schema
or
> the data in your tables?
> As simple as possible? A FOR XML query in a SQLXML template.
>
> ML|||If you're using BCP to write the results to a text file or using QA's
"Results to file" option, you'll have to remove the unwanted pieces of text
either manually or programatically (not in T-SQL).
Whether you like it or not, you will need a client application to read the
result.
Oh, and another thing - XML results might get split into several rows of
data if not read as an XML by an appropriate client.
If installing IIS creates a possible breach of security, you can still use
the IIS lock-down tool to keep your network secure. The power of SQLXML is
immense, so why not use it?
ML

Export to PDF

i'm experiencing the same problem with missing headers when exporting to pdf
and i'm using version 2000.
do you have a solution to this problem?Is it missing headers on just the first page? or all pages?

Export To Excel throw WrapperReportRenderingException

I knows I have post this before, but no solution found yet.
Could anyone help please..... ?
A report runs without any problem (Url access) and export to other format
also without any problem.
Only when I export the same report to Excel, the following error occurs in
the reportserver log and its throws an error on screen.
I have found out it is down to a table grouping on a data field.
I have created a cut down version of RDL file together with the RDL.data
file if anyone interested to take a look.
The report conatins a list and the list has a details group, which grouping
the data by week.
A table is sit inside the list to report the data.
Within the table, there is a group list which is grouped by a field from the
dataset.
Also a header and footer together with the table of cause is repeated on
every page.
The header in the cut down version of RDL file contains only a static
textbox.
The footer contains a static textbox and a textbox that has value ="Page " &
Globals!PageNumber & " of " & Globals!TotalPages.
Another thing that I have found is if I export a report with this page
number textbox included in the footer, this WrapperReportRenderingException
is throws. But, if I remove this page number text box from the footer, the
export to excel works...
Here is the error from the report server log:
------
aspnet_wp!library!1!01/11/2008-14:54:49:: i INFO: Call to RenderNext(
'/ExportExcelProblemReport' )
aspnet_wp!reportrendering!1!01/11/2008-14:54:50:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report., ;
Info:
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
error occurred during rendering of the report. -->
System.InvalidOperationException: Operation is not valid due to the current
state of the object.
at
Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32
index)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows
header, TableHeaderFooterRows footer, TableGroupCollection tableGroups,
TableRowsCollection detailRows, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList
indexPath, PageReportItems& pageRI)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem
parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout,
PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable&
rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates,
OutlineRenderStates[]& horizontalOutlineStates)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem
parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32
currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout
pageLayout, Int32& currentPageNumber, Stack& stack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack&
renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout&
lastPageLayout, Action action)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
-- End of inner exception stack trace --
aspnet_wp!webserver!1!01/11/2008-14:54:50:: e ERROR: Reporting Services
error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An
error occurred during rendering of the report. -->
Microsoft.ReportingServices.ReportProcessing.WrapperReportRenderingException:
An error occurred during rendering of the report. -->
Microsoft.ReportingServices.ReportRendering.ReportRenderingException: An
error occurred during rendering of the report. -->
System.InvalidOperationException: Operation is not valid due to the current
state of the object.
at
Microsoft.ReportingServices.ReportRendering.TableGroupCollection.get_Item(Int32
index)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReferenceInTable(TableHeaderFooterRows
header, TableHeaderFooterRows footer, TableGroupCollection tableGroups,
TableRowsCollection detailRows, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GetReference(ReportItem
dataRegion, IntList indexPath, Int32 startIndex)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.CollectHiddenReportItems(IntList
indexPath, PageReportItems& pageRI)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.PreScanPage(ReportItem
parentDataRegion, Boolean isPageNeedsEvaluation, PageLayout& pageLayout,
PageReportItems& pageRIItems, Stack& dynamicLayoutStack, Hashtable&
rowHeightStateList, OutlineRenderStates[]& verticalOutlineStates,
OutlineRenderStates[]& horizontalOutlineStates)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderStaticPage(ReportItem
parentDataRegion, PageLayout pageLayout, Hashtable formulaRIMap, Int32
currentPageNumber, PageReportItems& pageRIItems, Stack& dynamicLayoutStack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageLayout(PageLayout
pageLayout, Int32& currentPageNumber, Stack& stack)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderOrCountDynamicPage(Stack&
renderDynamicStack, Int32& currentPageNumber, Int32 stackTop, PageLayout&
lastPageLayout, Action action)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderPageCollection(PageCollection
pageCollection, Int32& currentPageNumber, Stack& stack, PageLayout&
lastPageLayout)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateWorkSheets()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.GenerateMainSheet()
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.RenderExcelWorkBook(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.ProcessReport(CreateAndRegisterStream
createAndRegisterStream)
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer.Render(Report
report, NameValueCollection reportServerParameters, NameValueCollection
deviceInfo, NameValueCollection clientCapabilities,
EvaluateHeaderFooterExpressions evaluateHeaderFooterExpressions,
CreateAndRegisterStream createAndRegisterStream)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(IRenderingExtension
renderer, CreateReportChunk createChunkCallback, RenderingContext rc,
GetResource getResourceCallback)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderSnapshot(CreateReportChunk
createChunkCallback, RenderingContext rc, GetResource getResourceCallback)
at Microsoft.ReportingServices.Library.RenderSnapshotAction.Render()
at
Microsoft.ReportingServices.Library.RSService.RenderFromSessionNoCache(CatalogItemContext
reportContext, ClientRequest session, RenderingResult& result)
at
Microsoft.ReportingServices.Library.RSService.RenderFromSession(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderNext(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effecectiveParameters, String[]&
secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderNextCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderNextCancelableStep.RenderNext(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType type,
Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation
operation)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
------
Thanks in advance !
Regards,
Chris.Hello,
Since the Excel export did not include the page footer, you may try to
remove the footer.
I tested on my side and did not reproduce this issue.
Did this issue appeared on all reports?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei,
Nice to hear from you again and thank you very much for looking into it.
I have just tried it on my cut down version of report, if I remove the
footer its works.
But when I tried it to my actural report, even if I have removed the
complete footer, the error still occured.
I have emailed you the actural report of our company, again a RDL file
together with the RDL.DATA file.
Could you please, please spent a little time of yours and take a look at it
please.
It is getting desperate.... :(
To answer your question, we have another 2 reports that use the similar
dataset also have the same problem when the table is group on the same
field.
Also, I am not sure whether it helps, I listed some of the dll files version
within our Report server bin directory.
On our live Web Server:
ReportingServicesService.exe
9.0.3205.0
Microsoft.ReportingServices.ExcelRendering.dll 9.0.3042.0
Microsoft.ReportingServices.NullRendering.dll
9.0.1399.0
On My Test Machine:
ReportingServicesService.exe
9.0.3054.0
Microsoft.ReportingServices.ExcelRendering.dll 9.0.3042.0
Microsoft.ReportingServices.NullRendering.dll
9.0.1399.0
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:zO0IwgoVIHA.4264@.TK2MSFTNGHUB02.phx.gbl...
> Hello,
> Since the Excel export did not include the page footer, you may try to
> remove the footer.
> I tested on my side and did not reproduce this issue.
> Did this issue appeared on all reports?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello,
I am performing some research on this. Since i did not reproduce this
issue, I may need some more time to try to get the resolution.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Export to Excel problem in RS (updated)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export to Excel problem in RS (updated - some progress !!)

Hello there,

Does anyone have a solution to the following export to excel problem i am experiencing in RS 2005 Standard Ed.

When exporting a matrix report in VS2005 (or in Report Manager) I get the following export error displayed in VS :

An error occurred during local report processing.

An error occurred during rendering of the report

Operation is not valid due to the current state of the object.

This is only a problem where exporting data into multiple worksheets - such as dept by dept on different wsheets.

Export to excel with 1 dept works fine.

I have managed to pinpoint the situation where this happens :

Matrix report with page break for the grouping.

I think there might be a fault with the export function.

Anyone like to confirm on this ?

|||

Did you ever find out if this was a bug?

I also have a problem with page breaks in matrix groupings where by I get blank pages when Print Previewing in Excel. However I am using RS 2000 with SP2.

Thanks

Scott

Export System Catalog info

Hello,

I'm looking for a solution that will the extraction of system catalog information into either Excel or visio. We are looking to do this for documentation purposes. Is there a way to do this on the fly or do I need to run a view and then copy and past the information.

use sql Scripting. If the instance is SQL 2005 , then SQLCMD and if it is 2000 then OSQL.

SQL 2005 - sqlcmd -q"Exec sp_who2" -o"sp_who2_as_on_date.text"

SQL Server 2000- OSQL -q"Exec sp_who2" -o"sp_who2_as_on_date.text"

Check this

Madhu

|||If not you can set the query editor on SSMS to post the results to file by stating to comma seperated values.