Converting SSRS 2008 reports to SSRS2005

If you are reading this, you are probably aware of the fact that you cannot deploy SSRS 2008 reports on an SSRS2005 server.  SSRS reports are housed in rdl files.  rdl files are basically xml files that follow a predetermined schema.  The schemas for 2005 and 2008 are very different.  The objects in use on the same report between the two versions can be radically different, and Microsoft does not support a downgrade path between the two.

There are probably not many reasons why you would end up in this situation, but if you find yourself there, here are your optons:
1. Upgrade the existing server to SQL Server 2008
2. Deploy the reports on another server that is already running SQL Server 2008
3. Set up a new server on SQL Server 2008
4. Rewrite the 2008 reports in 2005 manually
5. Attempt to use a conversion tool to downgrade the reports automatically

There are a couple of unsupported "tools" out there that individuals have written for their specific circumstances, so the conversion CAN be done.  However, it would not be easy due to new objects like the tablix. Options 1-3 would most likely be more cost effective than paying to have each report downgraded and tested.

If you are interested in trying, here are some links that might help:

This person claims to have written a tool that they will sell you - amarsale@gmail.com


RDL spec for 2008 - http://download.microsoft.com/download/6/5/7/6575f1c8-4607-48d2-941d-c69622e11c32/RDL_spec_08.pdf


RDL spec for 2005 - http://download.microsoft.com/download/c/2/0/c2091a26-d7bf-4464-8535-dbc31fb45d3c/rdlNov05.pdf


Did this help you?  If so, please leave a comment!

Error: Attempted to Read or Write Protected Memory

This error is displayed when trying to open or close a window with a .NET addin on it.  It is usually followed my the error
BAD_MEM_HANDLE

or simply the dreaded "The application has to close" error

Then GP crashes.

The causes are data buffer related.  Either there is a table buffer that has been opened in .NET that has not been closed, or a reader has not been closed.

The reader error can be hard to detect when reading the code.  For example, the following code will result in the reader being left open.

OdbcDataReader reader = oCmd.ExecuteReader();


if (reader.Read())
{
return  reader.GetString(0);
}
else
{
return string.Empty;
}

 
The reader can't be closed before the return statement, since the reader is needed for the return value.
 
To fix it, we have the take the long way around and store the return value before closing the reader.
 
OdbcDataReader reader = oCmd.ExecuteReader();

string result = "";
if (reader.Read())
{
result = reader.GetString(0);
}
else
{
result= string.Empty;
}
if (!reader.IsClosed)
{
reader.Close();
}
return result;

 
So the lesson learned here is that when working with readers, it is a good idea to store your reader results and close the reader before operating on them.. That way you won;t try to take a shortcut like the first example.

Did this help you?  If so, please leave a comment!

Handy zip code table

Thank goodness for the US Census!  The 1999 Census contained public domain info on zip codes (info the businesses usually pay a pretty penny for for the postal service).

I was able to use this data to create a SQL table with some useful zip code info in it like:
City
State
ZipCode class (tells you if it is a unique zip code or po box only)
Lat/Lon (for mapping applications)

I used SQL 2008's great Generate Scripts Wizard to export the table structure, index definitions, and all the data to a single script file.  You can find the zip file here (I just know there is a pun in there somewhere).

I will keep an eye out for a newer census report, but zip codes don;t change all that often, so this list should be pretty accurate.


Did this help you?  If so, please leave a comment!

SRS default parameters

I ran into an issue today where I had a report parameter default that I couldn't seem to get rid of.

In BIDS, I deleted the defaults for all parameters, then deployed the report, choosing the overwrite option.

In BIDS, the defaults were gone when I ran the report.  However, when opening from the SRS report viewer window, one of the parameters was still there.

After a bit of troubleshooting and digging around, I found out that the properties that are set up through the SRS report manager in the browser are not always reset when a report is re-deployed.  That is what was happening here.  Even though all the other changes and even the clearing of the defaults on the other parameters was flowing down when I deployed the report, this one parameter default was remaining in the SRS SQL table somehow.

So I used the report manager to delete this parameter's default too, and we are in business.

I still do not know what caused the issue, so I am adding a step to my personal best-practice for SRS report deployment - open the report manager and review the properties.

Happy Coding!



Did this help you?  If so, please leave a comment!

Using T-SQL to remove non-printable characters

 We frequently have a need to remove non-printable characters from text fields for export or printing.  Most often, this is the chars 9,10,or 13, but can frequently consist of other unicode characters.

Before I go on, let me say that I understand the whole idea of "printable" is dependant on what you mean by "print".  For simplicity, I am defining "printable" as anything in the base ASCII set (<128) that will actually display in the standard SQL Management Studio query results.  Therefore, everything else is "non-printable".  I realize there may be an exception or two, so I made sure to write code that was easily modifiable to include exceptions.

