RS2000 and SQL Server 2005

My company has different offerings for SQL Server itself and another for
Reporting Services. Our SQL Server is SQL Server 2005. The Reporting
Services offering is Reporting Services 2000 (with SQL Server 2000 for its
own database). The reports we would be creating would need to execute stored
procedures in the SQL Server 2005 environment. Is it possible to use
Reporting Services 2000 with SQL Server 2005? Thank you in advance for
guidance.You need to understand that what data you are reporting off of can be pretty
much anything (or at least a wide range of products). I report off of
Sybase, SQL Server 2000, SQL Server 2005, In-SQL (a real time database). I
don't report off it but I know Oracle is widely supported.
So, yes, without a doubt you will have absolutely no problem reporting off
of a stored procedure running in SQL 2005 from RS 2000.
One point, follow the below suggestions for stored procedures and temporary
tables:
If you use temp tables here is a handy list on how to write your stored
procedure to use temp tables in a RS compatible way:
1. Click on the refresh fields button (to the right of the ...) if your
field list is not showing up.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables. Let the temp tables just fall out
of scope. SQL Server will properly dispose of them when they are no longer
needed. I think people explicitly drop them due to habit. Most likely at one
time it was the proper thing to do. It is not necessary and if you drop them
then stored procedures will not work with RS.
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I have found this to only be an issue when you create a temp table in your
stored procedure that is then filled with data from another stored
procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SSM" <SSM@.discussions.microsoft.com> wrote in message
news:37FB341E-1FE7-4FC9-B0E9-316524947F14@.microsoft.com...
> My company has different offerings for SQL Server itself and another for
> Reporting Services. Our SQL Server is SQL Server 2005. The Reporting
> Services offering is Reporting Services 2000 (with SQL Server 2000 for its
> own database). The reports we would be creating would need to execute
> stored
> procedures in the SQL Server 2005 environment. Is it possible to use
> Reporting Services 2000 with SQL Server 2005? Thank you in advance for
> guidance.