IT Toolshed

SQL Server 2005 CTE (Common Table Expression)

2008-06-04T01:05:00.001-05:00

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

2008-06-04T01:04:00.001-05:00

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

2008-05-23T16:58:00.001-05:00

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

2008-05-23T16:56:00.001-05:00

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

2008-05-22T03:01:00.000-05:00

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

2008-05-11T12:03:00.002-05:00

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

2008-04-12T08:24:00.000-05:00

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

2008-04-11T13:40:00.005-05:00

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

2008-04-06T11:04:00.004-05:00

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

2008-03-17T22:36:00.001-05:00

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

2008-02-28T15:14:00.001-06:00

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

2008-02-04T10:49:00.000-06:00

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#

2008-02-01T11:48:00.000-06:00

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

2008-02-01T11:46:00.000-06:00

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

2008-02-01T10:12:00.000-06:00

101 Design Patterns & Tips for Developers

The file web.sitemap required by XmlSiteMapProvider does not exist

2008-01-25T15:26:00.000-06:00

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

2008-01-14T12:35:00.000-06:00

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

2008-01-13T14:45:00.000-06:00

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

2008-01-13T09:34:00.000-06:00

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

Asynchronous Web Services with MSMQ

2007-12-24T08:36:00.000-06:00

Learn how to overcome a common scalability limitation of Web Services.
Based on an article by Josh Lane

Many of the first Web Services you create are likely to be synchronous, where processing for each service invocation is handled at the time of the invocation. However, Web Services of this sort can have scalability limitations. You can overcome these issues by using Microsoft Message Queue (MSMQ) with Web Services.

Imagine you've decided to implement a help-desk-request logging system for a large IT department as a Web Service. This web service defines a single public SubmitRequest() method. The user request is processed immediately, regardless of current server load or database availability. This can limit scalability and robustness.

Public Function SubmitRequest(ByVal request As HelpDeskRequest)
Try
 Dim oldWriter as New OldSystemWriter
 oldWriter.SubmitRequest(request)
 dim newWriter as New NewSystemWriter
 newWriter.SubmitRequest(request)
 ContextUtil.SetComplete()
Catch ex As Exception
 EventLog.WriterEntry("HelpDeskRequestProcessor", _
   ex.Message,EventLogEntryType.Error )
 ContextUtil.SetAbort()
End Try

You can solve scalability problems by changing the web service from a synchronous request model to an asynchronous request model, introducing an intermediate component into the request-logging architecture that can take requests rapidly and guarantee their eventual delivery.

Such an asynchronous system can be built using MSMQ, a middleware subsystem that comes pre-installed on all Windows 2000 and upwards. It provides a means of storing messages of arbitrary content for forwarding or retrieval in operating-system-level structures called queues. MSMQ queues are Distributed Transaction Coordinator (DTC)-aware resource managers, meaning that send and receive operations against a queue can operate within a COM+ transaction. You can access the full complement of MSMQ services from the System.Messaging API in .NET.

First, you need to build a component that's triggered when the request message queue on the server receives a help request. This component removes the message from the queue and performs the processing.

The trigger calls this method each time a new message arrives in the
target queue. Note that the queue path is passed in as a parameter. Also
note that all work is performed inside a COM+ transaction, so the MSMQ
receive operation and both SQL Server write operations are treated as
a single atomic operation.

Public Sub ProcessMessage(ByVal path As String) _
Implements IMsgHandler.ProcessMessage
Dim mq As MessageQueue
Dim m As Message
Dim oldWriter as IRequestWriter
Dim newWriter as IRequestWriter
Try
 mq = New MessageQueue( path )
 m = mq.Receive()
 m.Formatter = New XmlMessageFormatter( _
   New Type() { GetType( _
   VSM_ShareTypes.HelpDeskRequest ) } )
 oldWriter = New OldSystemRequestWriter()
 oldWriter.WriteRequest( m.Body )
 newWriter = New NewSystemRequestWriter()
 newWriter.WriteRequest( m.Body )
  ContextUtil.SetComplete()
Catch ex As Exception
 EventLog.WriterEntry("VSM_RequestHandler", _
   ex.Message,EventLogEntryType.Error )
 ContextUtil.SetAbort()
Finally
 mq.Dispose()
End Try
End Sub

Create an MSMQ Trigger Rule and register the COM+ components using regsvcs.exe. Trigger rules allow your components to be invoked automatically as a result of messages arriving at the target queue. You can use MSMQ triggers to process queue messages instead of implementing a traditional queue listenener; be sure you understand the usage semantics of each option before choosing one.

New Rule (set up in the Computer Management Console):
Invoke COM component
Component ProgID:  VSM_MessageHandler.HelpDeskRequestHandler
Method Name: ProcessRequest

Here the public web service is responsible only for receiving incoming requests and writing them to the target request queue. This simple, fast operation minimizes the work performed by the web server and allows you to maximize precious web server resources and minimize client response times. Note the use of a local transaction instead of a COM+ transaction (which would be overkill since it involves only a single resource manager - the queue).


Public Function SubmitOrder( ByVal order As HelpDeskRequest)
Dim mqt As New MessageQueueTransaction()
Dim mq As MessageQueue
Try
 mq = New MessageQueue(m_QueuePath)
 mqt.Begin()
 mq.Send(order)
 mqt.Commit()
Catch ex As Exception
 EventLog.WriteEntry( "VSM_RequestTaker", _
  ex.Message, EventLogEntryType.Error )
 mqt.Abort()
Finally
 mq.Dispose()
End Try
End Function

Also see Using MSMQ message triggers (PDF file)

Add Reports and Charts to Web Pages

2007-12-24T01:05:00.000-06:00

Add Reports and Charts to Web Pages

The ASP.NET 2.0 version of Visual Studio 2005's ReportViewer control and its Report Designer let Web page developers quickly embed, format, and export interactive reports and charts without running a SQL Server Report Server.

March 2006 · by Roger Jennings

It's convenient to be able to embed reports and charts in Windows forms, but you often need to make these same reports and charts available to those who must view them from the Internet or an intranet.

I described how to build Windows form (smart client) applications that use VS 2005's new ReportViewer controls to incorporate embedded reports and charts in a recent issue (see "Build Reports More Easily," VSM November 2005). Now I'll show you how to accomplish the next step, which is using the Report Designer and ASP.NET 2.0 ReportViewer server control to create Web pages that contain reports and charts. I'll also describe how to repurpose the Report Definition Language Client (RDLC) XML files you created for smart client projects in the earlier article for use with the ASP.NET 2.0 ReportViewer server control. You'll also learn how to take advantage of XML InfoSet documents as the data source for smart client and Web-based projects, and overcome fatal "Failed to enable constraints" exceptions in Web reports. Along the way, I'll walk you through the important code-behind, design, and rendering differences between Windows and Web forms reports.

ReportViewer controls let you dispense with SQL Server Reporting Services (SSRS), which now requires a licensed edition of SQL Server 2005. The current version of SQL Server 2005 Express (SSX) doesn't support SSRS, but Microsoft promises a future SSX upgrade with a limited SSRS feature set. Unlike SSRS, which requires SQL Server 2005 or SSX, ReportViewer controls are database agnostic. You can use any database engine that has a native .NET, OLE DB, or ODBC driver that meets ADO.NET 2.0's requirements. You also can create a data source from an XML data document or instances of custom business objects.

This article's downloadable VB 2005 sample code includes Windows and Web form examples of tabular and crosstab reports, and charts that you generate from the AdventureWorks 2005 and Northwind sample databases. You must run the Transact-SQL scripts to install these two databases on your local instance of SQL Server 2005 or SSX to run the sample projects. If you use SSX, change the SQL Server connection string's localhost to localhost\SQLEXPRESS. The sample code installs by default in the current drive's \ReportViewer\ReportSmartClient and \ReportViewer\ReportWebSite folders. If you're running Visual Web Developer Express, install the Report Builder and ReportViewer (SQLServer2005_ReportAddIn.msi) add-ins before running the sample source code (see Additional Resources for links to the required files.)

Designing and embedding a tabular report in a Web form requires four basic steps: embed the ReportViewer server control; specify a report data source with the Table Adapter Configuration Wizard; add and populate a Table control in the Report Designer; and format the report Web page. Together, these steps let you create a simple report Web page. For the sake of simplicity, the example in this article uses the Northwind Employees table.

Embed the ReportViewer
Begin by opening a new file-system Web site and naming it ReportWebTest. Next, open Default.aspx in Design mode and drag a ReportViewer server control from the toolbox's Data category to the form to generate a ReportViewer1 server control (see Figure 1).

Now open ReportViewer1's Report Viewer Tasks smart tag, click on the Design a New Report link to add an empty Report1.rdlc file to the project, and open the Web Site Data Sources window. Next, click on the window's Add New Data Source link to generate a DataSet1.xsd file with an empty TableAdapter and start the Table Adapter Configuration Wizard. Add a new connection to localhost or localhost\SQLEXPRESS with Windows authentication and Northwind as the default database, click on the Next button, and save the connection with its default name, NorthwindConnectionString. Click on the Next button again, accept the default Use SQL Statements option, and click on the Next button to open the Enter a SQL Statement dialog.

Use this T-SQL statement to generate a simple employee roster report:

SELECT EmployeeID, FirstName, LastName, Title,
 Country, Extension, Notes FROM Employees

Click on Advanced Options to open the dialog of the same name, clear the Generate Insert, Update and Delete Statements check box, and click on OK. Note that you don't need DataSet update capability for reports. Click on the Next button, accept the default Fill and Get Data Method Names, clear the Create Methods to Send Updates Directly to the Database check box, and click on the Next and Finish buttons to add DataSet1 to the Website Data Sources window—also called the Field List. You don't need code to populate the DataTable when the page loads.

You're now ready to add and populate a Table control in the Report Designer. Drag a Table control from the Toolbox's Report Items category to the upper left area of Report1.rdlc's empty Body region. The default Table (table1) includes three rows of text boxes—Header, Detail and Footer—and three empty columns. Drag the EmployeeID, LastName, and FirstName field icons from the Website Data Sources window to the text boxes in columns 1, 2 and 3 of the Detail (center) row. Dropping a field in the Detail row adds the formatted field name to the Header text box and an =Fields!FieldName.Value expression to the Detail text box. Right-click on the right-most column header and select Insert Column to the Right to add columns for the Title, Country, and Extension fields (see Figure 2) .

The report width increases automatically as you add columns. (You add the ntext Notes column after formatting the report and page in the next step, because the width of the Notes column depends on available page width.) Expand the width of ReportViewer1 to about 750px, then press F5 to build and load the page in the ASP.NET Development Server (Cassini). Accept the Modify the Web.config File to Enable Debugging option when the Debugging Not Enabled message box opens, and click on the OK button. If the page is empty, close Internet Explorer (IE) 6.0, return to page Design mode, open the Report Viewer Tasks smart tag, and select Report1.rdlc from the Choose Report list. Next, click on the Choose Data Sources link to open the eponymous dialog and verify that the Data Source Instance is ObjectDataSource1 (see Figure 3). Finally, re-run the report. You'll use these features when you repurpose RDLC files from Windows forms later in the article.

Format the Final Report
The fourth and final step for embedding a tabular report in a Web form requires formatting the report to optimize consumption of browser real estate for your users' lowest display resolution or browser-window size, which this article's examples and the downloadable sample projects assume to be 800 by 600 pixels. Start by changing ReportViewer1's width property value from 750px to 100%. Unfortunately, you can't set the ReportViewer control's height in percent and specify XHTML 1.0 standards-mode processing. If you set 100% as the height property value, rendering the report in XHTML 1.0 standards-mode displays only the toolstrip; to view the report with quirks-mode rendering, remove this line from the Default.aspx source code:

Be forewarned: There's no guarantee that IE 7.0 will render quirks-mode reports correctly.

Select table1 in the Report Designer, click on the plain button in the Table's upper left corner to display sizing handles, and drag table1 to the upper left corner of the Body section. Click on the Header button to select the Header row, and click on the toolbar's Bold and Underline buttons to emphasize text. Change the Employee ID header to ID, and reduce the column width to 0.25 in.; reduce the Last Name and First Name column width to 0.88 in.; change the Title column width to 1.75 in.; change the Country and Extension headers to Ctry and Ext and the column widths to 0.38 in. Add a column and drag the Notes field to its Details text box; set the column width to 3.13 in. and drag the right edge of the Body section to the right edge of table1. Next, replace Untitled Page with Northwind Traders Employee Roster in the Default.aspx Source window, and press F5 to build and run the project to test your work so far.

You can delete the report footer row if you want, but that's not required. If you want to add a page header, page footer, or both, simply choose Report | Page Header or Report | Page Footer and add text or expressions. Most Web reports are single-page, so you can simplify the toolstrip by setting ReportViewer1's ShowPageNavigationControls property to False. You can add green-striping to the report by selecting Report1.rdlc's Details row (TableRow2), opening its Properties window, selecting the BackgroundColor property to open the color picker, clicking on the Web tab, and selecting to open the Edit Expression dialog. Now type this VBA expression in the text box (see Table 1):

=IIf(RowNumber(Nothing) Mod 2,
 "PaleGreen", "White")

Click on OK, then press F5 to build and render the page in IE (see Figure 4). Typing a search term in the text box or selecting a report export format enables the associated link buttons (see Figure 5). Unlike the Windows toolstrip, the Web form toolstrip has no print-related buttons. You set margins and print the report by executing IE's File | Page Setup, File | Print Preview, and File | Print commands.

Clone Web Report Pages
Report-oriented Web sites usually have multiple pages, enabling users to navigate easily between related reports without moving to another URL. This design model is similar to a Windows form project, such as ReportSmartClient.sln, that uses pages of a Tab control to display different reports (see Figure 6). Cloning pages for multiple reports saves substantial developer/designer time and effort. After you create a workable starting page, such as NWEmployees.aspx, you can copy and paste the page to the project, and then rename the cloned pages. For example, four of the five pages of the sample ReportWebSite project—NWOrders1997.aspx, NWOrdersChart.aspx, AWEmployees.aspx and AWCategories.aspx—began as clones of NWEmployees.aspx.

You can copy and paste RDLC files for the original and cloned pages from a corresponding Windows form project. The NWOrders1997Win.rdlc, NWOrdersChartWin.rdlc, AWEmployeesWin.rdlc, and AWCategoriesWin.rdlc files originate in the ReportSmartClient.sln project. Data source instances for Web-based reports differ considerably from their Windows form counterparts, so you must recreate the ObjectDataSource for each report page except AWCategories.aspx. However, you can create the required ObjectDataSource by copying the DataSetName.xsd file from the Windows form project to the Web site's App_Code folder.

Recreating an ObjectDataSource for a cloned page is simple. Open the ReportViewer Tasks smart tag and select the appropriate RDLC file from the Choose Report list. Most cloned pages have an existing ObjectDataSource1 instance, in which case selecting the new RDLC file adds an ObjectDataSource2 instance. Delete the added instance, open ObjectDataSource1's Tasks smart tag, click on the Configure Data Source link, and verify or change the object name in the Object Data Source Wizard's first dialog. Complete the Wizard's steps, click on the ReportViewer Tasks smart tag's Choose Data Sources link, and verify or change the Data Source Instance to ObjectDataSource1. This technique applies to conventional report pages, such as the ReportWebSite's NWEmployees.aspx, NWOrders1997.aspx, and NWOrdersChart.aspx.

The AWCategories.aspx page, like the AWCategories tab page of the Windows form project, requires code-behind to create and populate a runtime DataSet's DataTable from an XML data document (see Listing 1). The code to create a runtime, untyped DataSet for the Page_Load event handler is almost identical to that for the Form_Load event handler (see Listings 2 and 3). AWCategories.aspx illustrates a complex page that includes a background image, a complex matrix report that resembles an Excel PivotTable, and multiple detail charts. Unlike the corresponding Windows form report, the detail charts are bitmaps, so zooming the charts to 75% or smaller makes the charts impossible to read.

Use an XML InfoSet Document as a Web ReportViewer Data Source

Listing 1. ReportViewer controls require a typed or untyped DataSet with a DataTable to serve as the report's data source. This code behind the AWCategories.aspx page loads Listing 3's XML data into an untyped, runtime DataSet that's assigned to the ReportViewer's LocalReportDataSources(0) object. The bold attribute emphasizes the modifications required to the corresponding Windows form code. Note that you can't use the "|DataDirectory|" connection string macro, which points to the project's \App_Data folder, in file path statements.

Imports System
Imports System.Data
Imports Microsoft.Reporting.WebForms


Partial Class AWCategories
 Inherits System.Web.UI.Page


 Protected Sub Page_Load(ByVal sender As Object,
   ByVal e As System.EventArgs) Handles Me.Load
   If Page.IsPostBack Then
     Return
   End If
   With Me.ReportViewer1
     'Untyped DataSet for the report's DataSource
     Dim dsAwCats As New DataSet
     'Read the XML data into the DataSet
     Dim strPath As String = _
       "\ReportViewer\ReportWebSite\App_Data\"
     dsAwCats.ReadXml(strPath + "AWCategories.xml")
     'Assign the DataTable as the DataSource
     .LocalReport.DataSources.Add(New + _
       ReportDataSource("Sales",
       dsAwCats.Tables(0)))
     'Refresh the report with the new data
     .DataBind()
   End With
 End Sub
End Class

AWEmployees.aspx demonstrates an undocumented problem with data sources that execute SELECT queries. They generate this nasty exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." You can use the DataTable.GetErrors( ) method to find out what rows and columns have errors, but you might find that eliminating the errors is impossible. Windows forms let you bypass this problem by adding an AdventureWorksDataset.EnforceConstraints = False statement before the Me.AwEmployeesTableAdapter.Fill(Me.AdventureWorksDataset.AWEmployees) method call. Unfortunately, this simple approach doesn't work for Web form reports because their DataSets don't expose the EnforceConstraints property. The upshot of this omission is that you need to create a new AdventureWorksDataset instance and set its EnforceConstraints property value to False, and then generate and fill a new TableAdapter instance (see Listing 4). Issue a ReportViewer1.LocalReport.DataSources.Clear( ) instruction before adding the new DataSet instance with the ReportViewer1.LocalReport.DataSources.Add( ) method. If you don't clear the DataSources, autogenerated C# DataSet designer code will raise the error when processing the required, but unusable, ObjectDataSet.

Eliminate Web Forms' "Failed to enable constraints" Exceptions

Listing 4. Some SELECT queries, such as that for the AWEmployees.rdlc report's data source, throw this exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Windows forms let you ignore the exception by adding a DataSetName.EnforceConstraints = False instruction before you invoke the TableAdapterName.Fill( ) method. However, setting the EnforceConstraints property value in a Web form requires creating a new DataSet instance, which requires creating and filling a new TableAdapter.

Imports System
Imports System.Data
Imports Microsoft.Reporting.WebForms


Partial Class _Default
 Inherits System.Web.UI.Page


 Protected Sub Page_Load(ByVal sender As Object,
   ByVal e As System.EventArgs) Handles Me.Load
   If Page.IsPostBack Then
     Return
   End If
   Dim dsAdvWorks As New AdventureWorksDataSet
   Dim taEmployees As New
     AdventureWorksDataSetTableAdapters. _
     AWEmployeesTableAdapter
   dsAdvWorks.EnforceConstraints = False
   taEmployees.Fill(dsAdvWorks.AWEmployees)
   With Me.ReportViewer1
     'Remove ObjectDataSource1
     .LocalReport.DataSources.Clear()
     .LocalReport.DataSources.Add(New
     ReportDataSource("AdventureWorks"+_
        "Dataset_AWEmployees",
        dsAdvWorks.Tables(0)))
     'Refresh the report with the new data
     .DataBind()
   End With
 End Sub
End Class

The Report Designer and ReportViewer combination lets you quickly integrate tabular and crosstab reports, as well as charts and graphs, in your .NET 2.0 Windows and Web forms projects. Migrating Windows form RDLC files to corresponding Web-based reports is easy and most cloned Web reports don't require adding code. A limitation of Web reports is the poor readability of zoomed charts and graphs. But the primary ReportViewer drawback for .NET developers is the fact that Microsoft provides even less documentation for Web-based reports than Windows-based reports. Apart from these drawbacks, the .NET 2.0 ReportViewer reports are ready for primetime in Windows and Web forms.

About the Author

Roger Jennings is an independent architect/database developer and writer with more than 1.25 million English copies printed. Roger's latest .NET book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He''s also a Visual Studio Magazine contributing editor and online columnist, and manages the OakLeaf Systems blog. His Code of Federal Regulations Web services won Microsoft''s 2002 .NET Best Horizontal Solution Award. Reach him at roger_jennings@compuserve.com.

C# - MultiDropdownFilterService

2007-12-19T14:39:00.000-06:00

This class implements a filtering plug-in service that will allow the user to filter one or more SqlDataSources, GridViews and/or FormViews by the value selected in one of more DropDownLists.

Full text here.

VB.NET and C# Comparison

2007-12-19T13:52:00.001-06:00

This is a quick reference guide to highlight some key syntactical differences between VB.NET (version 2) and C#. Very useful!

Check out the Java (J2SE 5.0) and C# comparison too!

Two articles on datetimes

2007-12-18T21:46:00.000-06:00

Two articles on datetimes

The ultimate guide to the datetime datatypes

Frequently Asked Questions about Calendars, by Claus Tøndering

ASP.NET Performance Tuning Tips

2007-12-18T21:39:00.000-06:00

ASP.NET Performance Tuning Tips

Any programming model has its common performance pitfalls, and ASP.NET is no exception. This section describes some of the ways in which you can avoid performance bottlenecks in your code.

Disable Session State when not in use: Not all applications or pages require per-user session state. If it is not required, disable it completely. This is easily accomplished using a page-level directive, such as the following:

<%@ Page EnableSessionState="false" %>

Note: If a page requires access to session variables but does not create or modify them, set the value of the directive to ReadOnly. Session State can also be disabled for XML Web service methods. See Using Objects and Intrinsics in the XML Web services section.

Choose your Session State provider carefully: ASP.NET provides three distinct ways to store session data for your application: in-process session state, out-of-process session state as a Windows Service, and out-of-process session state in a SQL database. Each has its advantages, but in-process session state is by far the fastest solution. If you are only storing small amounts of volatile data in session state you should use the in-process provider. The out-of-process solutions are primarily useful in Web garden and Web farm scenarios or in situations in which data cannot be lost in the event of a server/process restart.

Avoid excessive round trips to the server: The Web Forms page framework is one of the best features of ASP.NET, because it can dramatically reduce the amount of code you need to write to accomplish a task. Programmatic access to page elements using server controls and the postback event handling model are arguably the most time-saving features. However, there are appropriate and inappropriate ways to use these features, and it is important to know when it is appropriate to use them.
An application typically needs to make a round trip to the server only when retrieving data or storing data. Most data manipulations can take place on the client between round trips. For example, validating form entries can often take place on the client before the user submits data. In general, if you do not need to relay information back to the server, then you should not make a round trip to the server.

If you are writing your own server controls, consider having them render client-side code for up-level (ECMAScript-capable) browsers. By employing "smart" controls, you can dramatically reduce the number of unecessary hits to your Web server.

Use Page.IsPostback to avoid extra work on a round trip: If you are handling server control postbacks, you often need to execute different code the first time the page is requested from the code you do use for the round trip when an event is fired. If you check the Page.IsPostBack property, your code can execute conditionally, depending on whether there is an initial request for the page or a responce to a server control event. It might seem obvious to do this, but in practice it is possible to omit this check without changing the behavior of the page. For example:

<script language="C#" runat="server">

public DataSet ds;
...

void Page_Load(Object sender, EventArgs e) {
// ...set up a connection and command here...
if (!Page.IsPostBack) {
String query = "select * from Authors where FirstName like '%JUSTIN%'";
myCommand.Fill(ds, "Authors");
myDataGrid.DataBind();
}
}

void Button_Click(Object sender, EventArgs e) {
String query = "select * from Authors where FirstName like '%BRAD%'";
myCommand.Fill(ds, "Authors");
myDataGrid.DataBind();
}

</script>

<form runat="server">
<sp:datagrid datasource='<%# ds.DefaultView %>' runat="server"/><br>
<sp:button onclick="Button_Click" runat="server"/>
</form>

The Page_Load event executes on every request, so we checked Page.IsPostBack so that the first query does not execute when we process the Button_Click event postback. Note that even without this check our page would behave identically, since the binding from the first query would be overturned by the call to DataBind in the event handler. Keep in mind that it can be easy to overlook this simple performance improvement when you write your pages.

Use server controls sparingly and appropriately: Even though it is extremely easy to use, a server control might not always be the best choice. In many cases, a simple rendering or databinding substitution will accomplish the same thing. For example:

<script language="C#" runat="server">

public String imagePath;
void Page_Load(Object sender, EventArgs e) {
//...retrieve data for imagePath here...
DataBind();
}

</script>

<%-- the span and img server controls are unecessary...--%>
The path to the image is: <span innerhtml='<%# imagePath %>' runat="server"/><br>
<img src='<%# imagePath %>' runat="server"/>

<br><br>

<%-- use databinding to substitute literals instead...--%>
The path to the image is: <%# imagePath %><br>
<img src='<%# imagePath %>' />

<br><br>

<%-- or a simple rendering expression...--%>
The path to the image is: <%= imagePath %><br>
<img src='<%= imagePath %>' />

In this example, a server control is not needed to substitute values into the resulting HTML sent back to the client. There are many other cases where this technique works just fine, even in server control templates. However, if you want to programmatically manipulate the control's properties, handle events from it, or take advantage of its state preservation, then a server control would be appropriate. You should examine your use of server controls and look for code you can optimize.

Avoid excessive server control view state: Automatic state management is a feature that enables server controls to re-populate their values on a round trip without requiring you to write any code. This feature is not free however, since the state of a control is passed to and from the server in a hidden form field. You should be aware of when ViewState is helping you and when it is not. For example, if you are binding a control to data on every round trip (as in the datagrid example in tip #4), then you do not need the control to maintain it's view state, since you will wipe out any re-populated data in any case.
ViewState is enabled for all server controls by default. To disable it, set the EnableViewState property of the control to false, as in the following example:

<sp:datagrid EnableViewState="false" datasource="..." runat="server"/>

You can also turn ViewState off at the page level. This is useful when you do not post back from a page at all, as in the following example:

<%@ Page EnableViewState="false" %>

Note that this attribute is also supported by the User Control directive. To analyze the amount of view state used by the server controls on your page, enable tracing and look at the View State column of the Control Hierarchy table. For more information about the Trace feature and how to enable it, see the Application-level Trace Logging feature.

Use Response.Write for String concatenation: Use the HttpResponse.Write method in your pages or user controls for string concatenation. This method offers buffering and concatenation services that are very efficient. If you are performing extensive concatenation, however, the technique in the following example, using multiple calls to Response.Write, is faster than concatenating a string with a single call to the Response.Write method.

Response.Write("a");
Response.Write(myString);
Response.Write("b");
Response.Write(myObj.ToString());
Response.Write("c");
Response.Write(myString2);
Response.Write("d");

Do not rely on exceptions in your code: Exceptions are very expensive and should rarely occur in your code. You should never use exceptions as a way to control normal program flow. If it is possible to detect in code a condition that would cause an exception, you should do that instead of waiting to catch the exception before handling that condition. Common scenarios include checking for null, assigning to a string that will be parsed into a numeric value, or checking for specific values before applying math operations. For example:

// Consider changing this:

try {
result = 100 / num;
}
catch (Exception e) {
result = 0;
}

// To this:

if (num != 0)
result = 100 / num;
else
result = 0;

Use early binding in Visual Basic or JScript code: One of the advantages of Visual Basic, VBScript, and JScript is their typeless nature. Variables can be created simply by using them and need no explicit type declaration. When assigning from one type to another, conversions are performed automatically, as well. This can be both an advantage and a disadvantage, since late binding is a very expensive convenience in terms of performance.
The Visual Basic language now supports type-safe programming through the use of a special Option Strict compiler directive. For backward compatibility, ASP.NET does not enable Option Strict by default. However, for optimal perfomance, you should enable Option Strict for your pages by using a Strict attribute on the page or Control directive:

<%@ Page Language="VB" Strict="true" %>

<%

Dim B
Dim C As String

' This causes a compiler error:
A = "Hello"

' This causes a compiler error:
B = "World"

' This does not:
C = "!!!!!!"

' But this does:
C = 0

%>

JScript also supports typeless programming, though it offers no compiler directive to force early binding. A variable is late-bound if:
It is declared explicitly as an object.
It is a field of a class with no type declaration.
It is a private function/method member with no explicit type declaration and the type cannot be inferred from its use.
The last distinction is complicated. The JScript compiler optimizes if it can figure out the type, based on how a variable is used. In the following example, the variable A is early-bound but the variable B is late-bound:

var A;
var B;

A = "Hello";
B = "World";
B = 0;

For the best performance, declare your JScript variables as having a type. For example, "var A : String".

Port call-intensive COM components to managed code: The .NET Framework provides a remarkably easy way to interoperate with traditional COM components. The benefit is that you can take advantage of the new platform while preserving your existing code. However, there are some circumstances in which the performance cost of keeping your old components is greater than the cost to migrate your components to managed code. Every situation is unique, and the best way to decide what needs to be changed is to measure site performance. In general, however, the performance impact of COM interoperability is proportional to the number of function calls made or the amount of data marshaled from unmanaged to managed code. A component that requires a high volume of calls to interact with it is called "chatty," due to the number of communications between layers. You should consider porting such components to fully managed code to benefit from the performance gains provided by the .NET platform. Alternatively, you might consider redesigning your component to require fewer calls or to marshal more data at once.

Use SQL stored procedures for data access: Of all the data access methods provided by the .NET Framework, SQL-based data access is the best choice for building scalable web applications with the best performance. When using the managed SQL provider, you can get an additional performance boost by using compiled stored procedures instead of ad hoc queries. For an example of using SQL stored procedures, refer to the Server-Side Data Access section of this tutorial.

Use SqlDataReader for a fast-forward, read-only data cursor: A SqlDataReader object provides a forward, read-only cursor over data retrieved from a SQL database. SqlDataReader is a more performant option than using a DataSet if it can be used for your scenario. Because SqlDataReader supports the IEnumerable interface, you can even bind server controls, as well. For an example of using SqlDataReader, see the Server-Side Data Access section of this tutorial.

Cache data and output wherever possible: The ASP.NET programming model provides a simple mechanism for caching page output or data when it does not need to be dynamically computed for every request. You can design your pages with caching in mind to optimize those places in your application that you expect to have the most traffic. More than any feature of the .NET Framework, the appropriate use of caching can enhance the performance of your site, sometimes by an order of magnitude or more. For more information about how to use caching, see the Cache Services section of this tutorial.

Enable Web gardening for multiprocessor computers: The ASP.NET process model helps enable scalability on multiprocessor machines by distributing the work to several processes, one for each CPU, each with processor affinity set to its CPU. The technique is called Web gardening, and can dramatically improve the performance of some applications. To learn how to enable Web gardening, refer to the Using the Process Model section.

Do not forget to disable Debug mode: The <compilation> section in ASP.NET configuration controls whether an application is compiled in Debug mode, or not. Debug mode degrades performance significantly. Always remember to disable Debug mode before you deploy a production application or measure performance. For more information about Debug mode, refer to the section entitled The SDK Debugger.

A Short History of the Calendar (and Julian dates)

2007-12-18T21:38:00.000-06:00

A Short History of the Calendar
Excerpt from Joe Celko's Data and Databases: Concepts in Practice

The Western world is and has been on a solar calendar. That means a year is defined as one revolution of the earth around the sun. Unfortunately, this revolution is not an even number of days (one solar year = 365.2422 days) and therefore solar calendars drift out of alignment unless corrected.

The Egyptian calendar drifted completely around approximately every 1,461 years and made two complete cycles from its introduction to the time of Julius Caesar. As a result, this calendar was useless for agriculture. The Egyptians relied on the stars to predict the flooding of the Nile. Julius Caesar decreed that the year 708 AUC (ab urbis conditae from the founding of the city of Rome, or 46 BCE) had 445 days in order to realign the calendar with the seasons. Leap years were referred to as bissextile years.

Julius Caesar, on the advice of Sosigenes of Alexandria, also introduced leap years in 708 AUC (they did not exist in solar calendars prior to then). This calendar became known as the Julian calendar. The year 46 BCE was called the Year of Confusion by the Romans.

Over the next several centuries, months were added to the year, and days were added and subtracted from the months by assorted Romans, until the Christians had control over the calendar. The problem with the Julian calendar was that it used a simple four-year leap year cycle. It drifted by approximately 3 days every 400 years and had gotten 10 days out of step with the seasons by 1582.

However, you might want to remember that a Roman calendar without a leap year would have drifted ‘completely around’ slightly more than once between 708 AUC and 2335 AUC (1582 CE). The summer solstice, so important to planting crops, had no relationship to June 21 by 1582, so Pope Gregory took two weeks out of the month of October in 1582 to realign things. (A thousand years before Pope Gregory, the Aztecs and Mayans knew the number of days in a solar year to three decimal places.)

The Gregorian calendar is now properly known as the Common Era calendar to avoid religious references that might confuse non-Christians. What used to be AD and BC are now properly abbreviated as CE and BCE.

The transition from the old Julian calendar to the Gregorian calendar took place at different times in different countries:

The Italian duchies and kingdoms, Catholic parts of Switzerland, Spain, and Portugal (at that time under the same king, Philip II), including their colonies, skipped from 1582 October 4 to 1582 October 15.

France (including its colonies) skipped from 1582 December 9 to 1582 December 20.

Poland skipped from 1582 October 4 to 1582 October 15.

German-Roman Empire (the Habsburgs in Austria): in 1583.

German duchies with Catholic confession: in 1583.

German duchies with Protestant confession skipped from 1700 February 18 to 1700 March 1.

Netherlands: in 1700.

Protestant parts of Switzerland: in 1701.

Denmark and Norway skipped from 1700 February 18 to 1700 March 1.

Sweden and Finland skipped from 1753 February 17 to 1753 March 1, but were one day apart from the old calendar between 1700 and 1712! (that is, 1700 February 28 was followed by 1700 March 1, and 1712 February 29 was followed by 1712 February 30, which was followed by 1712 March 1).

Great Britain and its colonies skipped from 1752 September 2 to 1752 September 14.

Russia and the former Soviet Union skipped from 1918 January 18 to 1918 February 1 (therefore, the October Revolution took place 1917 November 7 in the Gregorian calendar).

The Balkan nations switched between 1918 and 1924.

The Julian Date

If you’re going to use universal dates, think big and use Julian dates. The Julian date is a number set up by astronomers that currently is seven digits long. It ranges from 4713 January 01 BCE through 27,378 CE, which ought to be enough for any database application. The use of the starting date of 4713 January 01 BCE is related to solar and lunar cycles. This format avoids some problems, such as finding the day of the week (take the Julian date modulo 7) and leap year conversions. Durations are calculated by simple subtraction and addition. Finding the phase of the moon, lunar eclipses, solar eclipses, and other astronomical facts is fairly simple algebra which is why astronomers use it. The downside is that existing systems, both legal and computer, would need to be rewritten.

Unneeded Services on Windows XP

2007-12-18T21:37:00.001-06:00

Unneeded Services on Windows XP

Windows & .NET Magazine December 2003 pg. 88
Author(s) Michael Otey

The code-bloat problem seems to get worse with every release of Windows—each new release seems to run slower than the preceding one. In most cases, the problem doesn't stem from the base OS code but from the fact that each new release tends to incorporate more functionality. One key area that continues to expand is services: Windows XP automatically starts 36 services. Few users need all those services, however, and by trimming back unused services, you can make your system run more efficiently.

To disable a service, open the Control Panel Services applet and double-click the service to open its Properties sheet. On the General tab, click the Startup type drop-down box and select Disabled. If you discover that you've lost important functionality, restart the service by resetting its Startup type to Automatic or Manual. Here are 10 XP services that you can consider turning off.

10. Automatic Updates service—Some users depend on Microsoft's Automatic Updates to keep their systems up-to-date and will want to leave this service enabled. Personally, I like to be in control of the updates that are applied to my systems, so I turn off Automatic Updates.

9. Messenger service—The Messenger service sends and receives messages that the Net Send command or the Alerter service has transmitted. If you don't use the Net Send function or receive Alerter messages, you can safely disable this service.

8.TCP/IP NetBIOS Helper service—If you're still running WINS and NetBIOS on your network, you'll want the TCP/IP NetBios Helper service to remain enabled. However, if you run only TCP/IP, you can probably eliminate this service.

7. Wireless Zero Configuration service—As its name suggests, the Wireless Zero Configuration service supports automatic configuration of 802.11 wireless connections. Mobile users of laptops and tablet PCs should probably leave this service active, but networked client systems usually have no need for wireless connections and can safely disable the service.

6. Upload Manager service—The Upload Manager service performs asynchronous file transfers. This service lets your system send Microsoft information that's used to search for drivers for your system. I prefer to explicitly manage the drivers I use, so I disable the Upload Manager service.

5. Task Scheduler service—The Task Scheduler lets your system automatically run programs and scripts at a prescheduled time. Some third-party virus scanners and backup utilities use this service; others install their own scheduling service. To see whether anything on your system uses this service, open the Scheduled Tasks folder in Control Panel. If the folder is empty, you probably can disable Task Scheduler without sacrificing functionality.

4. Error Reporting service—The Error Reporting service contacts Microsoft when applications encounter an error. At first, I thought this service was cool, but after taking the time to send an error report to Microsoft several dozen times for a variety of problems with no visible result, I gave up on this service as more trouble than it's worth.

3. Remote Registry service—The Remote Registry service lets you access and manipulate the registry on other networked systems. This service can be useful on administrative workstations, but it can also be a potentially serious security exposure on end users' network clients. I recommend disabling the Remote Registry service on most client systems.

2. Server service—The Server service provides remote procedure call (RPC) support as well as support for file and print serving. Although this service is necessary on server systems, it can pose a security risk on network clients that don't need to provide file and print serving.

1. Computer Browser service—The Computer Browser service maintains and publishes to network clients a list of computers that are on the network. Although this service is useful on one or two key servers, network clients usually shouldn't run this service.

Good Articles about passing many arguments to a stored procedure

2007-12-18T21:35:00.001-06:00

Good Articles about passing many arguments to a stored procedure

Have you ever wanted to pass a DataTable, or array, containing tens or tens of thousands (or even millions) of records, into a SQL Server2000 stored procedure in just one database call? Ever wanted to pass a list of IDs of records to select or delete into a SP? Then read on.

Passing an array or DataTable into a stored procedure

How to pass a list of values or array to SQL Server stored procedure

Multi-table insert/update using XML

Simulating dynamic SQL in ASE

2007-12-18T21:29:00.001-06:00

Simulating dynamic SQL in ASE

With a certain regularity, Sybase users ask questions like "How can I use dynamic SQL in ASE ?" in the Sybase-related newsgroups. There are a number of different answers to this question.

The elegant way: execute-immediate (ASE 12.0+)

First, if you're running ASE 12.0 or later, you can use the execute immediate feature, which gives you full dynamic SQL capabilites, as it lets you execute dynamically generated SQL command strings. A simple example:
declare @c varchar(25)
select @c = "select getdate()"
exec (@c)

While this example is hardly something to get excited about, execute-immediate has opened a new dimension to the sort of thing you can do with T-SQL. The most important characteristic is that you can now create stored procedures (and triggers) that are independent of the schema of tables -- a highly useful thing to do.

The clumsy way: simulate dynamic SQL(ASE pre-12.0)

First, If you don't have ASE 12.0, (so you're on version 11.9.x or before), then your ASE version won't support dynamic SQL. However, you can still simulate certain types of dynamic SQL statements through a variety of tricks. These will work in ASE 11.9.x and earlier versions, and even in old stuff such as version 4.9.x.

These are different ways of doing this:

If you're using ASE version 11.5.x or 11.9.x, you can simulate dynamic SQL by using the CIS-related stored procedure sp_remotesql. You'll need to use a backdoor trick which has some drawbacks, but otherwise works very well. It also provides almost as much functionality as the execute immediate in ASE 12.0, but it's quite a bit more complicated to get it all set up. Go here to get details on how to set this up and download some examples.

For any version of ASE, including 4.9.x, 10, 11.0, 11.5 and 11.9, you can achieve quite a few things by creatively employing existing commands and procedures. However, note the following: while offering some possibly useful ideas, there are significant limitations on what's possible with this approach, so this is mainly aimed at inspiration rather than at solving a wide range of real-life problems.

--------------------------------------------------------------------------------

Creating a table with a variable name

While the pre-12.0 Sybase Transact-SQL syntax does not allow you to use a variable for the table name in the create table statement, it is possible to achieve the same result with the following sequence:

create table some_unused_name
( column_1 int, column_2 varchar(80) )

exec sp_rename "some_unused_name",
"the_final_table_name"

By putting these statements in a stored procedure, you can effectively create a table with a variable name.
Note that you can also rename the columns of the newly created table:

exec sp_rename "final_table_name.column_1",
"Column_Number_One"

CHECK MORE OF THIS ON http://www.sypron.nl/dynsql.html

Simulating dynamic SQL through CIS

This is a demo of how you can use certain CIS (Component Integration Services) features in ASE 11.5 or 11.9 to simulate dynamic SQL.

ASE 12.0 contains a new feature called execute immediate which lets you execute a text string containing dynamically created SQL queries; however, this isn't possible in ASE 11.5.x or 11.9.x. This demo shows how to achieve basically the same functionality in ASE 11.5.x / 11.9.x.

Below on this page, you can download SQL scripts which will create a stored procedure named sp_exec_dynsql. This procedure takes a text string as an argument and executes the SQL statements contained in the text string. Here's an example: the below procedure myproc takes two arguments, being a column name and a table name. It will then select the specified column from the specified table using sp_exec_dynsql:

create procedure myproc
@col_name varchar(32),
@tab_name varchar(70)
as
begin
declare @cmd varchar(255)
select @cmd = "select " + @col_name +
"from " + @tab_name
exec sp_exec_dynsql @cmd
end

You can then do things like:
1> exec myproc "pub_name", "pubs2..publishers"
2> go

pub_name
----------------------------------------
New Age Books
Binnet & Hardley
Algodata Infosystems

(3 rows affected)

Behind the scenes, this works as follows: using a CIS-related trick, you can access the local server as if it were a remote server. You can then use sp_remotesql to execute any command string in that not-so-remote server. Because sp_remotesql takes a text string as an argument, you can create any SQL statement dynamically and then execute it.
Note that you must be running ASE 11.5 or later (earlier versions do not have the CIS functionality included), and you must run set up the server for these tricks to work.

CHECK MORE OF THIS ON http://www.sypron.nl/dynsqlcis.html

Converting SQL result sets to XML in Sybase ASE

2007-12-18T21:28:00.001-06:00

Converting SQL result sets to XML in Sybase ASE

There is a growing interest among ASE users for combining SQL and XML. Perhaps the most common requirement is to represent an SQL result set in XML format. There are a number of ways of doing this:
ASE 12.5.1: ASE 12.5.1 comes with a completely new implementation of XML inside ASE. Unlike earlier attempts at XML-in-ASE, 12.5.1 does not use Java-in-ASE this new 'native' XML functionality (actually, it does use Java for some of the minor features, but the main functionality doesn't).

Among many other things (like XPath/XQuery support), this new feature lets you turn an SQL result set into an XML document by simply adding the for xml clause to a select statement:
1> select pub_id, pub_name from publishers for xml
2> go

0736
New Age Books

0877
Binnet & Hardley

1389
Algodata Infosystems

To extract elements from an XML document, use the xmlextract() function:
-- first create some test data
create table MyXMLTab (id int, xmldoc text)
go

insert MyXMLTab values (1,
'0736
New Age Books')

insert MyXMLTab values (2,
'1389
Algodata Infosystems')
go

-- now retrieve the data
1> select
2> pub_id = xmlextract("//pubs/pub_id/text()", xmldoc),
3> pub_id_tag = xmlextract("//pubs/pub_id", xmldoc)
4> from MyXMLTab where id = 2
5> go

pub_id pub_id_tag
------ ---------------------
0877 0877

Note how the text() function strips the XML tags from the XML element. Also note that the returned value is still a text string, so if conversion to a numeric datatype is desired, this should still be done explicitly with convert().

This is only a brief example of the XML functionality in 12.5.1+. Many more functions exist; full details can be found in the ASE product documentation.
The XML stuff in 12.5.1 is a licensable feature, but fortunately, it is included in the free Developer's Edition of 12.5.1. ASE 12.5.1 also supports web services, which is based on this XML functionality.
ASE pre-12.5.1: In pre-12.5.1, ASE supports XML/XQL based on the Java functionality in ASE. This works well provided you have XML documents already stored in the database and you want to parse/query these using XQL. Unfortunately, there isn't an easy way of converting between SQL format and XML (this can be implemented with some of the included Java classes, but this requires implementation work on your part). Also, you'll need to license the ASE_JAVA option to use this functionality.
Full details are described here.
ASE 12.0+: In 12.0+, you can also turn the contents of a table into XML format using the stored procedure sp_tab2xml, described below. This is quite a simple tool (though it uses rather advanced T-SQL programming tricks), and does not require any licensable options; yet, it is surprisingly useful.
Lastly, Thomas Gagne's 'is' tool is basically an isql replacement that can return a result set as XML (or HTML); it works well, but you'll have to build the tool from the source code your self - YMMV...

--------------------------------------------------------------------------------

sp_tab2xml: a simple SQL-to-XML tool for Sybase ASE
I have repeatedly received requests from ASE users who simply want to present some of their relational data in XML format, but don't want to bother with Java licenses or custom software development. Basically, many ASE users seem to need a simple way to present a result set as XML.

For those requirements, I wrote a (rather straightforward) stored procedure named sp_tab2xml which presents the rows in a database table (or view) as XML documents, with tags corresponding to the column names. You can download this stored procedure here.

Let's just display the contents of the table pubs3..publishers as XML:
1> use pubs3
2> go
1> sp_tab2xml publishers
2> go

0736
New Age Books
Boston
MA

0877
Binnet & Hardley
Washington
DC

1389
Algodata Infosystems
Berkeley
CA

(return status = 0)

The column-list parameter can be used to include only certain columns:
1> sp_tab2xml publishers, NULL, "pub_id, city"
2> go

0736
Boston

0877
Washington

1389
Berkeley

(return status = 0)

Expressions are also allowed, and XML tags can be renamed:
1> sp_tab2xml publishers, NULL,
2> "Town=upper(city), Publisher=pub_id"
3> go

BOSTON
0736

WASHINGTON
0877

BERKELEY
1389

(return status = 0)

Finally, the where and order-by clause can be used to filter and order the rows:
1> sp_tab2xml publishers, NULL, NULL,
2> "where city like 'B%' order by state"
3> go

1389
Algodata Infosystems
Berkeley
CA

0736
New Age Books
Boston
MA

(return status = 0)

CHECK OUT MORE OF THIS ON http://www.sypron.nl/xml.html

Querying the system catalog for tables, columns and constraints

2007-12-18T20:26:00.005-06:00

Querying the system catalog for tables, columns and constraints
--------------------------------------------------------------------------------
// show tables and their columns in their original order
SELECT so.name, sc.name, st.name, sc.length, sc.prec, sc.scale, sc.status
FROM dbo.sysobjects so, dbo.syscolumns sc, dbo.systypes st
WHERE so.type IN ( 'V', 'U' )
AND so.id = sc.id
AND sc.type = st.type
AND st.usertype NOT IN ( 18, 25 )
AND so.name = "RTI_IMPORT_PROVRVU"
ORDER BY sc.colid

// show referential integrity constraints
SELECT so.name table_name,
sc.name column_name ,
constr_obj.name constraint_name,
ref_obj.name referenced_table_name,
ref_col.name referenced_column_name
FROM sysobjects so,
sysobjects constr_obj,
sysreferences sr,
sysobjects ref_obj,
syscolumns sc,
syscolumns ref_col
WHERE constr_obj.id = sr.constrid
AND sr.tableid = so.id
AND sr.reftabid = ref_obj.id
AND sr.fokey1 = sc.colid
AND sr.tableid = sc.id
AND sr.refkey1 = ref_col.colid
AND ref_obj.id = ref_col.id

How to get the nth highest salary

2007-12-18T20:26:00.003-06:00

How to get the nth highest salary
--------------------------------------------------------------------------------

This SQL will find the employee with the nth highest salary from the 'employee' table defined as
emp_id Integer
salary decimal(12,0)

SELECT emp_id, salary
FROM employee a
WHERE ( SELECT COUNT( * )
FROM employee b
WHERE b.salary > a.salary ) = ( n - 1 )

To list all the employees in the 'top - n salary bracket' use:

SELECT emp_id, salary
FROM employee a
WHERE ( SELECT COUNT( * )
FROM employee b
WHERE b.salary > a.salary ) <= ( n - 1 )

How to concatenate all the values from a column and return a single row?

2007-12-18T20:26:00.001-06:00

How to concatenate all the values from a column and return a single row?
----------------------------------------------------------------------------------

It was possible to concatenate a series of strings to return a single column, in a sort of analogous manner to sum summing all of the numbers in a column. However, this was not a 'feature' but a bug, so if you are running an EBF that has the fix for CR 210688, this is no longer possible.

(Obsolete:) Use a case statement, a la,

DECLARE @string_var varchar(255)
SELECT @string_var = ""
SELECT @string_var = @string_var +
( CASE 1 WHEN 1 THEN char_col
END )
FROM tbl_a

PRINT "%1!", @string_var
GO

How to execute dynamic SQL in the server (within stored procedures and triggers)

2007-12-18T20:25:00.005-06:00

How to execute dynamic SQL in the server (within stored procedures and triggers)
----------------------------------------------------------------------------------

ASE System 12 solution:

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT(*) FROM master..sysobjects"
EXEC (@sqlstring)
GO

ASE 11.5 and 11.9 solution utilizing the CIS features:

Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
GO

Enable CIS
sp_configure "enable cis",1
GO

Finally, use sp_remotesql, sending the sql to the server defined in point 1.
DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT (*) FROM master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
GO

Remember to ensure that all of the databases referred to in the SQL string are fully qualified since the call to sp_remotesql places you back in your default database.

ASE 10 solution (undocumented feature using stored procedures):

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "sp_who"
EXEC @sqlstring
GO

How to avoid divide by zero

2007-12-18T20:25:00.003-06:00

How to avoid divide by zero
----------------------------------------------------------------------------------

SELECT CASE WHEN field2 = 0 THEN 0 ELSE field1 / field2
FROM table

SELECT field1 / ( field2 * CONVERT( int, SUBSTRING( '1', 1, ABS( SIGN( field2 ) ) ) ) )
FROM table

How to implement if-then-else comparing strings in a select clause

2007-12-18T20:25:00.001-06:00

How to implement if-then-else comparing strings in a select clause
----------------------------------------------------------------------------------

SELECT ISNULL( SUBSTRING( 'Petit', CHARINDEX( 'SMALL', PRODUCT_SIZE ), 255 ), 'Grand' )
FROM PRODUCTS

How to emulate the Oracle decode function/crosstab with boolean logic

2007-12-18T20:24:00.000-06:00

How to emulate the Oracle decode function/crosstab with boolean logic
----------------------------------------------------------------------------------

From the Rozenshteins book, "Advanced SQL", there is the concept of characteristic functions: the delta function. For a Boolean expression such as A = B is written delta[A = B]. Its definition is that delta[A = B] = 1 whenever A = B is true and delta[A = B] = 0 otherwise. For delta[ au_ord = 1 ] , the translation is ( 1 - abs( sign( au_ord - 1 ) ) ).

If you want to test a field to see if it is equal to a value, say 100, use the following code:

SELECT ( 1 - ABS( SIGN( ISNULL( 100 - , 1 ) ) ) )

The innermost function will return 1 when the field is null, a positive value if the field <> 100 and will return 0 if the field = 100. This example is for Sybase or Microsoft SQL server, but other servers should support most of these functions or the COALESCE() function, which is the ANSI equivalent to ISNULL.

The SIGN() function returns zero for a zero value, -1 for a negative value, 1 for a positive value The ABS() function returns zero for a zero value, and > 1 for any non-zero value. In this case it will return 0 or 1 since the argument is the function SIGN(), thus acting as a binary switch.

Put it all together and you get '0' if the value match, and '1' if they don't. This is not that useful, so we subtract this return value from '1' to invert it, giving us a TRUE value of '1' and a false value of '0'. These return values can then be multiplied by the value of another column, or used within the parameters of another function like SUBSTRING() to return a conditional text value.

This is a neat way to use boolean logic to perform cross-tab or rotation queries easily, and very efficiently. Using the aggregate 'Group By' clause in a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX() functions, you can create queries and views to perform all kinds of summarizations but this technique does not produce easily understood SQL statements.

How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)

2007-12-18T20:18:00.000-06:00

How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)
----------------------------------------------------------------------------------

If you are using ASE version 11.5 or later, the simplest way to implement if-then-else or implement the Oracle decode is with the CASE statement.

SELECT STUDENT_ID,
(CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
(CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
(CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
(CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
(CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID

SELECT
CASE WHEN PRODUCT_SIZE = 'SMALL' THEN 'Petit'
ELSE 'Grand'
FROM PRODUCTS