I first tried PATINDEX, but the pseudo-regex patterns can return somE wacky results, based on which default collation is being used.  It would also make the code harder to read and modify for someone not familiar with the PATINDEX flavor of regex.

So, here it is.  It is not the most efficient way, but certainly adequete, while being easy to modify.


DECLARE @I INT,
        @TEST VARCHAR(100)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
       @I=0 --start at zero


WHILE @I<256 --check entire extended ascii set
BEGIN
   IF @I=33 SELECT @I=127 --this jumps over the range that I want to keep
   SELECT @TEST=REPLACE(@TEST, CHAR(@I), ' ') --this replaces the current char with a space
   SELECT @I=@I+1
END

SELECT @TEST


Another way to do this which allows better granular control over which characters you allow is this:


DECLARE @GOOD VARCHAR(256),
        @TEST VARCHAR(100),
@I INT

SELECT @GOOD='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()-_=+/?.>,<;:|~ ' + char(13) + char(10) + char(9)

SELECT @TEST='123' + CHAR(13) + '412' + CHAR(200) + '341', --testing string
@I=0
SELECT @TEST


WHILE @IBEGIN
IF PATINDEX('%' + SUBSTRING(@TEST,@I,1) + '%',@GOOD)=0
BEGIN
SELECT @TEST=REPLACE(@TEST,SUBSTRING(@TEST,@I,1),' ')
END

   SELECT @I=@I+1
END

SELECT @TEST

Did this help you?  If so, please leave a comment!

More adventures in T-SQL

I often see overly complicated where clauses in procedures or views that could have been simplified with a little trick I have been using for years.

Here is an example of a where clause which is hard to read, and therefore hard to debug and modify:

WHERE (SV00700.Service_Call_ID = @Service_Call_ID OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (SV00700.Call_Invoice_Number = @Call_Invoice_Number OR
@DocumentNumber <> '' OR
@BatchNum <> '') AND (@DocumentNumber = '' OR
@DocumentNumber = rtrim(SV00700.Call_Invoice_Number)) AND (@BatchNum = '' OR
SV00700.BACHNUMB = @BatchNum)

The intent here was to filter based on a combination of parameters.
The trick I like to use is simple:  Assume all parameters are empty and temporarily replace them with the values.  If the statement still looks complicated, you need to refactor.

In this case:
WHERE (SV00700.Service_Call_ID = '' OR

'' <> '' OR
'' <> '') AND (SV00700.Call_Invoice_Number = '' OR
'' <> '' OR
'' <> '') AND '' = '' OR
'' = rtrim(SV00700.Call_Invoice_Number)) AND '' = '' OR
SV00700.BACHNUMB = '' ) AND remit2.INTERID = rtrim(db_name())

Notice all those instances of '' <> ''.  That is what SQL is actually seeing at run-time if the parameter is an empty string. 

We can make this much easier to read by figuring out how to evaluate each parameter individually, rather than trying to evaluate all possible combinations.

Consider this:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM(@Service_Call_ID) WHEN '' THEN '%' ELSE RTRIM(@Service_Call_ID) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@Call_Invoice_Number) WHEN '' THEN '%' ELSE RTRIM(@Call_Invoice_Number) END
  AND SV00700.Call_Invoice_Number LIKE CASE RTRIM(@DocumentNumber) WHEN '' THEN '%' ELSE RTRIM(@DocumentNumber) END
  AND SV00700.BACHNUMB LIKE CASE RTRIM(@BatchNum) WHEN '' THEN '%' ELSE RTRIM(@BatchNum) END
It accomplishes the same thing, but is much easier to read.  Further, each parameter can be easily removed without reworking the entire statement.
 
When we apply the trick to it:
WHERE SV00700.Service_Call_ID LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.Call_Invoice_Number LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END
AND SV00700.BACHNUMB LIKE CASE RTRIM('') WHEN '' THEN '%' ELSE RTRIM('') END

It is still easy to read. 

I hopes this helps someone else to go forth and simplify.



Did this help you?  If so, please leave a comment!

Wentity tip

I was working with a wentity object (a custom data object).  I bound it to some controls on my form.
Later, when coding the logic to open the form, I passed in values that were set on some of these fields to pull up an initial record.  When testing, however, I found that when I entered and then left one of these fields, the values in the other 2 were cleared.  I could not find any of my code that was the culprit.  Then I began wondering if the wentity object was the culprit.

In deed it was.  Each time I refreshed the window, I newed up the wentity object.  This cleared the fields in the object, but not on the window.  However, whenever I did anything that called validation logic from the wentity object, the values were refreshed (and cleared)!

The fix was to manually set the values for these three variables in the wentity object each time I newed it.


Did this help you?  If so, please leave a comment!