Converting Reporting Services (SSRS) rdl from 2005 into 2000

If you want to convert a SSRS rdl from 2005 to 2000, you can try the following

1) Open the .RDL file in a text editor and change the XML.
2) Change the first node to look like this

Report xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition” xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner”

3) Do a search for “Interactive” You should find 2 nodes InteractiveWidth and InteractiveHeight. You need to delete these nodes.

4) 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

5) In 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.

THIS IS NOT SUPPORTED SO I WOULD BACKUP YOUR REPORTS BEFORE ATTEMPTING THIS AND USE THIS ADVICE AT YOUR OWN RISK.

How to design a solution

When designing a solution, you have to make sure that the solution is not based on a particular product. Below is a basic design document that you should use when designing a solution which is based on SSADM.

Its very important to remember when undertaking a new project, that good design will overcome bad programming, but good programming will never overcome a bad design. If you design a technology based solution that does not work when you need it, you would have used technology to drive up your costs.

The worst thing you can do is buy a product, then design your solution around that product.
i.e Buying Crystal Reports then finding a solution that only can use Crystal Reports functionality

What you should do is design a 'Report' solution, then evaluate all the reporting products on the market that can fulfill for your design which may in the end be the Crystal Reports product.

Another bad practise is to design your solution on the fly, so that means coding and designing at the exact same time without a clear path to the solution. If you dont know where you are going, every path will take you there. So I would advice you to use a design document like the one below to aid you in your solution process.

1 Introduction

2 Business Activity Model
2.1 Fact Finding Methods
Stakeholder Analysis

2.2 Functional Requirements
1) Easily to implement
2) Easy to maintain and upgradeable

2.3 Current Physical Data Flow Diagram
2.4 Current Logical Data Flow Diagram

3 Technical system options
3.1 Assumptions and Dependencies
The finished solution will be using the .NET Framework to access the Acme Network. Any databases that are used for the solution will use either SQL Server 2005 or SQL Server 2000.

3.2 General Constraints
3.3 Goals and Guidelines
The design goals for this project are as follows

· KISS (Keep it Simple Stupid).
· Design with the ability to provision numbers quickly and reliably.

4 Detailed business specification
4.1 Physical Data Flow Diagram: Option 1
4.2 Logical Data Flow Diagram: Option 1
4.3 Logical Data Model Diagram: Option 1
4.4 User Interface Design: Option 1
4.5 Risk Assessment: Option 1
4.6 User Roles: Option 1

5 Logical design

In this stage, technically feasible options are chosen. The development/implementation environments are specified based on this choice. The following steps are part of this stage:

· Define BSOs (Business Systems Options). Its purpose is to identify and define the possible approaches to the physical implementation to meet the function definitions.It also validates the service level requirements for the proposed system in the light of the technical environment.
· Select BSO. This step is concerned with the presentation of the BSOs to users and the selection of the preferred option.


6 Logical process design

In this stage, logical designs and processes are updated.

Additionally, the dialogs are specified as well. The following steps are part of this stage:

· Define user dialogue. This step defines the structure of each dialogue required to support the on-line functions and identifies the navigation requirements, both within the dialogue and between dialogues.
· Define update processes. This is to complete the specification of the database updating required for each event and to define the error handling for each event.
· Define enquiry processes. This is to complete the specification of the database enquiry processing and to define the error handling for each enquiry.

7 Physical Design

· Screenshots
· Prototypes

8 Policies and Tactics
Test Description and Test Plans
Outcome
8.1 Error Codes

9 GLOSSARY

Transaction Log filling up problem

The most common reason for a transaction log in simple mode to fill up is because a transaction is still open. If you run DBCC OPENTRAN you will find out if this is the case.

If you want to set a trace to run at a specfic time, then you will have to do the following.

First, create your trace in Profiler as you would normally do. Then from the "Files" menu, select "Script Trace," then select the version of SQL Server you want to trace. This command will create a text file that includes all of the necessary commands to run the trace you desire.

Second, create a new Transact-SQL job step and then cut-and-paste the contents of this above text file into the Command box of the job's step. Now schedule the job to run. The Profiler trace will run until you manually stop it. If you want the script to stop automatically, you will have to manually modify the script to stop at a specified time

Import millions of records into SQL Server

In any mass import of data you need to do the following steps

1) Change the recovery mode to 'Bulk Logged' (or Simple if you dont have a backup strategy on the server)
2) Remove the indexes on the table (I would consider removing the Clustered and Non Clustered Indexes)
3) Import your 25 million records (I would try and do them in batches on 100,000 each to keep the tempdb from growing to big)
4) Reapply your indexes to your table
5) Change back your recovery mode on your database.

Testing Linked Servers Connections in SQL Server 2005

If you want to test linked server connections in SQL Server 2005, use the code below


declare @srvr nvarchar(128),
@retval int;set @srvr = 'my_linked_srvr';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0 raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );