5/12/10

SQL Server APPLY operator

If you would love two scoops of yummy SQL Server query goodness, the APPLY operator is for you.

As I've said before, rarely a day goes by when I don't say "D'oh!" in surprise (and embarrassment) at being unaware of a tool or technology that I probably should have known about, but just didn't.

The SQL Server APPLY operator is one of those gems that I should have known about 5 years ago. I really need to start buying those "What's New In SQL Server" books, and actually reading them.

Imagine this Microsoft Dynamics GP scenario: You need to query vendors and include the vendor's PRIMARY address information, as well as their REMIT TO address information. But it's not quite that simple (it never is).

You find that the client has inconsistent vendor address IDs in their database. Sometimes they just use the address ID of "REMIT", sometimes "REMIT TO" and sometimes "REMIT ADDRESS", among others. And naturally, there are some vendors who don't have a separate REMIT TO address at all. In theory, some vendors may have two or more remit addresses, so you have to handle that situation as well--in which case you only want the TOP 1 remit address record.

While trying to figure out how to write such a query, I came across this article that discussed the OUTER APPLY operator.

Think of it as an OUTER JOIN, but without needing the ON keyword and subsequent matching columns from the two tables. It's also a bit like a subquery, but with the flexibility of a JOIN.

In short, it will make your inner SQL geek salivate.

Here is an example query that retrieves basic vendor information, along with the TOP 1 primary and remit to address records, allowing me to reference the fields in my query through a derived table.

Note that I used the OUTER APPLY version, since I want to return the vendor information even if there is no primary or remit to address. If you only want to return records that have a match in your apply operation, you can use CROSS APPLY, which works like an INNER JOIN. (hence the two scoops of yummy goodness)

SELECT
RTRIM(v.VENDORID) AS VENDORID,
RTRIM(v.VENDNAME) AS VENDNAME,
RTRIM(ISNULL(vpa.VNDCNTCT, '')) AS CONTACT,
RTRIM(ISNULL(vpa.ADDRESS1, '')) AS ADDRESS1,
RTRIM(ISNULL(vpa.ADDRESS2, '')) AS ADDRESS2,
RTRIM(ISNULL(vpa.CITY, '')) AS CITY,
RTRIM(ISNULL(vpa.STATE, '')) AS STATE,
RTRIM(ISNULL(vpa.ZIPCODE, '')) AS ZIPCODE,
RTRIM(ISNULL(vra.ADDRESS1, '')) AS REMITADDRESS1,
RTRIM(ISNULL(vra.ADDRESS2, '')) AS REMITADDRESS2,
RTRIM(ISNULL(vra.CITY, '')) AS REMITCITY,
RTRIM(ISNULL(vra.STATE, '')) AS REMITSTATE,
RTRIM(ISNULL(vra.ZIPCODE, '')) AS REMITZIPCODE,
RTRIM(ISNULL(vpa.PHNUMBR1, '')) AS PHONE1,
RTRIM(ISNULL(vpa.PHNUMBR2, '')) AS PHONE2,
RTRIM(ISNULL(vpa.FAXNUMBR, '')) AS FAX
FROM PM00200 v
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%PRIMARY%') AS vpa
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%REMIT%') AS vra

I told you it was tasty! Bon Appétit!

Here are a few more articles on the topic:

http://msdn.microsoft.com/en-us/library/ms175156.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://decipherinfosys.wordpress.com/2007/10/08/apply-operator-in-sql-server-2005/

FROM: http://dynamicsgpland.blogspot.com/2010/05/sql-server-apply-operator.html

5/6/10

What financial data business owners neeed to know everyday

Yesterday Sales
Yesterday Cashflow
Last 30 Day Sales
Last 30 Day Cash Flow
Last 30 Day Top Selling Items
Last 365 Day Sales
Last 365 Day Cash Flow
Low Inventory Items

4/14/10

The Dashboard Demystified

by Victoria Hetherington, Dundas Data Visualization
Wednesday, September 23, 2009

This article aims to provide a fundamental understanding of what a dashboard is and provide a brief look at what dashboards can do for an organization. There is an abundance of dashboard literature out there and this article is a good starting point for those interested in a high level understanding of dashboards.

A dashboard is a business tool that displays a set of PIs (performance indicators), KPIs (key performance indicators), and any other relevant information to a business user. Dashboard data is often displayed in real-time after retrieval from one or more data sources in a business. Dashboards are interactive, allowing an executive to drill into particular aspects of the display or switch between facets or views of the data. Key performance indicators need special consideration because they are high-level measurements of how well an organization is doing in achieving critical success factors – in other words, the goals or targets set by an organization in their strategic planⁱ. Dashboards are composed of data visualization tools like charts, grids, gauges and maps. Many different sectors of many different businesses benefit from dashboards: both a miner determining where to drill from a map of a geographical area and a CEO deciding where to channel funds would benefit from dashboard use.

Dashboards can provide an effective solution to the overwhelming amount of data that business users experience every dayⁱⁱ. A dashboard can save employees time - and companies money - by making everything more intuitive, easier to observe, and allowing for extensive, real-time access instead of going through papers and emails to compile information. In order to have a significant return on the investment of a dashboard, it is important that the dashboard be exactly tailored to suit the needs of a company or a particular role within a company. In addition, it is important that a dashboard have metrics that are meaningful and useful to its target audience. Dashboard vendors, and those looking to invest in dashboards, must consider dashboard options such as interface – i.e., would a primarily graphical interface or an integration of graphics and text suit best? How about a static display or an interactive displayⁱⁱⁱ? Would it be necessary to invest in an ODS (operational data store) to store and support access to data and metadata^iv?

Before deciding on a dashboard and becoming familiar with dashboard categorization and the countless types of data visualization tools available, it is important to be aware of several traits all good dashboards have in common. All dashboards should display a quantitative analysis of what is going on with immediacy and intuitiveness. They should offer creative visual insight, such as an anatomy chart or heat map would offer a hospital – but the interface must not be overly complex: distractions, clichés, and unnecessary embellishments can create confusion^v. Good dashboards offer appropriate context for data: for example, a gauge may show that Company X sold 1000 units this year, but compared to what? Highlighting relevant data, effective color use, and a visually appealing interface all help too. A good business dashboard, in other words, pairs dashboard technology with visual effectiveness. In more explicit terms, here are some key elements of a good dashboard:

It communicates with clarity; quickly, and compellingly. Simplicity is key.
It has minimal unnecessary distractions, no matter how interesting, which could cause confusion.
It organizes business information to support meaning and usability
It applies the latest understanding of human visual perception to the visual presentation of information
It is pleasant to look at

Due to the incredible array of available dashboard technologies, definitive categorization of dashboards is a difficult task. One can categorize dashboards in terms of role, or strategic, analytic, and operational dashboards. Vendors are probably most used to referring to dashboards in these terms^vi; though there are several other categorizations that are very common as well.

Most often, dashboards are used for strategic purposes^vii. The common executive dashboard, designed for a strategic manager of a medium-sized business, for example, is a strategic dashboard. The strategic dashboard allows for a quick overview of an organization’s ‘health,’ so to speak; assisting with executive decisions such as the formation of long-term goals. The strategic dashboard, therefore, doesn’t require real-time data: what is going on right now is not important, what is pressing is what has been going on. When designing a strategic dashboard, visual communications experts recommend keeping the interface simple – showing just what has been going. It should be noted that not only higher-up corporate people use strategic dashboards to monitor an organization. For example, a middle manager can monitor data on a dashboard, and then create a presentation to pitch to his CEO about the data he has observed.

The analytic dashboard, as the name suggests, assists with data analysis. This can include making comparisons, reviewing extensive histories, and evaluating performance. When using an analytic dashboard, a tactical manager can go beyond what is going on – as with the strategic dashboard – and drill into the causes. They can determine why sales targets were not met; why problems keep occurring^viii. Through exploring these patterns, goals can be set to correct these issues over time.

The operational dashboard monitors functions which need constant, real-time, minute-by-minute attention, from a blood pressure monitor in an operating room to an assembly line in a refrigerator factory. As with the strategic dashboard, it is recommended that an operational dashboard have a simple interface: no statistics or analyzing. All that is required of a good operational dashboard is immediacy and practicality, like names of workers and sections of the workplace. They are generally used by those on a departmental, rather than executive level^ix.

Categorization of dashboards can go another, equally common route: a vendor could categorize dashboards by the type of data they handle: either quantitative; or pertaining to data based on quantity or number – which is overwhelmingly more common – or qualitative; which could include scheduling and simple, pertinent lists. A vendor could also think of dashboards in terms of domains, both vertical and lateral. A vertical dashboard is specialized for a specific industry, such as mining, manufacturing, banking, or healthcare. Dashboards in lateral domains are designed for the internal departments that most organizations have: the financial, marketing, manufacturing, and human resources departments of a bank, a mining company, and a hospital could all use a similar dashboard to create goals and determine solutions for financial problems; likely from strategic and analytic dashboards for example. It is hard to have an understanding of one mode of categorization without the other.

There are many, many different kinds of dashboards, all tailored to fit specific roles and almost every lateral and vertical cross-section of the world’s industries. This article described three fundamental types of dashboards, but a dashboard does not need to fit one of the categories in order to be successful. Successful dashboards convey a great deal of dense necessary information with clarity and immediacy. Over time, a successful dashboard will improve an organization’s decision-making based on aggregated BI, assist in goal-setting, help monitor negative trends, and improve workplace productivity.

http://www.dashboardinsight.com/articles/digital-dashboards/fundamentals/the-dashboard-demystified.aspx?newsletterid=042010

4/1/10

Formatting Dynamics GP Phone Numbers in a SQL Query

I'm sure this has been addressed thousands of times over the years, but since I just had to write the script, I thought I would post it here for posterity.

By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.

I recently had to write a SQL query that would be used to export vendor information to a CSV file. Because the system receiving this data has a free form phone number field, we wanted to make sure that the phone number data coming from GP was formatted consistently.

I'm sure there are several other ways to do this, but this is the script that I created to put the phone numbers in US phone format. It's pretty basic, but gets the job done.

SELECT
CASE RTRIM(PHNUMBR1)
WHEN '00000000000000' THEN ''
WHEN '' THEN ''
ELSE '(' + SUBSTRING(PHNUMBR1, 1, 3) + ') ' + SUBSTRING(PHNUMBR1, 4, 3) + '-' + SUBSTRING(PHNUMBR1, 7, 4)
+ CASE WHEN RIGHT(RTRIM(PHNUMBR1), 4) <> '0000' THEN ' x' + RIGHT(RTRIM(PHNUMBR1), 4) ELSE '' END
END AS PHONE
FROM PM00200

If you are having to store international phone numbers in GP, then it probably makes sense to edit the "Format" resource in Modifier to use a Fill of Space, and no format string.

Or if anyone has any other tips or tricks on querying and formatting GP phone numbers, post a comment!

3/29/10

VBScript To Send Email Using CDO

Sending email with CDO is a simple task. First we create a reference to the CDO component

Set objMessage = CreateObject("CDO.Message")
then fill-in Sender, Subject and Recipient (To) fields of the headers and the body text which can be either plain text or HTML. You can also add a file attachment. You then use the Send method to send the email.

Below I'll show all three types of emails, and how to send an email using a remote SMTP server in the event you are not running your own. I've added and example to illustrate how to request a return receipt and delivery status notifications.

Please note, when using the AddAttachment method in your scripts you must use a fully qualified pathname as the argument to the method. Using just a file name or a relative path will produce the error The specified protocol is unknown.

If you receive an error message related to objMessage.From then you should try replacing it with objMessage.Sender

I've added sample code to illustrate how to load the body of the email from a text file on your disk.

I've added a sample of how to load recipient data from a database.

I've added a sample illustrating how to use data from Excel in an email.

If you are looking for an ASP based email form processor then please look here.

If you are interested in a mass mailer using CDO and VBScript, look here.

This sample sends a simple text email that can be viewed in any email client.

Sending an HTML email.

Note the use of the Cc & Bcc properties to send using Blind Carbon Copy (Bcc) and Carbon Copy (Cc).
These properties can be used with either text or HTML email.

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@my.com"
objMessage.To = "test@paulsadowski.com"

'The line below shows how to send using HTML included directly in your script
objMessage.HTMLBody = "

This is some sample message html.

"

'The line below shows how to send a webpage from a remote site
'objMessage.CreateMHTMLBody "http://www.paulsadowski.com/wsh/"

'The line below shows how to send a webpage from a file on your machine
'objMessage.CreateMHTMLBody "file://c/temp/test.htm"

objMessage.Bcc = "you@your.com"
objMessage.Cc = "you2@your.com"
objMessage.Send

Sending a text email with an attached file.

By repeating the .AddAttachment method you can attach more than one file.
When attaching files keep in mind that your recipient may be limited in their
ability to receive files above a certain size. Many ISPs limit emails to 8 or 10MB each.
You should not send large files to anyone before obtaining their permission.

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@my.com"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text."
objMessage.AddAttachment "c:\temp\readme.txt"
objMessage.Send

Sending a text email using a remote server.

Sometimes you need to send email using another server. It may be required by your
company, or your ISP may be blocking the SMTP port, or your dynamic IP may be
blacklisted for being in a dynamic pool.

This code shows you how to use a remotes server rather than the SMTP server
on your own machine.

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@my.com"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text."

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Sending a text email using authentication against a remote SMTP server.

More and more administrators are restricting access to their servers to control spam or limit
which users may utilize the server. This example shows you how to use basic authentication,
the most commonly used authentication method, when the SMTP server you are using requires it.

This code is slightly more complex but not very difficult to understand or work with.

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" "
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Send using authentication against a remote server with a file attachment and return receipt and
delivery disposition notification requests. In order to use the Delivery Status Notifications (Return
Receipt and Delivery Disposition requests) we need to create a reference to the CDO Configuration
object in addition to the CDO Message object and set a small number of properties. You must
use cdoSendUsingPort (network connection) and not the SMTP server's pickup directory
(cdoSendUsingPickup).

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourhost.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your-username"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your-password"
.Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
Set .Configuration = objConf
.To = "test@paulsadowski.com"
.From = "me@my.com"
.Subject = "This is a CDO test message"
.TextBody = strBody
'use .HTMLBody to send HTML email.
.Addattachment "c:\temp\Scripty.zip"
.Fields("urn:schemas:mailheader:disposition-notification-to") = "me@my.com"
.Fields("urn:schemas:mailheader:return-receipt-to") = "me@my.com"
.DSNOptions = cdoDSNSuccessFailOrDelay
.Fields.update
.Send
End With

In real world usage you'll most likely want to load the text of the email from a file on your
computer. The sample code below shows you how to do this. The text can be either
plain text or HTML as needed.Our example assumes your text is in the file
C:\Temp\MyEmail.txt. This code loads the entire content of that file into a variable,
here named BodyText which you can then reference in your CDO code. We
assume BodyText is in the scope of your CDO code.

'These constants are defined to make the code more readable
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'Open the file for reading
Set f = fso.OpenTextFile("c:\temp\MyEmail.txt", ForReading)
'The ReadAll method reads the entire file into the variable BodyText
BodyText = f.ReadAll
'Close the file
f.Close
Set f = Nothing
Set fso = Nothing

Once the text is loaded you can use it in your CDO code something like this...

objMessage.TextBody = BodyText
or
objMessage.HTMLBody = BodyText

Load Recipients from a Database

As is the case with most thing in Windows there are many ways to accomplish a task. This is one method of many.

Our database is an Access format database that resides on the local disk. The table in our database that we are interested in is called Customers and each record consists of 4 fields named "ID", "Name", "Email", and "Customer", where ID is an autogenerated index, Name is the full name of our customer, Email is the customer's email address and Customer is their customer identification number.

We are only interested here in two fields, Name and Email.

ID	Name	Email	Customer
1	Bob Jones	bjones@test.com	12345
2	Jane Smith	jsmith@test.net	12346

Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\Acme Inc\Databases\Customers.mdb"
SQLQuery = "SELECT Name, Email FROM Customers"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result("Name"), Result("Email")
Result.MoveNext
Loop
end if
OBJdbConnection.Close

As you can see the code is simple. We create a database connection object then open the database and query it for the Name and Email fields of each customer. Those values are passed for each customer to a subroutine that sends the customer an email.

Sub SendMail(TheName, TheAddress)
Dim objMessage, Rcpt

Rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "This Month's Sales"
objMessage.From = """Acme Sales"" "
objMessage.To = Rcpt
objMessage.HTMLBody = TextBody
objMessage.Send

End Sub

If you are not accustomed to working with databases then this may have seemed a daunting task but as you can see from the code above, it's really quite simple.

We've already covered sending email so I'll just mention that this subroutine assumes the HTML body text is a variable called TextBody (see Loading email body text from a file)

Also we format the recipient's address in the standard format of "Name" for a more professional look to the recipient..

Remarks

As previously stated there are many ways to do this. I've presented one simple method here. Your own use may be with an ODBC connection; it may use mySQL or SQL Server; it may include personalization of the email body text and more. My intent here was to provide you with the basics to get you started.

Load data from an Excel Worksheet

There may be times when you want to generate an email using data from an application such as Excel. This is one simple illustration of how that could be done.

In our example we will be using a Workbook with three columns starting at column A row 1. Each row represents one product in our inventory and the three columns contains the following data about each item: Part Number, Name of Part, Number of Items in Inventory. Graphically our Workbook looks like this:

Part	Name	Stock
4583586	Fliggalhopper	452
5898547	Looplonger	293

This particular script works by walking down each cell of column 1 till it finds an empty cell which it assumes is the end of the list of entries. If your file may contain empty cells then you can use the Worksheet's UsedRange.Rows.Count property to find the last row in which an entry is made. Your code would then use a for loop something like this:

rowLast = objSheet.UsedRange.Rows.Count
for x = rowStart to rowLast
' do stuff
next

Function GetData()
Dim x, strTemp, objExcel, objWB

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("c:\Acme Inc\Workbooks\Test.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible while debugging
objExcel.Visible = True

' This is the row of our first cell.
x = 1

do while objExcel.Cells(x, 1).Value <> ""
strTemp = strTemp & objExcel.Cells(x, 1).Value & _
Space(10 - Len(objExcel.Cells(x, 1).Value))
strTemp = strTemp & objExcel.Cells(x, 2).Value & _
Space(50 - Len(objExcel.Cells(x, 2).Value))
strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCRLF
x = x + 1
loop

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application.
objWB.Close
objExcel.Quit

set objWB = Nothing
set objExcel = Nothing

GetData = strTemp
End Function

' This is our main function.
Dim strBody

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Inventory report for " & Date
objMessage.From = "me@my.com"
objMessage.To = "bossman@my.com"
strBody = "Part" & Space(6) & "Item" & Space(46) & "Stock" & vbCRLF

' Here we call the function GetData to populate the body text.
strBody = strBody & GetData

objMessage.TextBody = strBody
objMessage.Send

The code above will produce an email that looks something like this:

To: bossman@my.com
From: me@my.com
Subject: Inventory report for 3/19/2005

Part Item Stock
4583586 Fliggalhopper 452
5898547 Looplonger 293

This sample sends a simple text email via GMail servers.

It's like any other mail but requires that you set the SMTP Port to 465 and tell CDO to use SSL

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" "
objMessage.To = "me@my.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication and SSL."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "You@gmail.com"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

3/19/10

7 Things Never to Say to Your Boss

Everyone has a boss. Even if you "work for yourself," you're still an employee to your client.

A big part of maintaining the boss-employee relationship is to never allow a boss to think you dislike your work, are incapable of doing it, or--worse--consider it beneath you.

[See the best careers for 2010.]

These sound like no-brainers, but many statements heard commonly around the workplace violate these basic rules. Looking for an example? Here are seven heard in workplaces all the time. They may seem ordinary, even harmless. But try reading these from your boss's point of view. You'll see right away why it's smart to never allow these seven sentences to pass your lips:

"That's not my job." You know what? A lot of bosses are simple souls who think your job is to do what's asked of you. So even if you're assigned a task that is, indeed, not your job, refrain from saying so. Instead, try to find out why your boss is assigning you this task--there may be a valid reason. If you believe that doing the task is a bad idea (as in, bad for the company) you can try explaining why and suggesting how it could be better done by someone else. This may work, depending on the boss. In any case, remember that doing what's asked of you, even tasks outside your job description, is good karma.

[See the 50 worst job interview mistakes.]

"It's not my problem." When people say something is not their problem it makes them look like they don't care. This does not endear them to anybody, especially the boss. If a problem is brewing and you have nothing constructive to say, it's better to say nothing at all. Even better is to pitch in and try to help. Because, ultimately, a problem in the workplace is everyone's problem. We're all in it together.

"It's not my fault." Yet another four words to be avoided. Human nature is weird. Claiming that something is not our fault often has the result of making people suspect it is. Besides, what's the real issue here? It's that something went wrong and needs to be fixed. That's what people should be thinking about--not who is to blame.

"I can only do one thing at a time." News flash: Complaining you are overworked will not make your boss feel sorry for you or go easier on you. Instead, a boss will think: (1) you resent your job, and/or (2) you aren't up to your job. Everybody, especially nowadays, feels pressured and overworked. If you're trying to be funny, please note that some sarcasm is funny and lightens the mood. Some just ticks people off.

[See how to answer 10 tricky interview questions.]

"I am way overqualified for this job." Hey, maybe you are. But the fact is, this is the job you have. You agreed to take it on and, while you may now regret that decision, it's still your job. Complaining that it's beneath you only makes you look bad. Plus, coworkers doing similar jobs may resent and dislike you. And guess what? Bosses will not think, "Oh, this is a superior person whom I need to promote." Nope, they'll think, "What a jerk."

"This job is easy! Anyone could do it!" Maybe what you're trying to convey here is that you're so brilliant your work is easy. Unfortunately, it comes off sounding more like, "This work is stupid." Bosses don't like hearing that any work is stupid. Nor do they really like hearing that a job is easy peasy. It belittles the whole enterprise. If a task is simple, be glad and do it as quickly as you can. Even "stupid" work needs to get done.

"It can't be done." Saying something can't be done is like waving a red flag in a boss's eyes. Even if the thing being suggested truly is impossible, saying it is can make you look ineffectual or incapable. Better to play detective. Why is the boss asking you to do whatever it is? What's the problem that needs to be solved? What's the goal? Search for doable ways of solving that problem or reaching that goal. That's what bosses really want. Most of them do not expect the impossible.

Last words: When in doubt, remember that silence really is golden.

2/28/10

Biztalk Error Message - SSO masterkey not backed up

After installation of Microsoft Biztalk 2004, following error message gets written to the eventlog:

"The master secret has not been backed up. If you lose the master secret all the information stored in the SSO system will be lost permanently and your systems may fail to work correctly. Please use the SSO admin tools to back up your master secret."

To get rid of it, the SSO master key must be backed up using following command (assuming a default installation):

cd C:\Program Files\Common Files\Enterprise Single Sign-On\
ssoconfig -backupsecret filename.bak

The program will request a password and a reminder phrase for the backup file. After the file is created the error message should be gone.

I hope this blog can be useful to you and save some time in searching for a solution to this error.

http://www.cubido.at/Blog/tabid/176/EntryID/139/Default.aspx

2/23/10

Are you a good decision maker?

Decisions, decisions, decisions. It seems like every time we turn around, we have to make more decisions. The question is, "Are you a good decision maker?" If you aren't (or don't think you are), there is no need to worry. Decision-making is a skill that can be learned by anyone. Although some people may find this particular skill easier than others, everyone applies a similar process.

There are two basic kinds of decisions: those that are arrived at using a specific process and those that just happen. Although both kinds of decisions contain opportunities and learning experiences, there are definite advantages to using a specific process to make a decision. The most obvious advantage is the reduced level of stress you will experience.

Wise decisions are made using a definite process. They are based on the values and perceptions of the decision-maker and include carefully-considered alternatives and options along with periodic reassessments of the decision and its effects. Wise decisions may or may not follow societal norms and expectations, but they are right for the decider based on what he/she knows at that point in time about his/her options as well as him/herself.

Ten Steps to Wise Decision-Making

This process can be applied to any situation where you need to make an important decision. If you follow these ten basic steps, you will find yourself making wiser decisions in your professional as well as your personal life.

Define, as specifically as possible, what the decision is that needs to be made. Is this really your decision or someone else's? Do you really need to make a decision? (If you do not have at least two options, there is no decision to be made.) When does the decision need to be made? Why is this decision important to you? Who will be affected by this decision? What values does this decision involve for you?
Write down as many alternatives as you can think of. Brainstorm as many different alternatives as you can imagine. Let your imagination run free and try not to censure anything; this is not the time to be judgmental. Just be sure to write everything down.
Think where you could find more information about possible alternatives. If you only come up with a few alternatives, you may want to get more information. Additional information generally leads to more alternatives. Places where you can look for the information you need include friends, family, clergy, co-workers, state and federal agencies, professional organizations, online services, newspapers, magazines, books, and so on.
Check out your alternatives. Once you have a list of alternatives, use the same sources of information to find out more about the specifics of each option. You will find that the more information you gather, the more ideas will pop into your head. Be sure to write these down and check them out too.
Sort through all of your alternatives. Now that you have your list of alternatives, it is time to begin evaluating them to see which one works for you. First, write down the values that would come into play for each alternative. Second, look for the alternatives which would allow you to use the greatest number of your values. Third, cross the alternatives off the list which do not fit into your personal value framework.
Visualize the outcomes of each alternative. For each remaining alternative on your list, picture what the outcome of that alternative will look like. Here, too, it helps if you write out your impressions.
Do a reality check. Which of your remaining alternatives are most likely to happen? Cross off those alternatives that most likely will not happen to you.
Which alternative fits you? Review your remaining alternatives and decide which ones feel most comfortable to you. These are your wise decisions. If you are very happy about a decision, but are not as comfortable with its possible outcome, this is a clue that this is not a wise decision for you. On the other hand, you may dislike an alternative, but be very excited about the possible outcome. This decision would probably not be wise for you either. If you feel you can live with both the alternative as well as the possible outcome, this is the wise decision you should follow.
Get started! Once you have made your decision, get moving on it. Worrying or second-guessing yourself will only cause grief. You have done your very best for the present; you always have the option of changing your mind in the future. Remember, no decision is set in stone.
How is it going? Be sure to review your decision at specified points along the road. Are the outcomes what you expected? Are you happy with the outcomes? Do you want to let the decision stand or would you like to make some adjustments? If the decision did not come out the way you planned, go through the complete decision-making process again. In the process, answer the following questions: Did I not have enough information? What values actually came into play? Were they my values or someone else's? Remember, you can always change your mind!

Common Decision-Making Mistakes

As much as we would like to believe that we do not have any prejudices or biases, the fact is that everyone does. The more aware you are of yours, the better off you will be. The main reason everyone has their own way of viewing the world is because our brains simply cannot take in everything, at least not on a conscious level.

Have you ever tried to learn ten new things all at once? If you have, you know that it is very easy to become overwhelmed and end up learning very little at all. That is because of the way the brain works. Our brains screen and categorize information so that we can understand the world around us without being overwhelmed by it. We get into trouble when we fail to realize that many of the perceptions we hold are based on what society (i.e., parents, teachers, the church, all institutions, etc.) teach us, not what we actually know to be true.

Below is a list of the most common decision-making mistakes. By learning about these pitfalls now, you will be able to avoid them in the future.

Relying too much on expert information. Oftentimes, people have a tendency to place too much emphasis on what experts say. Remember, experts are only human and have their own set of biases and prejudices just like the rest of us. By seeking information from a lot of different sources, you will get much better information than if you focused all of your energy on only one source.
Overestimating the value of information received from others. People have a tendency to overestimate the value of certain individuals in our society and underestimate the value of others. For instance, experts, authority figures, parents, high status groups, people who seem to have it all together, and people we respect have a way of swaying our opinion based simply on the fact that we believe they know more than we do. When you find yourself doing this, ask yourself: Do they know as much about this problem as I do? Are their values the same as mine? Have they had any personal experiences with a problem like mine? In other words, keep their opinions in perspective.
Underestimating the value of information received from others. Whether we realize it or not, we also have a tendency to discount information we receive from individuals such as children, low status groups, women (yes, believe it!), the elderly, homemakers, blue-collar workers, artists, etc. This is unfortunate since many times these groups can paint a good picture of the other side of your problem. In other words, these groups may use entirely different values and perceptions in their answers to your questions. The result is a larger perspective of what the issues really are. Just make a note that if you find yourself discounting the information you receive from anyone, make sure you ask yourself why.
Only hearing what you want to hear or seeing what you want to see. Try this exercise. Ask a friend to look around them and make note of everything that is green. Now, have them close their eyes. Once their eyes are closed, ask them to tell you what around them is red. Almost everyone you ask will not be able to tell you what was red because they were focusing on what was green. Our perceptions work the same way. If we have expectations or biases that we are not aware of, we tend to see what we want to see. Likewise, if someone tries to tell us something we do not want to hear, we simply do not hear them. This is a common mistake that many people make. The key is to be aware of your own prejudices and expectations while at the same time staying open to everything that comes your way.
Not listening to your feelings or gut reactions. Have you ever made a decision only to have it be followed by a major stomach ache or headache? This is your body talking to you. Our brains are constantly taking in more information than we can consciously process. All of this extra information gets buried in our subconscious. Although we may not be able to retrieve this information, our body stores it for us until it is needed. In moments when we need to make a decision, our bodies provide clues to the answer through feelings or gut reactions. Unfortunately, our society teaches us to ignore these feelings, but by tuning into your intuition, you will find that you will make much better decisions in the long run.

http://www.sba.gov/smallbusinessplanner/manage/makedecisions/SERV_GOODDEC.html

Making Time

You planned on getting to work early to finish the project that's due today, but now the car won't start. You know you wrote the mechanic's name down somewhere, but now you can't remember where you put it. You frantically search through your notes, but you can't find it anywhere. There's no way you're going to have time to finish your project. You start to panic.

The clock just keeps ticking.

Most of us have felt swamped at one time or another. With hectic work schedules, family responsibilities, and social engagements, there just doesn't seem to be enough time for everything we need and want to do. However, there is light at the end of the tunnel. Although life will always provide us with its little twists and turns, once we learn to manage our time wisely, much of the day-to-day chaos in our lives can be reduced or even eliminated.

The first step in learning how to manage your time is to develop a general work schedule. Your work schedule should include time for yourself as well as time for the maintenance of your business.

After you've defined the major elements of your workload, the next step is to prioritize them by identifying critical deadlines, routine maintenance items, and fun/relaxation time. Answering questions like "How much time do I have to make this decision, finish this task, or contact this person?" will help you to start identifying what needs to be done immediately versus what can wait. Setting priorities depends on deadlines, how many people you must call to get the information you need, and whether you can delegate or get assistance from others. If you are involved in group projects, reserve additional time for communication and problem-solving.

Once you have identified your priorities, look at all of your options for achieving them. Evaluate and move forward with the ones you feel are the most useful for you. The only time to consider changing approaches mid-task is when you know the change will save time. If you are in doubt, it is usually best to consider in the direction you started.

By setting up your work schedule and identifying your priorities, you have already started down the road to more effective time management. Other time management suggestions you may find useful for managing both your business life as well as your personal life include the following:

Contract out tasks. Contract out tasks you do not have the expertise to complete. Your client will appreciate your honesty and effort to get the best result.
Start with the most worrisome task. Start the morning, afternoon, or evening with the most worrisome task before you. This will reduce your anxiety level for the next task.
Complete deadline work early. Not only will this reduce stress and lighten your work schedule, but it will also give you more self-confidence about managing your schedule.
Know your capacity for stress. When you are hitting overload, take the break you need (even if it is a short one) when you need it.
Stay organized. Take time at the end of each day to briefly organize your desk and make reminder lists of tasks for the next day or week.
Take advantage of down time. Allow yourself some down time between busy periods to review your schedule and reevaluate your priorities.
Get physical. Physical exertion such as walking, bicycling, swimming, or organized sports activities helps to discharge stress. Stretching, yoga, jumping rope, sit-ups, playing with children, or doing yard work are other types of therapeutic breaks you should consider during times of stress.
Have fun. Be sure to have some fun while working or playing; a good sense of humor can keep most problems in perspective.
Divide up your time. Decide how much time to spend on business development, personal needs, volunteerism, and family. Start by allowing 25 percent of your time for yourself. Each time you make a commitment, set a timeline for your involvement. Remember that maintenance takes at least 25 percent of the time you spend on any project whether it's business, marriage, or serving on the board of a non-profit organization.
Build flexibility into your schedule. Your availability to family and friends depends on the flexibility you build into your schedule. Female business owners frequently have the primary responsibility for making sure family members are cared for when they are dependent or ill, so it's necessary to leave some time in your schedule for emergencies or to have good backup resources. Get to know your neighbors so you know who to call on for help in times of crisis.

In the bigger picture, consider the relationship between your business life and your personal life. Be as realistic as possible when answering the following questions, keeping in mind what is most important to you:

What are your long term goals? Your partner's goals?
Where are the conflicts, and where are the similarities?
What is it that you really want to do? List all possible ways to accomplish this.
How long will it take you to reach your goal?
How do your timeline and goals affect your family (parents, siblings, partner, children)?
How do your personal goals conflict with or match your business goals?
How much time can you donate to community programs?
Have you talked about your personal goals with your business partner?
Have you talked about your business goals with your personal partner?

Don't underestimate the toll that emotional stress takes on your physical health and your ability to concentrate on your work or enjoy time with your family. Make sure you have time for the important people and events in your life.

(Ohio Women's Business Network, Columbus, OH, 4/97)

http://www.sba.gov/smallbusinessplanner/manage/makedecisions/SERV_MAKETIME.html

The Interview Process: How To Select The "Right" Person

How do you select the right person for your business? There is no perfect answer, but the interview process can be a tremendous help if you use it effectively. In other words, you must have completed all of the other steps in the hiring process in order to get the most out of the interview process.

Interviewing candidates for a position within your company is one of the final steps in the hiring process. Before you get to this step, you want to make sure that you've completed all of the preceding steps since each of these steps will have a direct impact on how effective the interview process will be. Below is a list of the steps involved in the hiring process. Note that after you have completed the interviewing process, there are still two additional key steps that you need to complete. In order to achieve the best hiring results possible, just remember that all of the steps are important.

In order, the key steps to finding the right person to fill a position in your company include:

Determining your need to hire a new employee. Are you properly utilizing the skills and talents of your current employees? Do you know what needs to be done? Can your business growth support a new employee?
Conducting a thorough job analysis. What are the job's essential functions and key performance criteria?
Writing a job description and job specification for the position based on the job analysis.
Determining the salary for the position, based on internal and external equity. Is the salary comparable and proportional with the salaries and responsibilities of other positions inside your company as well as similar positions out in the marketplace?
Deciding where and how to find qualified applicants. What are the recruitment techniques to be used? What is the time frame for conducting your search? Remember, advertising is not the only, or necessarily the best, way to recruit.
Collecting and reviewing a fair amount of applications and resumes and then selecting the most qualified candidates for further consideration.
Interviewing the most qualified candidates for the position, based on the job's description and specification.
Checking references.
Hiring the best person for the job.

Hopefully, after reviewing all of the resumes, you will be able to pick and choose a select number of qualified applicants to be interviewed. (If not, you may want to expand your time frame and rewrite any ad copy and/or look at another recruitment technique.)

Now that you know where the interview process fits into the hiring process, let's take a look at the dos and don'ts of conducting a successful interview.

Conducting the Successful Interview - What to Do

1. Prepare in Advance for the Interview

Know what you want in a candidate before you begin the interview. Review the job specifications and requirements that have been prepared.
Know the job and its responsibilities. Review the job description.
Prepare a list of standard questions concerning the candidate's skills, abilities, and past work performance that you want him/her to answer.
Prepare a list of prioritized and measurable criteria, either in the form of a worksheet or other method, for analyzing and comparing the candidates.
Review the candidate's resume prior to the interview.
Set specific appointment times and reasonable time limits.
Be prepared to justify the use of any required employment test. Typically, the most legally defensible tests are those that involve a piece of the job.

2. Collect Pertinent Information During the Interview

Since past behavior predicts future behavior, look for the candidate's behavior patterns as you collect information. For example, has the candidate enjoyed big picture work or detailed analysis more? Is he/she more of a generalist or more of a specialist? Oftentimes, by listening to how the candidate responds to your questions about previous jobs, you will be able to get a very good idea of what their behavior will be like in the future.
Try not to offer too much detailed information up front so that the candidate will be able to formulate answers that exactly fits your company's needs. Don't put the right words in his/her mouth! Remember, the candidate (hopefully) wants the job and will be looking to say the right thing to impress you.
Ask questions that focus on the candidate's past performances. For example, if the job, such as an office manager, demands an individual who is well-organized and handles paperwork easily, you may want to ask, "How do you keep track of your own schedules and desk work in your current position?"
Ask specific, structured questions in regards to specific problems that the jobholder may face. Focus on past behavior and the results of the candidate's actions in a particular situation. For example: "As the customer service representative, you may encounter a few unhappy campers who will yell and scream at you over the telephone or in person. Have you had any experience dealing with difficult customers? Who was the most difficult customer you had to deal with? What was the situation? How did you resolve the problem?"
Notice how well the candidate listens and responds to the questions asked.
Note the candidate's choice of words and non-verbal behavior. Are they answering your questions clearly?
Listen to the questions the candidate asks. Clarify the reasons why the questions are being asked. Notice which questions he/she asks first as they may be his/her primary concerns.
Take detailed handwritten notes concerning job related topics that will help you distinguish the candidates from one another (especially if you will be conducting several interviews). Help yourself remember each candidate and each interview clearly.
Record information pertaining to the set criteria that will help in the evaluation of candidates.
Organize and analyze the information immediately after the interview when memory is fresh. Don't try to remember everything, it's impossible. One idea is to rate each candidate on each of the criteria immediately following the interview.

3. Look and Act Professionally During the Interview

Dress appropriately.
Avoid appearing bored and fatigued.
Set a businesslike atmosphere.
Structure the interview and inform the candidate of the structure. Let the candidate know you will be focusing on past results and that you will be taking a lot of notes.
Provide information on the company and the job to each candidate.

4. Treat All Candidates Fairly

Use your list of standard questions during each interview so that you treat the applicants equally and so you can compare apples to apples.
Refer to the criteria for analyzing candidates. Ask questions in regards to the job criteria.
Keep all questions job-related.
Do not ask discriminating questions.
Show a genuine interest in every candidate you interview.
If possible, have at least one other person meet and/or interview candidates who are finalists. They should also rate the candidates on each of the criteria; ultimately, all interviewers should compare their ratings and discuss any discrepancies. Having more than one interviewer helps control personal biases.

5. Be Courteous and Respectful

Conduct the interview in a private place away from distractions.
Begin the interview on schedule.
If possible, conduct the interview without interruptions.
Allow sufficient time for the interview.
Appreciate the candidate's accomplishments.
Do not patronize the candidate.
Do not argue with the candidate.
Thank the candidate for his/her time and interest.

6. Facilitate Open Communication

Immediately attempt to establish a rapport with the candidate by breaking the ice; for example, ask about their experiences in a particular industry or geographical location (refer to his/her resume).
Promote a relaxed environment with free-flowing conversation.
Do not dominate the discussion by talking too much. Many experts use a 80/20 rule - you talk 20% of the time and the candidate talks 80% of the time.
Politely probe the candidate for information by asking open-ended questions that will provide insight into the candidate's values and traits.
Ask structured questions that will require some thought on the part of the candidate.
Listen carefully to the candidate's answers. If they do not provide you with specific results, probe until they do.
Explain the selection process to the candidate. Offer realistic time frames and stick to your word!

The Successful Interview - What Not to Do

The following list is comprised of subject matter that is widely regarded as off-limits for discussion in an interview by employment experts. Most of these subjects relate directly to federal and state employment laws. Legislation covering equal employment opportunity is extensive and complex. Check not only federal laws, but also your own state's laws and guidelines. Remember, state laws vary! Consult an attorney for legal advice before you begin the search process for a new employee.

In an interview, or on an employment application, do not ask questions:

Concerning the age of the candidate. Be careful using the words over qualified with older candidates.
About their arrest record (this is different from convictions - in most states, it is permissible to ask if the candidate has ever been convicted of a crime).
About race or ethnicity.
Concerning the candidate's citizenship of the U.S. prior to hiring (It is permissible to ask "Will you be able to provide proof of eligibility to work in the U.S. if hired?")
Concerning the candidate's ancestry, birthplace, or native language (it is permissible to ask about their ability to speak English or a foreign language if required for the job).
About religion or religious customs or holidays.
Concerning the candidate's height and weight if it does not affect their ability to perform the job.
Concerning the names and addresses of relatives (only those relatives employed by the organization are permitted).
About whether or not the candidate owns or rents his/her home and who lives with them. (Asking for their address for future contact is acceptable.)
Concerning the candidate's credit history or financial situation. In some cases, credit history may be considered job-related, but proceed with extreme caution.
Concerning education or training that is not required to perform the job.
Concerning their sex or gender. Avoid any language or behavior that may be found inappropriate by the candidate. It's his/her standard of conduct that must be met.
Concerning pregnancy or medical history. Attendance records at a previous employer may be discussed in most situations as long as you don't refer to illness or disability.
Concerning the candidate's family or marital status or childcare arrangements (it is permissible to if the candidate will be able to work the required hours for the job).
Concerning the candidate's membership in a non-professional organization or club that is not related to the job.
Concerning physical or mental disabilities (asking whether the candidate can perform the essential job duties is permitted). The ADA allows you to ask the applicant to describe or demonstrate how they would perform an essential function(s) when certain specific conditions are met . Check the law or consult with an attorney before moving forward.

Remember - when in doubt, ask yourself if the question is job-related; if not, don't ask!

For more information

The U.S. Equal Employment Opportunity Commission homepage: http://www.eeoc.gov/.

http://www.sba.gov/smallbusinessplanner/manage/manageemployees/SERV_INTPROCESS.html

Writing Effective Job Descriptions

Writing Effective Job Descriptions

A job description describes the major areas of an employee's job or position. A good job description begins with a careful analysis of the important facts about a job, such as the individual tasks involved, the methods used to complete the tasks, the purpose and responsibilities of the job, the relationship of the job to other jobs, and the qualifications needed for the job.

It's important to make a job description practical by keeping it dynamic, functional, and current. Don't get stuck with an inflexible job description! A poor job description will keep you and your employees from trying anything new and learning how to perform their job more productively. A well-written, practical job description will help you avoid hearing a refusal to carry out a relevant assignment because "it isn't in my job description."

Realistically speaking, many jobs are subject to change due either to personal growth, organizational development, and/or the evolution of new technologies. Flexible job descriptions will encourage your employees to grow within their positions and learn how to make larger contributions to your company. For example: Is your office manager stuck "routinely ordering office supplies for the company and keeping the storage closet well stocked " or is she/he "developing and implementing a system of ordering office supplies that promotes cost savings and efficiency within the organization?"

When writing a job description, keep in mind that the job description will serve as a major basis for outlining job training or conducting future job evaluations.

A job description should include a:

Job Title
Job Objective or Overall Purpose Statement - This statement is generally a summary designed to orient the reader to the general nature, level, purpose, and objective of the job. The summary should describe the broad function and scope of the position and be no longer than three to four sentences.
List of Duties or Tasks Performed - The list contains an item by item list of principal duties, continuing responsibilities, and accountability of the occupant of the position. The list should contain each and every essential job duty or responsibility that is critical to the successful performance of the job. The list should begin with the most important functional and relational responsibilities and continue down in order of significance. Each duty or responsibility that comprises at least five percent of the incumbent's time should be included in the list.
Description of the Relationships and Roles the occupant of the position holds within the company, including any supervisory positions, subordinating roles, and/or other working relationships.

When using job descriptions for recruiting situations, you may also want to attach the following:

Job Specifications, Standards and Requirements - The minimum amount of qualifications needed to perform the essential functions of the job, such as education, experience, knowledge, and skills. Any critical skills and expertise needed for the job should be included. For example, for a receptionist, critical skills may be having 1) a professional and courteous telephone manner, 2) legible handwriting if messages are to be taken, 3) the ability to handle a multiple-lined phone system for a number of staff members, and 4) the patience and endurance to sit behind a desk all day.
Job Location - Where the work will be performed.
Equipment to be used in the performance of the job. For example, do your company's computers run on a Apple MacIntosh or PC Windows environment?
Collective Bargaining Agreements - Agreements and terms that relate to job functions, if applicable, such as when your company's employees are members of a union.
Non-Essential Functions - Functions which are not essential to the position or any marginal tasks performed by the incumbent of the position.
Salary Range - Range of pay for the position.

Keep each statement in the job description crisp and clear:

Structure your sentences in classic verb/object and explanatory phrases. Since the occupant of the job is your sentences' implied subject, it may be eliminated. For example, a sentence pertaining to the description of a receptionist position might read: "Greets office visitors and personnel in a friendly and sincere manner."
Always use the present tense of verbs.
If necessary, use explanatory phrases telling why, how, where, or how often to add meaning and clarity. For example: "Collects all employee time sheets on a bi-weekly basis for payroll purposes."
Omit any unnecessary articles such as "a," "an," "the," or other words for an easy to understand point description. Using the above example, the statement could have read, "Greets all visitors and the office personnel to the building in a friendly and a sincere manner."
Use unbiased terminology. For example: use the he/she approach or construct sentences in such a way that gender pronouns are not required.
Avoid using words which are subject to differing interpretations. Try not to use words such as "frequently," "some," "complex," "occasional," and "several."

http://www.sba.gov/smallbusinessplanner/manage/manageemployees/SERV_JOBDESC.html