SQL Server 2005 CTE (Common Table Expression)

SQL Server 2005 CTE (Common Table Expression)

By Bihag Thaker

CTE is a new feature provided by Microsoft SQL Server 2005. In real world, we often need to query hierarchical data from the database. For example, to get a list of hierarchical list of all the employees, list of product categories etc. CTE fulfills this requirement and let us query the database recursively.

Demo Builder - Create Flash Presentations
Create interactive Flash movies that allow you to show how applications and systems work. Download a FREE trial now.

Let’s us do this practically. Assume that we store different categories of computer books and any category can have sub-categories. For this, we will create a table named tblCategories with the following structure and insert some categories into this table as shown below:

Create Table tblCategories
(
CategoryID Int Constraint PK_tblCategories_CategoryID Primary Key,
CategoryName VarChar(100),
ParentCategoryID Int Constraint FK_tblCategories_ParentCategoryID References tblCategories(CategoryID)
)

GO

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(1,'Languages',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(2,'Networking',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(3,'Databases',Null)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(4,'Visual Basic',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(5,'C#',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(6,'Java',1)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(7,'VB.Net',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(8,'VB 6.0',4)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(9,'Desktop Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(10,'Web Application Development with VB.Net',7)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(11,'ActiveX Objects and VB 6.0',8)

Insert Into tblCategories(CategoryID,CategoryName,ParentCategoryID) Values(12,'Network Security',2)

Now if you query the database with the following SELECT command,

Select * From tblCategories Where CategoryID = 1

You will get the following result:

CategoryID CategoryName ParentCategoryID
----------- ---------------- ----------------
1 Languages NULL

(1 row(s) affected)

No surprise! But what, if you want to get the list of all the categories/sub-categories falling under the root category ‘Languages’? To do this, you will need to perform a recursive query and to do that we use CTE.

Let’s do this with the help of CTE. To create CTE we will use the following syntax:

With cteCategories
AS (
Select CategoryID,CategoryName,ParentCategoryID
From tblCategories
Where CategoryID=1

Union All

Select C.CategoryID,C.CategoryName,C.ParentCategoryID
From tblCategories As C Inner Join cteCategories As P On C.ParentCategoryID = P.CategoryID

)

Select CategoryID,CategoryName,ParentCategoryID From cteCategories

Run the above query and see the result as shown below:

CategoryID CategoryName ParentCategoryID
----------- ---------------------------------------------------- ----------------
1 Languages NULL
4 Visual Basic 1
5 C# 1
6 Java 1
7 VB.Net 4
8 VB 6.0 4
11 ActiveX Objects and VB 6.0 8
9 Desktop Application Development with VB.Net 7
10 Web Application Development with VB.Net 7

(9 row(s) affected)

SQL Server 2005 Paging Performance Tip

SQL Server 2005 Paging Performance Tip

By Robbe Morris

This quick tip demonstrates how to get the total rows as part of the paging query as well as how avoid a common coding error with joins that can harm performance.

Demo Builder - Create Flash Presentations
Create interactive Flash movies that allow you to show how applications and systems work. Download a FREE trial now.

I've seen the following technique in several beginner code samples for demonstrating SQL Server 2005's ability to return paged results.

I've added the TotalRows = Count(*) OVER() line to demonstrate how return the total rows returned above and beyond the row count for the paged set. This removes the need for a second query to get the total rows available for paging techniques in your application. In your application, just check to make sure your resultset has records, then just grab the first record and retrieve its TotalRows column value.

Notice that in this query, the JOIN between the Orders table and the Users table is being run across all records that are found NOT just the records returned in the paged set.

declare @StartRow int
declare @MaxRows int

select @StartRow = 1
select @MaxRows = 10

select *
from
(select o.*,u.FirstName,u.LastName,
TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
from Orders o , Users u
WHERE o.CreateDateTime > getdate() -30
AND (o.UserID = u.UserID)
)
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1

If you adjust your query as follows, you will see a substantial boost in performance. Notice this query only performs the join on the returned resultset which is much, much smaller.

SELECT MyTable.*,u.FirstName,u.LastName
FROM
(SELECT o.*,
TotalRows=Count(*) OVER(),
ROW_NUMBER() OVER(ORDER BY o.CreateDateTime desc) as RowNum
FROM Orders o
WHERE o.CreateDateTime > getdate() -30
) as MyTable, Users u
WHERE RowNum BETWEEN @StartRow AND (@StartRow + @MaxRows) -1
and (MyTable.UserID = u.UserID)

10 PowerBuilder Pitfalls

First the good news: PowerBuilder is a great tool; in fact you can't
accidentally do much wrong. This
strength is based on a number of reasons. The following is list of why
I think PowerBuilder is so great, but
you might like to add one or two more items to it:
PowerScript is simple; it's easy to learn because of its clearly
laid out grammar.
PowerScript is a strongly typed language; many problems
simply don't arise because of the compiler telling you about an
error.
PowerScript is easy to read. PowerBuilder itself takes care of
the tedious task of correct indentation. Thus PowerBuilder
programs look pretty much the same in terms of "layout" in all
PowerBuilder shops around the world.
There is no pointer system. You cannot access addresses of objects and
do nasty things with them.
PowerBuilder does all the low-level memory management for you. There's
no need to allocate and
dealloacate memory for strings or arrays, as it's done for you. When
using objects, garbage collection
cleans up after you.
No dangling pointers. All references to a single object become invalid
whenever the the object is
destroyed and can be checked by using IsValid().
Values of simple data types are initialized - numeric values with
zero, Booleans with FALSE, etc.
Those elements are part of the strength of PowerBuilder (plus the
DataWindow, of course). However, there
are a few pitfalls in PowerBuilder programming that seem to frequently
occur. This article will show you
some of those issues. I have collected them over time in my function
as technical lead where I do a lot of
code review and "programmers first-level support" as I repeatedly come
across the same problems. For
some of the problems, I'll propose a workaround to make your programs
safer. Let's start.
1. Find With Wrong Parameters
What's wrong with the following piece of code?
l_max = ds.RowCount()
l_index = ds.Find('x>5', 1, l_max)
DO WHILE l_index > 0
of_WorkOn(l_index)
l_index = ds.Find('x>5', l_index + 1, l_max)
LOOP
Answer: It hides a potential endless loop - Find() can search
backwards! If the value in column x is greater
than 5 in the last row of the DataStore, l_index will eventually
become l_max. The Find() will read:
l_index = ds.Find('x>5', l_max + 1, l_max)
PowerBuilder recognizes that l_max + 1 is greater than l_max and will
start to search backwards. Of course,
it will find the row l_max that fits the expression. The perfect
endless loop is done.
HOW TO AVOID PITFALL 1:
This behavior is documented and thus won't change. What can we do
about it? There are two workarounds:
Page 2
Always code the Find finishing not at RowCount(), but at RowCount() + 1.
Implement a function Find in your DataWindow or DataStore ancestor
class that only takes two
arguments: the expression and the starting row. Within the function
use the solution stated above.
2. Incorrect Boolean DataWindow Expressions
This pitfall is really a major problem if programmers are not aware of
it, so be warned. PowerBuilder
supports two language grammars: PowerScript and DataWindow Expression
syntax. Many functions known
from PowerScript are available within DataWindow expressions as well,
so it's easy for us to code in each
of them. But beware, there are some subtle and very important differences.
Whats's wrong with the following expression?
NOT IsNull(x) AND x > 100
Nothing really. At least if you use it within PowerScript, PowerScript
will evaluate it to
(NOT IsNull(x)) AND (x > 100)
But take care: if you use the same expression within a DataWindow (for
Find, Filter, etc.), PowerBuilder
will evaluate it to:
NOT (IsNull(x) AND (x > 100))
therefore evaluating it to FALSE, because a value cannot be NULL and
greater than 100 at the same time.
What is the reason for that?
DataWindow expressions have a different operator precedence for
logical operators compared to
PowerScript (and just about any other computer language I know).
Usually NOT has precedence over AND,
and AND has precedence over OR. But within DataWindow expressions, AND
and OR have precedence
over NOT. AND and OR will be evaluated in the order of how they occur.
This is very weird, but still is expected and documented behavior.
Simply start PowerBuilder help and
search for "operators:precedence", and you'll find the two diverging
styles of logical operator precedence
within PowerBuilder.
HOW TO AVOID PITFILL 2:
The only way you can get around this problem is to use brackets to
tell PB explicitly what you mean. For
the example above you'll need to write:
(NOT IsNull(x)) AND (x > 100)
3.Missing Message Boxes
Sometimes we use MessageBoxes for "quick and dirty" debugging. Once in
a while a programmer tells me
that PowerBuilder is not showing message boxes anymore. Take a look at
the next code snippet; what could
be wrong here?
s_data = ds.GetItemString(1, 'data')
MessageBox('data is', s_data)
The reason that PowerBuilder doesn't show the MessageBox is simple:
s_data is null! And PowerBuilder
does with MessageBox just about the same thing it does with all other
functions that are being called with
nulls as arguments: it does not execute the function but returns null.
HOW TO AVOID PITFALL 3:
Page 3
The workaround for that is simple, but involves some work on your
side: you need to code your own
version of function MessageBox (for instance as a global function).
You can't override PowerBuilder
built-in functions without losing the ability to call them, so you
need to give the function a new name, say
MsgBox. The problem is that there is a wealth of overloaded versions
of MessageBox functions, so you
need to implement quite a few. Within each of the calls, check the
arguments for nulls.
4. Not Fully Regenerated Source Code
Sometimes people in the Sybase newsgroups complain that PowerBuilder
is slow and not stable. More often
than not, the reason for that is the source code is not thoroughly
regenerated. This has been especially true
for the early versions of PB 7, where a change within the source of a
function changed the order of the
function list in the source code, which in turn led to incorrect
functions being called if you did not
regenerate all depending (i.e., calling) classes.
HOW TO AVOID PITFALL 4:
The workaround for this is simple: let computers do the dirty work for
you. Do a nightly regeneration of
your sources either with OrcaScript or any of the third-party tools
that focus on that kind of task.
5. Passing Objects per Reference
This is not really a pitfall but rather an aesthetic issue: many
developers apparently still believe that they
need to pass objects "by reference" in order to be able to change
their instance variables. This is wrong,
PowerBuilder does not pass the object but only the object reference
per reference. The only time you'll need
this is when you change the object reference in the called function
(for example, because you create or
re-create it).
HOW TO AVOID PITFALL 5:
That's an easy one: simply use pass by value for objects.
6. New DataWindow Columns Are Not Updatable by Default
A PowerBuilder classic - I guess just about every programmer tripped
over this behavior at least once in his
or her career. Whenever you add a column to a table, you need to add
it to the DataWindow result set in
order to retrieve it from the database. That's the simple part. But,
the newly added columns are not
updatable by default. PowerBuilder used to give those columns a tab
order of 0, so you couldn't edit the
column. However, with more recent builds, newly added columns get the
highest tab order, so you are able
to edit the columns right away. The bad part: those columns won't be
saved until you manually add them to
the list using the "update properties" dialog.
HOW TO AVOID PITFALL 6:
Always remember to edit the update properties when you add a column.
7. Using ClipBoard in a DataWindow
Is the function ClipBoard not working for you? It might be because you
use it within a DataWindow.
Sometimes the clipboard is used to store interim values. Using
ClipBoard(s_data) within DataWindow
source code is not successful, because the function ClipBoard is
implemented within the class DataWindow
with different semantics and thus hides the global function ClipBoard.
The result is that the data you want to
put in the clipboard simply isn't there.
HOW TO AVOID PITFALL 7:
When trying to access the clipboard in DataWindow sources, explicitly
use the global operator :: and
Page 4
therefore write ::ClipBoard(s_data).
8. SetItemStatus Needs Intermediate Step
This is also a PowerBuilder classic but is already well known: there
are some item statuses that can't be set
directly; you need a second SetItemStatus to achieve the goal. One
example: changing the column status
from "new" to "notModified" simply doesn't work. You need to set it to
"dataModified" first, then you can
change it to "notModified". Some others settings are allowed, but
don't work as expected; changing from
"newModified" to "notModified" will change the state to '"new".
HOW TO AVOID PITFALL 8:
Whenever you implement SetItemStatus, check with the PB online help
for the "SetItemStatus method" if
the combination is to be successful. Do an intermediate step if necessary.
9. No Runtime Error for Incorrect SetItems or SetFilters
If you issue an incorrect GetItemX on a DataWindow, PowerBuilder will
raise an error if you used a wrong
row, a wrong column name, or a wrong data type for the column.
Unfortunately, there will be no runtime
errors for incorrect SetItem commands. You need to check the return
code of the function yourself. The
same holds true for SetFilter. I believe these two functions to be
some of the most important ones for PB
scripts as they can influence the flow of execution and the stored data.
HOW TO AVOID PITFALL 9:
In your ancestor classes for DataStore and DataWindow, override all
SetItems and SetFilters and raise an
exception if there is an error. You will make your programs much more robust.
10. No Data After Retrieve
Retrieving data in a datastore doesn't always mean that you have the
read rows available in the primary
buffer. While any experienced PowerBuilder programmer will know this,
it is a common source of
problems for newbies. PowerBuilder applies the filter expression
defined in the DataWindow painter after it
has read the data. So it may happen that you read loads of data and
the return value of Retrieve is zero! Why
is that? The answer is simple: the return value of Retrieve is the
number of rows in the primary buffer after
the Retrieve, not the number of rows retrieved. The two numbers (rows
retrieved versus rows in primary
buffer) might be different in two cases:
There is a filter expression defined that filters some data.
PowerBuilder applies the filter after having
read the rows. You will find those rows in the filter buffer of your
DataWindow/Datastore.
1.
You returned 2 in RetrieveStart, thus not emptying the buffers before
the read. If there had been rows
before the Retrieve, the return value of Retrieve will be the sum of
existing rows and newly read
rows.
2.
HOW TO AVOID PITFALL 10:
Just be aware of the fact. You might want to remove the built-in
filter and do a SetFilter() and Filter in
source code (which is much more readable anyway).
Summary
PowerBuilder has only a few gotchas you need to know in order to write
reliable software. The 10 pitfalls
mentioned above are my personal list of items to be aware of. Keeping
those in mind when programming or
- even better - taking care of them in your base classes will help you
write a robust application.
Do you know of any other issues I didn't mention here? Please drop me
a brief note - if I get enough
Page 5
feedback, I'll compile those issues into another PBDJ article.
www.romu.com

Combining PowerBuilder Help Files

Combining PowerBuilder Help Files

Wouldn't it be nice if all of the help files were combined so you
could hit Shift-F1 on a PB, PFC, or Corporate Extension function and
have it automatically come up?



Well now you can! Just do the following:

* Open pbhlpxx.cnt in Notepad (or any other text editor) and add
the following lines immediately after :Title PowerBuilder Help (the
second line):
:Index PFC Help=pbpfcxx.hlp
:Index Corporate Extension Help=CorpExtn.hlp
:Link pbpfcxx.hlp
:Link CorpExtn.hlp
:Include pbpfcxx.cnt
:Include CorpExtn.cnt
* Save and close the file
* Make sure all of the *.hlp files you have included reside in the
Help folder of your PowerBuilder installation

Using Windows Server 2008 as a SUPER workstation OS

Windows Server 2008 is the best OS to be released till date from Microsoft's stable. And the moment I got hold of the RTM build I could not resist installing it on my workstation. Due to the nature of my work I always prefer running a Server OS on my main workstation... I have been running Windows 2003 disguised as XP (with all the themes and stuff) all these days.

So here is my tale of how I went about setting up Windows Server 2008 to look and fell like its desktop counterpart Windows Vista.

1. Enable Hardware Virtualization

My workstation is a x64 machine with hardware virtualization capabilities. This means I can run Hyper-V on my machine. Even if your machine's hardware supports virtualization it is most likely not going to be enabled by default. You have to enable it via your BIOS setup.

2. Install the latest Graphics and Audio drivers

Being a server OS Windows 2008 carries with it basic graphics and audio drivers. To utilize the full strength of your hardware ensure you install the latest drivers for both graphics and audio hardware. Only with the proper graphics drivers will you be able to enable the "Aero" experience on Windows 2008.

3. Desktop Experience Feature

The Desktop Experience Feature enables a bunch of stuff that is by default present on a desktop OS. Most importantly it includes Themes, Windows Media player and the Aero related features. You will have to enable it form the Server Manager. The "Turn Windows features on or off" / "Add remove windows components" has all been rolled into the Server Manager now.

Server Manager > Features > Desktop Experience

Installing the Desktop Experience feature does not enable them. You have to manually set them up.

4. Themes

To enable Themes you will basically have to enable the Themes Service. Again being a server OS it is not enabled by default.

Services.MSC > Themes

Set the start up type to Automatic

Enabling the Aero Theme.

For this go to Control Panel > Personalization >Theme and select Windows Aero

5. Search

Search is also disabled by default on Windows 2008. Searching is important for me as I use it a lot to find my emails. To enable search you will have to add the File Services Role via Server Manager.

Server Manager > Roles > File Services > Windows Search

Outlook relies on this search service.

6. Disable Shutdown Event Tracker

Since I am using it as a workstation I do not want to keep a track of all the Shutdowns. The Shutdown Event Tracker is the pop up that you get asking you for a shutdown reason. To disable it

Open mmc.msc

Add the Group Policy snap-in

Under Administrative Templates expand System

Set Display Shutdown Event Tracer to Disabled

7. Audio

For audio you need to enable the Windows Audio service. You do this by setting the startup type to Automatic.

Services.msc > Windows Audio

Ensure you have proper drivers for your audio hardware... for me the default driver was not enabling the headphones ... it started working fine after I got the proper driver.

8. SuperFetch

As a workstation, enabling SupertFetch will give you that additional bit of responsiveness. The SuperFetch services is disabled by default and when you try to enable it you will most likely get an error message "The operating system is not presently configured to run this application"

You will have to make two registry changes to enable this service. I basically copied them over from my Vista machine.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\PrefetchParameters

EnablePrefetcher DWORD 3

EnableSuperfetch DWORD 3

9. Get a codec pack.

For media hungry buffs download a codec pack. This will ensure you can play all media files.

10. Enable Hyper-V

With Hyper-V you can run virtual machines on your workstation. This is useful if you want to run your tests on older OS versions. Enabling Hyper-V is easy

Server Manager > Roles > Hyper-V

Remember you need a Hyper-V enabled Windows 2008 licence and also your hardware has to support virtualization.

Also If you are using an existing VHD it may ask you to re-Activate Windows as it detected hardware changes.

One good thing about Windows Server 2008 is that it no longer asks for the i386 folder like Windows 2003 while you enable features.

Using Windows Server 2008 as a SUPER workstation OS ... Cont'd

Here are a couple of things I missed in my previous post

11. Processor Scheduling

As pointed out in a comment on my previous post; On Windows Server 2008 background services are given preference over interactive programs. You can change this behavior by

Control Panel > System and Maintenance > System > Advanced System Settings > Advanced > Performance > Settings > Advanced > Processor Scheduling

Setting this to Programs will make foreground programs more responsive.

12. Visual Effects

One thing you will notice on Windows Server 2008 is that by default you will not see Preview Thumbnails in your Documents / Music / Video folders. This has to be enabled explicitly.

Control Panel > System and Maintenance > System > Advanced System Settings > Advanced > Performance > Settings > Visual Effects

Based on your preference you can tweak these settings.

13. Power Options

Do your bit for a Green World! The Balanced (default) power plan on Windows Server 2008 does not turn off hard disks by default. On Vista hard disks are turned off after 20 mins. You can change this by

Control Panel > Hardware and Sound > Power Options > Change plan settings

It does take a bit to kick start the hard disks when you resume work but that's a sacrifice worth making for a greener world :).

14. IE Enhanced Security

IE Enhanced Security Configuration has been moved from Add Remove Windows Components (on Windows 2003) to the Server Manager on Windows Server 2008.

Server Manager > Security Information > Configure IE ESC

You now have a choice to disable it only for Administrators.

And to end with a couple of clarifications

* Why am I recommending Windows Server 2008 over Windows Vista ?

I am not!

* How to get Sidebar / Media center on Windows Server 2008?

My honest opinion would be to look for alternatives.

* Will hardware problems go away moving to Windows Server 2008?

Not likely. One of the biggest complaints against Vista was hardware issues. Without proper drivers from your hardware vendors your ride on Windows Server 2008 is again going to be bumpy. For me all Vista compatible drivers worked fine with Server 2008 and I believe they should work for you as well.

* Will all software work on Windows Server 2008?

Most will but some setups detect Windows Server 2008 as a server OS and may not install. The compatibility mode does not have a Vista option only XP / Windows 2003 and other legacy OS.

C# class initialization order

1) Derived static constructor
2) Derived Instance members constructor
3) Base static constructor
4) Base Instance consctructor (members too)
5) Derived Instance constructor

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats

Users of the Microsoft Office XP and 2003 programs Word, Excel, or PowerPoint—please install all High-Priority updates from Microsoft Update before downloading the Compatibility Pack.

By installing the Compatibility Pack in addition to Microsoft Office 2000, Office XP, or Office 2003, you will be able to open, edit, and save files using the file formats new to Word, Excel, and PowerPoint 2007. The Compatibility Pack can also be used in conjunction with the Microsoft Office Word Viewer 2003, Excel Viewer 2003, and PowerPoint Viewer 2003 to view files saved in these new formats. For more information about the Compatibility Pack, see Knowledge Base article 924074.

Note: If you use Microsoft Word 2000 or Microsoft Word 2002 to read or write documents containing complex scripts, please see http://support.microsoft.com/kb/925451 for information to enable Word 2007 documents to be displayed correctly in your version of Word.

All you want to know about ViewState and why it can be so bad

Keep ViewState out of Page for Performance Enhancement Redux

Death By ViewState - Bad perf, high memory usage and high CPU in GC - Death By ViewState

TRULY Understanding ViewState

Understanding ASP.NET View State

Resize Thumbnails in Windows Explorer

Create a DWORD key named ThumbnailSize under MyComputer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Explorer

Give it any value such as 128, 256, etc.

Article : If any of you out there like to use the thumbnail view, especially for browsing through photos and images, it can become a bit of a drain on your system. It is possible to lower the thumbnail size and quality by editing the following registry keys.

Open the registry and navigate to :

HKEY_CURRENT_USER\ Software\ Microsoft \ Windows\ CurrentVersion\ Explorer

Create a new DWORD value called ThumbnailSize, and set the value between 32 and 256.

And/or create another DWORD value called ThumbnailQuality, and set the value between 50 and 100.
Key Details :

USER Key:
[HKEY_CURRENT_USER\ Software\ Microsoft \ Windows\ CurrentVersion\ Explorer]
Value Name: ThumbnailSize
Data Type: REG_DWORD (DWORD Value)
Data Value: 32 - 256

USER Key:
[HKEY_CURRENT_USER\ Software\ Microsoft \ Windows\ CurrentVersion\ Explorer]
Value Name: ThumbnailQuality
Data Type: REG_DWORD (DWORD Value)
Data Value: 50 - 100

Create Formatted Text Documents for PSP

Create Formatted Text Documents for PSP


I did some research and thought I would post my results for anyone with a hankering to put formatted text and images right onto a PSP. This will only work with PCs since it focuses on using Paperless Printer to make your JPGs. I used Open office (www.openoffice.org) to create the test documents. You can use any word processor or page layout app you want.

Here is Paperless Printer. Free for non commercial work.
http://www.rarefind.com/paperlessprinter/downloads/paperlessprinter_3.0_setup.exe

To setup a document to be the right dimensions to print to Paperless Pritner is pretty easy and is great for getting pur text documents into the PSP as jpegs.

• Paperless Printer uses and odd pixel to inch conversion(98.159...pixels per inch). So these settings won't work with Acrobat (72ppi) or other software.
• Page size should be set to 4.89x2.81.
• Set all margins to 0. This allows tables or rules to reach the edges of the screen if you want them to.
• Text and other elements just change the indent on both sides to move them in 1/8th of an inch. This gives a little breathing room so the text doesn't touch the edge of the screen.
• The two fonts that work best on computer screens are Georgia and Verdana. They were originally designed for screen display and are quite readable on the PSP as well, you can, of course, use any font you like. Font size should be 10-14 depending on how good your eyes are. I found 12 to be more than enough and verdana and georgia are readable at 10 points.
• You will need to change the margins on your headers and footers (automatic in Word). Set their margin to 0.01 so it seperates a little from the top.

Once you have this format you can use any images, text and formatting and it will all go over to the PSP. The Paperless Printer has a lousy compressor so leave compression 90% or higher.

How to stop Windows Update nagging you to restart after an update

How to stop Windows Update nagging you to restart after an update

After Windows Automatic Updates has downloaded updates to your computer, it may display a dialog that says: "Updating your computer is almost complete. You must restart your computer for the updates to take effect. Do you want to restart your computer now?" If you click "Restart Later", the dialog will appear again after 10 minutes, which is very annoying if you are busy and consider that your computer should not put its needs above your own.
Solution

Windows may need to be restarted after an update has occurred, in order to allow files that are in use to be replaced. If you would prefer to restart the computer at your convenience, and not be nagged to do so, try one of the following solutions:

Method 1

* Click Start, Run and enter the command net stop wuauserv

This will stop the Windows Update service until the next restart of the computer, which will stop the reminders to restart your computer for this update.

Method 2

If you are running Windows XP Professional, the following steps will increase the period between restart reminders to the maximum possible.

* Click Start, Run and enter the command gpedit.msc
* Select Local Computer Policy, Computer Configuration, Administrative Templates, Windows Components, Windows Update
* Double-click Re-prompt for restart with scheduled installations
* Change the value to 1440
* Close the Group Policy Editor
* Click Start, Run and enter the command gpupdate /force

This will stop the repeated reminders for this and all future Windows updates.

If you have Windows XP Home instead of Windows XP Pro, you will probably get the following message:

Windows cannot find 'gpedit.msc'. Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search.

GPEDIT doesn't exist on Windows XP Home edition, since it's for Group Policy, a Windows XP Pro feature. However, you can set the key in the group policy area of your registry. Google for the links, or just download and double-click on the .reg file mentioned in this post: http://computer-vet.com/weblog/2005/05/20/windows_automatic_reboots.html

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU
DWORD value "NoAutoRebootWithLoggedOnUsers" set to 1.

Using Microsoft Excel within your ASP.NET application

COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

If you add a reference to Microsoft Excel and then try to use it within your ASP.NET application you may receive the following error.

Server Error in '/excel' Application.

---

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

Example Application

The problem is that by default Microsoft Excel as a COM object can only activated by the following accounts:

• Administrator
• System
• Interactive

When you are running your ASP.Net account on Windows XP your web application is running as the ASPNET account.

The way to resolve this issue is to edit the DCOM configuration settings for the Microsoft Excel Application object.

Configure DCOM

• Go to the Start-Run menu item.
• Type in "DCOMCNFG" and hit enter.
• This should load the "Component Services" MMC (you can also load from Administrative Tools - Component Services"
• Expand "Component Services"
• Expand "Computers"
• Expand "My Computer"
• Select the "DCOM Config" item
• Select the "Microsoft Excel Application" item.
• Right click and select Properties
• Select the Security Tab and you should see the following:
  
  
  
  
• Under "Launch and Activation Permissions" select the "Customize" option.
• Click the "Edit" button
  
  

  Windows XP		Windows 2003 Server

  
  
  
• Click the "Add" button to add a new account to the list.
• On the dialog that is displayed click the Locations button
  
  (this is because by default your domain will be selected and we need a local account)
  
  In this dialog scroll the list to the top (sometimes the first item is not visible) but scroll to the top and select the first item which is your computer name. In the list below "CCROWE" is the name of my computer.
  
  
  
  
• Click the OK button
• On the dialog that is displayed enter "ASPNET" as the account name (make sure location is set to the name of the computer that IIS is on) on Windows XP or if you are running on Windows 2003 Server you must enter the account that the Application Pool is running as, by default "Network Service"
  
  

  Windows XP		Windows 2003 Server
  Note: A quicker way on Windows XP is to just enter the computer name and the account so in my case that would be: ccrowe\ASPNET

  
  
• Click the OK button
  
• Now make sure you select the following options for the "ASP.NET Machine Account" or the account that is the application pool identity ( by default Network Service)
  
  • Local Launch : Allow
  • Remote Launch : [blank]
  • Local Activation : Allow
  • Remote Activation : [blank]

  These settings can be seen below:

• Windows XP		Windows 2003 Server

• Click the OK button and test your web application again and it should work fine.

Note: Remember if you are running on Windows 2003 Server you must use the application pool identity as the account and not the ASPNET account.

posted on Thursday, March 02, 2006 4:18 AM | Filed Under [ ASP.Net c# IIS ]

Capitalize Initial in C#

private static string CapitalizeInitial(string strValue)
{
if (strValue.Length == 0)
return strValue;

return String.Concat( strValue.Substring( 0, 1 ).ToUpper(),
strValue.Substring( 1 ) );
}

Converting Excel dates (numeric) to regular dates

When importing dates from Excel, it is common to obtain a number instead of a date. That is the number of days after 1/1/1900.

private static string ConvertExcelDate(string strDate)
{
int iDate = 0;
if (! int.TryParse(strDate, out iDate))
return "";
DateTime dtDate = new DateTime(1900, 1, 1);
dtDate = dtDate.AddDays(iDate - 2);
return dtDate.ToShortDateString();
}

101 Design Patterns & Tips for Developers

101 Design Patterns & Tips for Developers

The file web.sitemap required by XmlSiteMapProvider does not exist

The file web.sitemap required by XmlSiteMapProvider does not exist

I used a custom XMLSiteMapProvider where in i have defined a web.sitemap file with urls.
The application worked fine in the test environment and for administrators but errored out for other users.

The actual error was that the normal anonymous user didn't have enough permission to access the web.sitemap file - in fact it needed permission for the whole directory.

It seems IIS still has some issues with directory/file permissions and the account used for anonymous access (e.g.: IUSR_MACHINENAME).

How to email using TELNET

TELNET mail.plano1.tx.home.com 25
HELO test (127.0.0.2)
MAIL FROM:billgates@richguy.com
RCPT TO:fulano@sicrano.com
DATA
bla-bla-bla

. (<- enter-dot-enter to finish)

QUIT

Fixing Error when IIS doesn't start

A convenient way to deal with "Unexpected Error 0x8ffe2740"

Ever had the annoying problem of IIS not wanting to start, coughing up the message "Unexpected Error 0x8ffe2740 Occurred."? The reason is that some other application has grabbed port 80. The most common applications doing this are Skype or Trillion. You can try just ending task on them and see if IIS will then start. If you're not running either of those then what could it be? Microsoft's KB article about the subject describes using the third-party utilities TCPView or FPort. But I think an easier way to find the issue is to simply drop out to a command prompt and run:

netstat -aon

Scroll up to the top part with TCP listings and you'll see something like this:

Then under "Local Address" look for 0.0.0.0:80. This is your entry, and a the far right is the PID you're after. With that number you can then run Task Manager, select the Process tab, and add the PID column in the display from View / Select Columns:

When you've found the process with the same PID, end task on it. The universe should then return to a state of perfect harmony. (Or at least your IIS will be able to start at that point!)

Another more geeky option to kill the offending app is to compile and run this line of .NET code:

System.Diagnostics.Process.GetProcessById( PID# ).Kill()

(Of course putting in the proper PID where indicated there.)

With everything that the Process class does, you could actually write your own highly effective Task Manager application in .NET if you really wanted to!

Utilities to replace SpinRite

The UBCD (http://www.ultimatebootcd.com/) has several (free) utilities for scanning disks for physical failures.

For data recovery tools: Undelete Plus (http://www.undelete-plus.com/) and TestDisk (http://www.cgsecurity.org/wiki/TestDisk).