For security reasons DTD is prohibited in this XML document and System.OutOfMemory

I am getting this error while running a very large dataset. Please help..

The full description of the error is : "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."

I am having hard time figuring out a solution for this. I am using sql 05, SSRS 05, Report viewer

I have a very big Dataset which pulls up millions of rows.

When I pass parameters from Windows forms to run on Reportviewer through webservice it is giving me "System.OutOfMemory" Exception and when I pass same parameters on the server(i.e. http:\\server\reportmanager) it is giving me this error "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method".

I have no problem when I pass small dataset.

Please help me solve this annoying this problem.

|||

I found this article and i guess this should answer your question.

http://support.microsoft.com/default.aspx/kb/909678

|||

Thanks Chaitanya for the informative link.

I've already checked that and done necessary changes for the long running reports.

Right now I was able to print 55541 pages. We decided to take printout in batches if the requirement more than 55541. They best possible solution would be schedule a report and call it from Windows service using webservices.

Please let me know if we can print unlimited pages(i.e about 3 million pages).

For security reasons DTD is prohibited in this XML document

I am getting this error while running a very large dataset. Please help..

The full description of the error is : "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."

I am having hard time figuring out a solution for this. I am using sql 05, SSRS 05, Report viewer

I have a very big Dataset which pulls up millions of rows.

When I pass parameters from Windows forms to run on Reportviewer through webservice it is giving me "System.OutOfMemory" Exception and when I pass same parameters on the server(i.e. http:\\server\reportmanager) it is giving me this error "For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method".

I have no problem when I pass small dataset.

Please help me solve this annoying this problem.

|||

I found this article and i guess this should answer your question.

http://support.microsoft.com/default.aspx/kb/909678

|||

Thanks Chaitanya for the informative link.

I've already checked that and done necessary changes for the long running reports.

Right now I was able to print 55541 pages. We decided to take printout in batches if the requirement more than 55541. They best possible solution would be schedule a report and call it from Windows service using webservices.

Please let me know if we can print unlimited pages(i.e about 3 million pages).

For getting Idea

SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
PartsLocations.Qty, PartsLocations.LastInventoried
FROM Parts
INNER JOINT Manufacturer ON Parts.ManufacturerID=
Manufacturer.ManufacturerID
INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID
WHERE SKU?
Would performance will be increased after creating a nonclustered index on
the primary key of each above tables ?
Thanks
NOOR
Noor,
If you really have a PK constraint then you already have indexes on those
columns as they are created behind the scenes when you create the
constraint. If not you should definitely create them as all PK's should
have the proper index. It would also help to ensure there is a proper index
on the SKU columns as well.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:Ou7xkJoXEHA.3796@.TK2MSFTNGP11.phx.gbl...
> SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
> PartsLocations.Qty, PartsLocations.LastInventoried
>
> FROM Parts
>
> INNER JOINT Manufacturer ON Parts.ManufacturerID=
> Manufacturer.ManufacturerID
> INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID
> WHERE SKU?
>
>
> Would performance will be increased after creating a nonclustered index on
> the primary key of each above tables ?
>
> Thanks
> NOOR
>
|||Yes I am talking about non clustered index not the clustered index.
Thanks
Noor
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uEU7gHqXEHA.3112@.tk2msftngp13.phx.gbl...
> Noor,
> If you really have a PK constraint then you already have indexes on those
> columns as they are created behind the scenes when you create the
> constraint. If not you should definitely create them as all PK's should
> have the proper index. It would also help to ensure there is a proper
index[vbcol=seagreen]
> on the SKU columns as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:Ou7xkJoXEHA.3796@.TK2MSFTNGP11.phx.gbl...
on
>
|||A PK does not have to be a clustered index. It gets created that way by
default but if you have another index that would be better as a clustered
index you can create the PK as non-clustered. But it makes no sense to have
2 indexes on the same column, one clustered and one not.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:%235Ca%23PqXEHA.1764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Yes I am talking about non clustered index not the clustered index.
> Thanks
> Noor
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uEU7gHqXEHA.3112@.tk2msftngp13.phx.gbl...
those[vbcol=seagreen]
> index
index
> on
>
|||Andrew,
While it's not often the case, it can make sense to have both
clustered and non-clustered indexes on the primary key. In the repro
below, you can see how fewer data pages might be read by a query on the
key columns when there is a non-clustered index available - presumably
in this example there are separate reasons for the primary key to be
clustered.
use Northwind
go
drop index [Order Details].OrdersOrder_Details
drop index [Order Details].OrderID
go
set statistics io on
go
select OrderID, min(ProductID) as minProd
from [Order Details]
group by OrderID
go
set statistics io off
go
create unique nonclustered index OD_OP on [Order Details](OrderID,
ProductID)
go
set statistics io on
go
select OrderID, min(ProductID) as minProd
from [Order Details]
group by OrderID
go
set statistics io off
go
drop index [Order Details].OD_OP
go
create index OrdersOrder_Details on [Order Details](OrderID)
create index OrderID on [Order Details](OrderID)
go
Steve Kass
Drew University
Andrew J. Kelly wrote:

>A PK does not have to be a clustered index. It gets created that way by
>default but if you have another index that would be better as a clustered
>index you can create the PK as non-clustered. But it makes no sense to have
>2 indexes on the same column, one clustered and one not.
>
>
|||Steve,
Yes covered indexes usually have less IO. But a single column PK will
usually not be advantageous to have both a clustered and non.
Andrew J. Kelly SQL MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:OiNjm15XEHA.996@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Andrew,
> While it's not often the case, it can make sense to have both
> clustered and non-clustered indexes on the primary key. In the repro
> below, you can see how fewer data pages might be read by a query on the
> key columns when there is a non-clustered index available - presumably
> in this example there are separate reasons for the primary key to be
> clustered.
> use Northwind
> go
> drop index [Order Details].OrdersOrder_Details
> drop index [Order Details].OrderID
> go
> set statistics io on
> go
> select OrderID, min(ProductID) as minProd
> from [Order Details]
> group by OrderID
> go
> set statistics io off
> go
>
> create unique nonclustered index OD_OP on [Order Details](OrderID,
> ProductID)
> go
> set statistics io on
> go
> select OrderID, min(ProductID) as minProd
> from [Order Details]
> group by OrderID
> go
> set statistics io off
> go
> drop index [Order Details].OD_OP
> go
> create index OrdersOrder_Details on [Order Details](OrderID)
> create index OrderID on [Order Details](OrderID)
> go
> Steve Kass
> Drew University
> Andrew J. Kelly wrote:
have
>

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
..
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT

FOR EXPLICIT

i don't think the documentation could be more confusing.
Consider:
CREATE TABLE MyNewsEntries(
guid uniqueidentifier,
title varchar(200),
description text,
pubDate datetime,
imageFilename varchar(260),
imageMimeType varchar(100) )
Desired output:
<rss>
<item>
<title>MyNewsEntries.title</title>
<description>MyNewsEntries.description</description>
<pubDate>MyNewsEntries.pubDate</pubDate>
<guid>MyNewsEntries.guid</guid>
<enclosure url="[MyNewsEntires.imageFilename]"
type="[MyNewsEntries.imageMimeType]" />
</item>
<item>
...
</item>
</rss>
Ordered by MyNewsEntries.pubDate DESC
Now, after much cursing and swearing, i managed to vomit up:
SELECT
1 AS Tag, NULL AS Parent,
NULL AS [rss!1!],
NULL AS [item!2!title!element],
NULL AS [item!2!description!element],
NULL AS [item!2!pubdate!element],
NULL AS [item!2!guid!element],
NULL AS [enclosure!3!url],
NULL AS [enclosure!3!type]
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
NULL as [rss!1!element],
title AS [item!2!title!element],
description AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!2!url],
NULL AS [enclosure!2!type]
FROM MyNewsEntries
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
NULL AS [rss!1!element],
title AS [item!2!title!element],
NULL AS [item!2!description!element],
pubDate AS [item!2!pubdate!element],
guid AS [item!2!guid!element],
imageFilename AS [enclosure!3!url],
imageMimeType AS [enclosure!3!type]
FROM MyNewsEntries
FOR XML EXPLICIT
Which runs, but the order is wrong. All the enclosures are appearing at the
end.
If i try
ORDER BY [item!2!pubdate!element] DESC
FOR XML EXPLICIT
The it puts the rss entry at the end, and complains:
| Parent tag ID 1 is not among the open tags.
| FOR XML EXPLICIT requires parent tags to be opened first.
| Check the ordering of the result set.
So i try
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
[item!2!pubdate!element] DESC
But now it is mixing the order of Tag=2 and Tag=3 elements, putting all
<enclosures> in one <item>
So i try:
SELECT * FROM (
My entire query above
) DerivedTable
ORDER BY
CASE
WHEN (Parent IS NULL) THEN 0
WHEN (Parent = 0) THEN 0
ELSE 99999
END,
Tag,
[item!2!pubdate!element] DESC
Which seems to work.
My question is: Is this what i have to do to get SQL Server to return XML in
an explicit format?
Go back to your original query, change
NULL AS [rss!1!element],
to
1 AS [rss!1!element],
everywhere *except* under tag 1 (the first)
which should be left as null.
Now change your order by to
ORDER BY [rss!1!],[item!2!pubdate!element] DESC,Tag
FOR XML EXPLICIT