Posted by: Charles Maitland | Wednesday 12 April, 2006

Reporting Services 2005 into 2000

In SQL Server Reporting Services (SSRS) 2005 there are a whole host of new features. This means that reports that are developed in Visual Studio 2005 AKA the “SQL Server Business Intelligence Development Studio” cant be deployed to SQL Server 2000 Report Servers or opened in Visual Studio 2003.

Unfortunately that is just what a colleague of mine needed to do.

Fortunately I remembered reading somewhere, cant find the reference now, that with a bit of a hack it can be done. BUT ONLY FOR BASIC REPORTS!

What you need to do is open the .RDL file in a text editor and change the XML.

The First node needs to be changed to read:

<Report xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition&#8221; xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner”&gt;

Then do a search for “Interactive” You should find 2 nodes InteractiveWidth and InteractiveHeight. You need to delete these nodes.

I have found that reports then work.

NOTE. This is not an approved, supported or condoned by anyone. Do it at your own risk and take backups!

Advertisements

Responses

  1. I did this however, I get another message (this report takes 2 date parameters named pBegDate and pEndDate) that says,

    “An error has occurred during report processing. (rsProcessingAborted) Get Online Help
    Query execution failed for data set ‘JLG_SQL’. (rsErrorExecutingCommand) Get Online Help
    Line 1: Incorrect syntax near ‘pBegDate’. “

  2. I would guess that there is a different way that dates are handled when it come to parsing the SQL Statement for your data set.
    The only thing can suggest is to take out the dates try again and then, if it wrks, put them back in.
    Sorry it is a dirty hack!

  3. The XML in the RDL file is different between 2005 and 2000 when you want to pass parameters to a SQL procedure.

    In 2000 they pass parameters this way in the RDL file:

    StoredProcedure
    =”procGetInvMovement”

    =Parameters!pBegDate.Value

    =Parameters!pEndDate.Value

    JLG SQL

    and in 2005 they do it this way:

    true
    =”Execute procGetInvMovement ‘” & Parameters!pBegDate.Value & “‘, ‘” & Parameters!pEndDate.Value & “‘”

    =Parameters!pBegDate.Value

    =Parameters!pEndDate.Value

    JLG SQL

    Notice how they don’t include the parameters in the commandtext tag in 2000!!!

    It worked for me after I figured that out.

    Hopes this saves someone some time!!!

  4. oops—I guess it took out all my XML tags. I’ll try this again.

    The XML in the RDL file is different between 2005 and 2000 when you want to pass parameters to a SQL procedure.

    In 2000 they pass parameters this way in the RDL file
    (note I used &lt for the symbol:

    &lt Query&gt
    &lt CommandType&gt StoredProcedure&lt /CommandType&gt
    &lt CommandText&gt =”procGetInvMovement”&lt /CommandText&gt
    &lt QueryParameters&gt
    &lt QueryParameter Name=”@BegDate”&gt
    &lt Value&gt =Parameters!pBegDate.Value&lt /Value&gt
    &lt /QueryParameter&gt
    &lt QueryParameter Name=”@EndDate”&gt
    &lt Value&gt =Parameters!pEndDate.Value&lt /Value&gt
    &lt /QueryParameter&gt
    &lt /QueryParameters&gt
    &lt DataSourceName&gt JLG SQL&lt /DataSourceName&gt
    &lt /Query&gt

    and in 2005 they do it this way:

    &lt Query&gt
    &lt rd:UseGenericDesigner&gt true&lt /rd:UseGenericDesigner&gt
    &lt CommandText&gt =”Execute procGetInvMovement ‘” & Parameters!pBegDate.Value & “‘, ‘” & Parameters!pEndDate.Value & “‘”&lt /CommandText&gt
    &lt QueryParameters&gt
    &lt QueryParameter Name=”pBegDate”&gt
    &lt Value&gt =Parameters!pBegDate.Value&lt /Value&gt
    &lt /QueryParameter&gt
    &lt QueryParameter Name=”pEndDate”&gt
    &lt Value&gt =Parameters!pEndDate.Value&lt /Value&gt
    &lt /QueryParameter&gt
    &lt /QueryParameters&gt
    &lt DataSourceName&gt JLG SQL&lt /DataSourceName&gt
    &lt /Query&gt

    Notice how they don’t include the parameters in the commandtext tag in 2000!!!

    It worked for me after I figured that out.

    Hopes this saves someone some time!!!

  5. One more addition to the 2000 version of the .RDL file. You will need to add this line:

    &ltCommandType&gtStoredProcedure&lt/CommandType&gt

    after the first &ltQuery&gt tag!

  6. I cannot say THANK YOU!!!! loud enough. This saved me countless hours of work and was SOOO simple.

    I wasn’t using stored procedures, all my queries were in the reports so it worked like a charm. I had a couple of other elements (fixed headers, hidden parameters) that didn’t exist in 2000. I simply removed those elements when I got the error and it worked like a charm for each and every one of my reports.

    Again, thank you.

  7. Very very useful, thank you.

    I am developing reports on sql 2005, but suddendly one of my customers asked for an urgent new report in the old production server. I do not have visual studio 2003 installed on my machine, so i was looking for your advice! Great!

  8. THANK YOU JASON!!!! I was getting the same error. I was using a stored procedure, and I saw your post, added StoredProcedure RIGHT AFTER the tag in my .rdl file, and it worked!!! You’ve made my day!

  9. THANK YOU! Worked Fine for me.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: