You Can't Copyright Common Sense

No you cannot, unless your name happens to be Thomas Payne, you were born over 200 years ago, and actually published the pamphlet titled, "Common Sense".

The truth is that if you listen to most speakers today or read one of the plethora of books about sales, service or whatever, you will find that they are telling you about what you probably already know. For example, "A firm handshake and friendly smile go a long way toward making a lasting impression".... well of course it does.

Is there any true new ground when it comes to human interaction that has not already been written? Possibly, but not nearly as much as has been re-written over and again since the beginning of recorded time. So the question is, why do people keep buying the books, listening to those speakers or receiving the epiphanies in the middle of the night?

The answer is simple. No you cannot copyright common sense - but what ideas you put together behind what you say is as important as how you put your personal brand on it.

Dynamic SQL Passthrough Queries with Parameters

If you need to run a passthrough query against another DBMS and need to create that SQL on the fly because of a parameter, then you may be out of luck. At least, according to MSDN:
http://msdn.microsoft.com/en-us/library/ms188427.aspx

However, there is a nice workaround you can employ that will do the job. Here is an example:

-- variable to contain the passthrough sql statement
DECLARE @SQL VARCHAR(300)
-- variable to contain the dynamic lookup value into the query
DECLARE @KeyLookup INTEGER
-- varliable to contain the passthrough query
DECLARE @query VARCHAR(400)
-- populate the keylookup value
SET @KeyLookup = 102
-- prepare the sql statment for the passthrough query
SET @query = 'select * from some_table where key_value = ' + CONVERT(VARCHAR(10) , @KeyLookup)
-- prepare the passthrough execution query
SET @sql = 'select * from openquery(linkedservername, ''' + @query + ''')'
-- option to print the passthrough execution query in full for debug purposes (this is nice because you can paste it into a sql
-- editor and run it to see what is wrong)
PRINT @SQL
-- execute the passthrough
EXEC (@SQL)

Creating System Stored Procedures

So what are the steps to creating a systemwide stored procedure in SQL Server?

• Create them in the Master Database
• Name must start with "sp_"
• Mark them as System Objects using
• for sql 2000 - master.dbo.sp_MS_upd_sysobj_category
• For 2005 and later - sys.sp_MS_marksystemobject

For a test in 2008:

1. Create a stored procedure in the system databaseone called sp_test
2. Register them as system stored procs using sp_MS_marksystemobject.
3. Then try to use them each from another database without prefixing them with master.dbo.

How to send IM text when a SQL job fails

/* this is very useful for critical production jobs that may fail at times when you do not have access to your email, but can be contacted via SMS Text Messaging on your CellPhone*/

set nocount on
declare @message varchar(555)
set @message = 'Process failed in job xyz'

-- different providers have different addresses to send SMS to cell phone --
/* T-Mobile: phonenumber@tmomail.net
Virgin Mobile: phonenumber@vmobl.com
Cingular: phonenumber@cingularme.com
Sprint: phonenumber@messaging.sprintpcs.com
Verizon: phonenumber@vtext.com
Nextel: phonenumber@messaging.nextel.com
where phonenumber = your 10 digit phone number */

exec master.dbo.xp_sendmail @recipients = '5555551212@messaging.sprintpcs.com' , @message = @message , @subject = 'Svr263 Job Failure'

Geometric Project Managment

In a prominent banking institution a member of the IT staff made the following sarcastic joke; "In any other part of the world, two people might create a baby in 9 months, but here they throw 18 people at the problem and expect results in 1 month."

Simple math, right? Hardly so. There is no parallel dimension where human development happens at a faster rate. Certain things are just hard constraints with absolutely no variables. In other cases, like in the world of IT, the variables do exist, but only with trade-offs or functions that can be illustrated through geometric and mathematical formula. Geometry applied to project management - looks like witchcraft!

The usual real-world constraints of any set of Requirements are Cost, Resources, and Time. Conceptually this is simple. For any given project there exists a set of movable laws of nature. A set of requirements can be produced in a significantly shorter time, but only with an increase in cost and/or resources. Cost can be reduced, but only with an investment of time and/or resources. One can argue that Time and Cost are resources, and normally I would agree in the most broad definitions. Consider though that with an overall reduction or increase in Requirements, there is a direct relationship with the other inputs of this function.

Just as changing a single attribute of a triangle effect the other attributes, the same holds true for a set of requirements in a project.

Job Notification Random Features

If you've worked with job notifications in the past then you probably already know this, but I thought it was worth discussing. Any time you change the name of the operator you want to notify, the event that the operator is notified upon defaults back to 'On Success'.

There are three different events that will generate notification from the job properties:

• When the Job Completes - generates whether the job fails or completes
• When the job Fails - generates notification only upon failure
• When the job Succeeds - generates only upon job success

The problem comes in that intermittently when you change the operator to be notified, the event will default back to 'When the job Fails'. This is a great problem in that you would not be notified in the event of job success.

Using Try-Catch in Transact-SQL

SQL 2005 implemented the TRY-CATCH form of trapping errors in SQL code. The old ON_Error statement no longer applies. This new construct is used in many languages today including C# and VB.NET to name a few. The basic concept is that you place your code within a Try and then use a Catch to find errors it may have encountered. Here is a very simple example you can run:

/* Simple Try Catch Example */
DECLARE @Denominator INT
SET @Denominator = 0

BEGIN TRY
SELECT 1/@Denominator AS [Output]
SELECT 'Computation did not fail' AS [message]
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS Error#, ERROR_MESSAGE() AS [MESSAGE];
GOTO EXITOR
END CATCH;
SELECT 'Catch Did Not Find Error'
EXITOR:


Now set the value of @Denominator to 1 or some other valid value. Notice how the flow of program execution changes.