Root node from a FOR XML

Hello,
How can I get a XML with a root node from a FOR XML ?:
Now I have this:
The SQL:

SELECT * FROM Table_Projects Project
FOR XML AUTO

and the XML output:

<Project>
<ItemID>1</ItemID>
....
</Project>
<Project>
<ItemID>2</ItemID>
....
</Project>
...

and I want this:

<Projects>
<Project>
<ItemID>1</ItemID>
....
</Project>
<Project>
<ItemID>2</ItemID>
....
</Project>
...
</Projects>

thanks and regards,
Edu
What client are you using, just TSQL or ADO or ADO.net, etc|||Hello pkr,
I'm using ADO.NET on an ASP.NET page (with c#)
Now I am using the following code to solve the problem:

XmlDocument xmlDoc =new XmlDocument();

myConnection.Open();

SqlDataReader dr = myCommand.ExecuteReader();

string stringXml = "<Projects>";

while (dr.Read())

{

stringXml += dr.GetString(0);

}

stringXml += "</Projects>";

xmlDoc.LoadXml(stringXml);

myConnection.Close();

return(xmlDoc);

But I am searching for a solution on the SQL Server side ( without SQLXML 3.0 for the moment)
Regards,
Edu
|||Ah yes the holy grail of avoiding SQLXML, sorry I don't believe there is such a beast, at least not yet. One, and I think horrible, way it to use mutliple results sets...
<code>
Select '<root>'
Select * from table for xml raw
Select '</root>'
</code>
Horrible isn't? I think the way you're currently doing is the best of a bad bunch.|||I've done this with the use of FOR XML EXPLICIT and a UNION ALL. Here are 3 resources on this topic that I've personally found to behelpful:

SQL Server 2000, XML and XSL: The Ultimate Combination
Retrieving XML from SQL Server Using SELECT ... FOR XML
Demystify SQL Server FOR XML EXPLICIT

|||Yes good point, although IMHO xml explicit is a horrible syntax, luckily they've done a much better job in 2005.

Root node attributes

Hello,

I've been trying to craft some xml that has attributes on the root, but without any success.

For example :-

SELECT
Test1 "Test1",
Test2 "Test2"
FROM
TestTable
FOR
XML PATH('TestValue'),
ELEMENTS,
ROOT('TestValues test=1') <-- this is not allowed, but this is the kind of thing that i would like

To produce :-

<TestValues test=1>
<TestValue>
<Test1>asdf </Test1>
<Test2>asdf </Test2>
</TestValue>
<TestValue>
<Test1>asdf </Test1>
<Test2>asdf </Test2>
</TestValue>
</TestValues>

Any ideas?

Thanks
Cedric

Don't you hate it when you click on the send button and at the same time an idea pop's into your head?!

Just thought i'd answer my own question incase someone is interested...

SELECT 1 '@.Test',
(
SELECT
Test1 "Test1",
Test2 "Test2"
FROM
TestTable
FOR
XML PATH('TestValue'),
TYPE
)
FOR
XML PATH ('TestValues')

Clearly there was too much blood in my coffee stream...

Thanks

Cedric

Root Namespace Prefix and OpenXml

Hi,
I can't seem to nail down the correct way to use OpenXml with input Xml that
conatains a root namespace.
'=================================
PROC
=================================
<snip>
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.ExchangeRtMgmtXml, '<root
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
INSERT INTO #ExchangeRtMgmt (
ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
)
SELECT ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
WITH(
ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
Identifiernvarchar(100) '@.Identifier',
ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
BeginDtsmalldatetime '@.BeginDt',
Ratedecimal '@.Rate',
RateSourcenvarchar(1) '@.RateSource',
ParticipantIsManagedbit '@.ParticipantIsManaged'
)
EXEC sp_xml_removedocument @.iDoc
</snip>
'======================================
' Call SProc
'======================================
EXEC spr_ManagedExchangeRate_byParticipant '<?xml version="1.0"
encoding="utf-16"?><ExchangeRtParticipants
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
ReinscReferenceNicknameTxt="NACRE" Identifier="2"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
/><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
/></ExchangeRtParticipants>'
Can someone please point me in the right direction?
Hi Jeff,
If I get your intention right I think you only need to change few little
things in order to make your code work:
- N'<?xml version="1.0" encoding="utf-16"?>: the N indicates that the
XML doc is using UTF-16 encoding;
- if you want to apply the namespace uri
"http://tempuri.org/ExchangeRtParticipants.xsd “ to all elements underneath
'ExchangeRTParticipans' element than you don't need to specify a prefix for
this namespace. It will be considered the default namespace for each node
under the element that declares this namespace. So the namespace declaration
inside your xml instance will be:
<ExchangeRtParticipants
xmlns="http://tempuri.org/ExchangeRtParticipants.xsd">...
- However if you keep the prefix declaration in your top level element
<ExchangeRtParticipants
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd">...; that means
that only the elements that are declared using the prefix in their name are
bound to that specific namespace. In your case <AgreementParticipant> is NOT
declared using the prefix 'er' and because of that it is NOT part of
'"http://tempuri.org/ExchangeRtParticipants.xsd” namespace; it is part of the
'NoName' namespace. That is why when you query for
'//er:AgreementParticipant' using the 'er' prefix in your XPath query inside
the OpenXML clause nothing is returned; AgreementParticipant element DOES NOT
belongs to "http://tempuri.org/ExchangeRtParticipants.xsd" namespace, it
belongs to 'nonamespace' uri. So if you don’t want <AgreementParticipant> to
belong to namespace referent by ‘er’ than you need to change you XPath expr
to '//AgreementParticipant' in order to get values out of that element.
I’ve changed your sample code below to implement the solution where you
don’t specify a prefix to "http://tempuri.org/ExchangeRtParticipants.xsd”
namespace in your xml instance declaration so it will be the default
namespace for every node under your top level node. Please let me know if
that was your primary intention.
-- modified sample
DROP PROCEDURE foo
GO
create procedure foo (@.doc XML) -- use ntext if you are running SQLServer
2000
AS
BEGIN
DECLARE @.iDoc INT
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.doc, '<root
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
SELECT ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
WITH(
ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
Identifiernvarchar(100) '@.Identifier',
ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
BeginDtsmalldatetime '@.BeginDt',
Ratedecimal '@.Rate',
RateSourcenvarchar(1) '@.RateSource',
ParticipantIsManagedbit '@.ParticipantIsManaged'
)
EXEC sp_xml_removedocument @.iDoc
END
GO
EXEC foo 'N<?xml version="1.0" ?><ExchangeRtParticipants
xmlns="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
ReinscReferenceNicknameTxt="NACRE" Identifier="2"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
/><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
/></ExchangeRtParticipants>'
Thanks,
Ana Elisa - SDET - SQLServer Group
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff Spicolli" wrote:

> Hi,
> I can't seem to nail down the correct way to use OpenXml with input Xml that
> conatains a root namespace.
> '=================================
> PROC
> =================================
> <snip>
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.ExchangeRtMgmtXml, '<root
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
> INSERT INTO #ExchangeRtMgmt (
> ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> )
> SELECT ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
> WITH(
> ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
> Identifiernvarchar(100) '@.Identifier',
> ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
> ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
> SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
> SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
> TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
> TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
> BeginDtsmalldatetime '@.BeginDt',
> Ratedecimal '@.Rate',
> RateSourcenvarchar(1) '@.RateSource',
> ParticipantIsManagedbit '@.ParticipantIsManaged'
> )
> EXEC sp_xml_removedocument @.iDoc
> </snip>
>
> '======================================
> ' Call SProc
> '======================================
> EXEC spr_ManagedExchangeRate_byParticipant '<?xml version="1.0"
> encoding="utf-16"?><ExchangeRtParticipants
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
> ReinscReferenceNicknameTxt="NACRE" Identifier="2"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
> /><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
> /></ExchangeRtParticipants>'
>
> Can someone please point me in the right direction?
>
|||Thanks Ana,
Part of the dilemma is a direct effect of the design pattern.
1) XML stream is passed into an Engine
2) Engine Consists of 4 Commands
3) Each command takes in a Stream and returns a subset stream (Xpath
Filtered via XSL)
4) All Streams are then merged back into a single stream.
I noticed stright away that if either XML stream conatained identical
Namespace's (Prefixed or not), then the issue was really not present as there
is a standard way to perform xpath queries on the XML.
The problem identified itself after the first 'pass' through.
Your reply was very helpful, thanks.
"Ana Elisa Schmidt [MSFT]" wrote:
[vbcol=seagreen]
> Hi Jeff,
> If I get your intention right I think you only need to change few little
> things in order to make your code work:
> - N'<?xml version="1.0" encoding="utf-16"?>: the N indicates that the
> XML doc is using UTF-16 encoding;
> - if you want to apply the namespace uri
> "http://tempuri.org/ExchangeRtParticipants.xsd “ to all elements underneath
> 'ExchangeRTParticipans' element than you don't need to specify a prefix for
> this namespace. It will be considered the default namespace for each node
> under the element that declares this namespace. So the namespace declaration
> inside your xml instance will be:
> <ExchangeRtParticipants
> xmlns="http://tempuri.org/ExchangeRtParticipants.xsd">...
> - However if you keep the prefix declaration in your top level element
> <ExchangeRtParticipants
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd">...; that means
> that only the elements that are declared using the prefix in their name are
> bound to that specific namespace. In your case <AgreementParticipant> is NOT
> declared using the prefix 'er' and because of that it is NOT part of
> '"http://tempuri.org/ExchangeRtParticipants.xsd” namespace; it is part of the
> 'NoName' namespace. That is why when you query for
> '//er:AgreementParticipant' using the 'er' prefix in your XPath query inside
> the OpenXML clause nothing is returned; AgreementParticipant element DOES NOT
> belongs to "http://tempuri.org/ExchangeRtParticipants.xsd" namespace, it
> belongs to 'nonamespace' uri. So if you don’t want <AgreementParticipant> to
> belong to namespace referent by ‘er’ than you need to change you XPath expr
> to '//AgreementParticipant' in order to get values out of that element.
>
> I’ve changed your sample code below to implement the solution where you
> don’t specify a prefix to "http://tempuri.org/ExchangeRtParticipants.xsd”
> namespace in your xml instance declaration so it will be the default
> namespace for every node under your top level node. Please let me know if
> that was your primary intention.
> -- modified sample
> DROP PROCEDURE foo
> GO
> create procedure foo (@.doc XML) -- use ntext if you are running SQLServer
> 2000
> AS
> BEGIN
> DECLARE @.iDoc INT
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.doc, '<root
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
>
> SELECT ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
> WITH(
> ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
> Identifiernvarchar(100) '@.Identifier',
> ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
> ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
> SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
> SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
> TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
> TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
> BeginDtsmalldatetime '@.BeginDt',
> Ratedecimal '@.Rate',
> RateSourcenvarchar(1) '@.RateSource',
> ParticipantIsManagedbit '@.ParticipantIsManaged'
> )
> EXEC sp_xml_removedocument @.iDoc
> END
> GO
> EXEC foo 'N<?xml version="1.0" ?><ExchangeRtParticipants
> xmlns="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
> ReinscReferenceNicknameTxt="NACRE" Identifier="2"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
> /><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
> /></ExchangeRtParticipants>'
> Thanks,
> Ana Elisa - SDET - SQLServer Group
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jeff Spicolli" wrote:

Root Element MIssing & For security reasons DTD is prohibited in this XML document

This error comes up in the print preview and nothing prints.

We are using VS-2005, SQL 2005 and SSRS 2005.

We had the same problem yesterday , so we have reinstalled IIS, .Net Framework 2.0 and Reporting Services, then reconfigured RS security, uploaded all the reports and enabled remote errors. After doing everything this error was gone and we were operating pretty fine.

