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!

Office 2010

I love these new ribbons in Office.

I created a custom ribbon and moved it to the top of the tab list show it is the default tab that shows.
It has my most commonly used tasks in outlook (some of which normally require significant digging to get to).

I exported it in case anyone else is interested in it.

The true beauty is that the exported file is XML..woot!

So I can post it here.  Just save it to a notepad file and give it the extension .exportedUI
Then right click on any tab in outlook and choose customize.  At the bottom of the window, choose Import/Export, then Import.  Browse to the file and import it.


Here is the xml for it:

<mso:cmd app="olkexplorer" dt="1" />
<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">
<mso:ribbon>
<mso:qat/>
<mso:tabs>
<mso:tab id="mso_c1.27D5463" label="David's Tab" insertBeforeQ="mso:TabMail">
<mso:group id="mso_c2.27D5463" label=" " autoScale="true">
<mso:control idQ="mso:NewMailMessage" visible="true"/>
<mso:control idQ="mso:NewAppointment" visible="true"/>
<mso:control idQ="mso:RulesAndAlerts" visible="true"/>
<mso:control idQ="mso:OutOfOffice" visible="true"/>
<mso:control idQ="mso:AllModuleNameItems" visible="true"/>
</mso:group>
<mso:group id="mso_c1.288DC28" label=" " autoScale="true">
<mso:control idQ="mso:SendReceiveAll" visible="true"/>
<mso:control idQ="mso:Reply" visible="true"/>
<mso:control idQ="mso:ReplyAll" visible="true"/>
<mso:control idQ="mso:Forward" visible="true"/>
<mso:control idQ="mso:Delete" visible="true"/>
</mso:group>
<mso:group id="mso_c1.287D94B" label=" " autoScale="true">
<mso:control idQ="mso:MarkAsRead" visible="true"/>
<mso:control idQ="mso:MarkAsUnread" visible="true"/>
</mso:group>
<mso:group id="mso_c2.288638F" label=" " autoScale="true">
<mso:control idQ="mso:CheckForUpdates" imageMso="GreenBall" visible="true"/>
<mso:control idQ="mso:ShowInConversations" visible="true"/>
</mso:group>
</mso:tab>
</mso:tabs>
</mso:ribbon>
</mso:customUI>


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

More fun with SSRS

One of the nice things about Visual Studio is that you can set a default font for the controls and then as you drag them onto the form, the font is applied.

But what happens when you later want to adjust the default font size for the entire page, for instance?
Changes to the default do not flow down to the existing controls (Hint to MS, this would be a awesome feature to add!)

To handle this, you can open the report and choose View > Code, then do a find/replace on the elements I am targeting.  Don't try to scroll down and find the elements, since they are not necessarily listed in any logical order.

Someone should write a mass re-formatting tool for SRS that uses regex to find certain nodes and alter the structure for the new version.



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