Suddenly just we started getting the same annoying message."ROOT ELEMENT MISSING" from Report Server and from Report Manager we have "

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. "

Please Help. I need to fix this ASAP.

Are you running out of memory on the server?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=128205&SiteID=1

|||

Thanks Greg for the quick response.

We just restarted everyting and it seems all the reports are working fine now. But what annoys me is why does this happen. Do you think the Buffer cashe was full and it triggred this operation. If yes then what do we have to do to increase the Buffer cache.(we have 150000 i.e 15gigs of buffer settings.)

If not buffer cache then what ?

Please help me understand this problem.

|||

I am an associate of VDeepak, the problem is definately not as simple as low memory. The report that is erroring out is only a page or so, and we routinely run 200 + page reports on this server. If it helps, here is the trace from the application error log:

Event Type: Error
Event Source: IHRS
Event Category: None
Event ID: 0
Date: 8/10/2007
Time: 9:28:40 PM
User: N/A
Computer: AFTRAHR-APP04
Description:
2007-08-10 21:28:40,225 [1] ERROR IHRSMESSAGE [(null)] - Root element is missing.AFTRA\escherrer
System.Xml.XmlException: Root element is missing.
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res)
at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlTextReaderImpl.Read()
at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Load(XmlReader reader)
at Microsoft.Reporting.WinForms.WebRequestHelper.ExceptionFromWebResponse(Exception e)
at Microsoft.Reporting.WinForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension)
at Microsoft.Reporting.WinForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension)
at Microsoft.Reporting.WinForms.ProcessingThread.ProcessThreadMain(Object arg)

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

|||

Usually what this means in a webapp with localmode (RDLCs) is that the deployed RDLC didn't... deploy or build right...

I am not going to say this correctly, but the general idea is: when you get the "root element is missing" the actual RDLC that deployed is completely empty.

Here I see WinForms and ServerMode -- so it isn't quite the same thing, it's just a suspiciously similar error. So I am wondering: could something be wrong in the ReportServer Tempdb? Could this be refreshed after a restart?

>L<

|||

I think you may be on the right track. It is indeed cleared up after a restart, but doesnt re-occur for any particular reason. Every time we think we know what triggers it we restart and trigger again but it doesnt reoccur.

We noticed after a deloy it happened, so we restarted and it was gone. Then re-deployed the same reports and no problem. Then yesterday I was changing a report server timeout setting and the problem came back. Restarted and problem was gone. Changed setting again but problem did not reoccur.

Very frustrating indeed.

|||

Hmm.. When it happens, does it always happen with the same reports? How are you doing the deploy?

>L<

|||

We deploy from visual studio 2005 to a RS 2005 instance. The one time we correlated deployment from a set of three reports it did not ever cause the error again. There was one report in particular that the error was discovered on but it exists on all reports.

To be specific, when this error is happeneing, we can view a 1 page report, but print preview gives the root element missing error.

|||>>We deploy from visual studio 2005 to a RS 2005 instance

Have you tried the "upload file" method of deploying rather than from Visual Studio? It's not likely to make any difference, given that you can actually *see* the report although not *print preview* *** . IOW, if the report definition is not empty, then it is not, no matter what you're trying to do with it. -- This is really cr*ppy, btw, you have my sympathies.

*** I didn't realize before that the same report was giving these intermittent, different results *without* a restart.

If you can see the report and not print preview it sounds more like the report definition is there but not available. Could it be locked? Again, I'm thinking the reportserver tempdb, but I do not have any information about HTH or WTH this might occur <s>. But the fact that a timeout made a difference is suspicious.

A couple of things I'm going to ask just in case it rings a bell for any MS guys reading:

* -- were these report definitions migrated from RS 2000? what about the SQL server? Did you by any chance upgrade RS from 2000 to 2005? (I'm thinking maybe run DBCC UPDATEUSAGE in that case?)

* -- anything interesting in either the SQL Server logs or the available reports for either the ReportServer or ReportServerTempDB databases for the time period(s) in question?

>L<