Remote Desktop (RDC) shortcut keys

Original Article

Shortcut key	Description
ALT+PAGE UP	Switches between programs from left to right.
ALT+PAGE DOWN	Switches between programs from right to left.
ALT+INSERT	Cycles through the programs in the order they were started.
ALT+HOME	Displays the Start menu.
CTRL+ALT+BREAK	Switches the client between a window and full screen.
CTRL+ALT+END	Brings up the Windows Security dialog box.
ALT+DELETE	Displays the Windows menu.
CTRL+ALT+Minus (-) symbol on the numeric keypad	Places a snapshot of the active window, within the client, on the Terminal server clipboard (provides the same functionality as pressing PrintScrn on a local computer.)
CTRL+ALT+Plus (+) symbol on the numeric keypad	Places a snapshot of the entire client window area on the Terminal server clipboard (provides the same functionality as pressing ALT+PrintScrn on a local computer.)

Disaster Recovery: What to do when the SA account password is lost in SQL Server

Instruction here

Debunking the 12 Key Myths of EHR Implementation

Click here

SQL date table script

Script to create a date table function

SQL builds

Microsoft SQL Server 2008, 2005, 2000 and 7.0 Builds

You don't have to be insane to be a progammer...

Excellent cautionary tale with very  useful pointers.
If you code professionally I reccomend this post.



Common Programmer Health Problems

I find many programmers seem to ignore their body's physical state when they're coding, most likely due to the intense concentration required. I'm hoping other people could benefit by simply understanding a few health related problems programming has almost caused me or caused many other people I know, and how I avoided them.

What I do want to cover are a set of particular problems programmers have from their daily profession. These are just simple really obvious things that for some reason programmers don't realize aren't supposed to be happening:

 

• Pain in your wrists from Repetitive Strain Injury (RSI).
• Problems with your eyes from staring at moving print for extended periods.
• Back problems from poor posture, especially in the lower back and upper shoulders.
• Bowel and urinary issues from not crapping and pissing when you should.
• Dehydration from drinking too much caffeine and not enough water.
• Problems with hemorrhoids and the prostate for guys from sitting too much. Yep, I'm gonna go there.
• Vitamin D deficiency from lack of sunshine.
• Sleeping disorders from staying up late and drinking too much coffee.
• General stiffness and soreness from a lack of stretching in general.

Passing lists to a SPROC - excellent article

Arrays and Lists in SQL Server 2005 and Beyond

In the public forums for SQL Server, you often see people asking How do I use arrays in SQL Server? Or Why does SELECT * FROM tbl WHERE col IN (@list) not work? The short answer to the first question is that SQL Server does not have arrays – SQL Server has tables. However, upto SQL 2005 you could not specify a table as input to SQL Server from a client, but you had to pass a string with the values and unpack it into a table on the SQL Server end.

This article describes a number of different ways to do this, both good and bad. I first give a background to the problem (including a quick solution that is good enough in many cases). I then give a brief overview over the methods, whereupon I discuss general issues that apply, no matter which method you use. Having dealt with these introductory topics, I devote the rest of the article to detailed descriptions of all methods, and I discuss their strengths and weaknesses.

SQL Date Elegance - Dan Guzman does it again

Calendar Table and Date/Time Functions

I frequently see questions in the forums and newsgroups about how to best query date/time data and perform date manipulation. Let me first say that a permanent calendar table that materializes commonly used DATEPART values along with time periods you frequently use is invaluable. I’ve used such a table for over a decade with great success and strongly recommend you implement one on all of your database servers. I’ve included a sample calendar table (and numbers table) later in this post and you can find other variations of such a table via an internet search.

• Removing the Time Portion

• First and Last Day of Period

• Calendar and Numbers Table

SQL Column Types

SELECT T.name AS TableName
, C.colorder AS ColOrder
, C.name AS ColName
, CASE WHEN C.xtype = 34 THEN 'IMAGE'
  WHEN C.xtype = 35 THEN 'TEXT'
  WHEN C.xtype = 36 THEN 'UNIQUEIDENTIFIER'
  WHEN C.xtype = 48 THEN 'TINYINT'
  WHEN C.xtype = 52 THEN 'SMALLINT'
  WHEN C.xtype = 56 THEN 'INT'
  WHEN C.xtype = 58 THEN 'SMALLDATETIME'
  WHEN C.xtype = 59 THEN 'REAL'
  WHEN C.xtype = 60 THEN 'MONEY'
  WHEN C.xtype = 61 THEN 'DATETIME'
  WHEN C.xtype = 62 THEN 'FLOAT'
  WHEN C.xtype = 98 THEN 'SQL_VARIANT'
  WHEN C.xtype = 99 THEN 'NTEXT'
  WHEN C.xtype = 104 THEN 'BIT'
  WHEN C.xtype = 106 THEN 'DECIMAL(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
  WHEN C.xtype = 108 THEN 'NUMERIC(' + CONVERT(VARCHAR,C.xprec) + ',' + CONVERT(VARCHAR,C.xscale)+ ')'
  WHEN C.xtype = 122 THEN 'SMALLMONEY'
  WHEN C.xtype = 127 THEN 'BIGINT'
  WHEN C.xtype = 165 THEN 'VARBINARY'
  WHEN C.xtype = 167 THEN 'VARCHAR(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 173 THEN 'BINARY(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 175 THEN 'CHAR(' + CONVERT(VARCHAR,C.length) + ')'
  WHEN C.xtype = 189 THEN 'TIMESTAMP'
  WHEN C.xtype = 231 THEN 'NVARCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
  WHEN C.xtype = 239 THEN 'NCHAR(' + CONVERT(VARCHAR,C.prec) + ')'
  ELSE '???' END AS ColType
, C.isnullable AS ColNullable

FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID

WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder

When the IE8 Developer Toolbar get confused while docked...

Discovering Internet Explorer Developer Tools

Getting Started

Getting started with the tools is simple: press F12 or click Developer Tools from the Tools menu.

Once open, the tools exist in their own window, each one connected to a single tab in Internet Explorer. If you prefer to decrease the number of open windows, pin the tools to a tab by clicking the Pin button or pressing CTRL+P.

Some features of the tools do not need the complete tools interface. In that case, click the Minimize button or press CTRL+M when the tools are pinned. The tools become a row at the bottom of the window, providing access to just theCommand Menu bar.

Things in SQL Oracle Developer I LIKE (and wish I had in MS SSMS)

1) Ability to filter objects (SPROC/functions/tables) with a LIKE (not a contains) and have multiple filters on at the same time

LIKE 'usp_hv%'
OR   'xsp_hv%'
OR   '%maint'


2) Ability to search within CODE using SQL without opening a separate tool

SELECT type, name, line, text
FROM   user_source
WHERE  UPPER(text) LIKE UPPER('%Text to search for%');

Outer join shortcut? DO NOT USE

Every now and again I find this awful SQL notation. (this is a note so I don't have to google it again)
Terse code is fine as long as you know what all the squiggly bits mean!

outer join shortcut? - dBforums:

*= is a LEFT JOIN
=* is a RIGHT JOIN.
It is a T_SQL extension and was valid up thru SQL 2000 It is no longer available in SQL 2005.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

How the other half thinks (tune in next year)

Think Like a Developer & Designer Series

The “Think Like A...” series is a collection of topics presenting a developer's point of view and a designer's point of view on what goes into making a WEB 2.0 website. The purpose of this series is to help developers understand and appreciate a designer's viewpoint on creating a website, and on the flip side, to help designers understand and appreciate a developer’s viewpoint on creating a website.

Oracle VS SQL debug

Can you spot the difference?

MS SQL

    SELECT PATNT_REFNO, PATAL_REFNO
    FROM TMP_PATSEARCH_FPATID

Oracle

    DBMS_OUTPUT.PUT_LINE('PATNT_REFNO'
            || CHR(9) || 'PATAL_REFNO');
    FOR c1 IN ( SELECT PATNT_REFNO, PATAL_REFNO
                FROM TMP_PATSEARCH_FPATID )
    LOOP
        DBMS_OUTPUT.PUT_LINE( TO_CHAR(c1.PATNT_REFNO)
                 || CHR(9) || TO_CHAR(c1.PATAL_REFNO) );
    END LOOP;


Why Oracle Sux

DECLARE
v_First VARCHAR2(20) := NULL;
v_Second VARCHAR2(20) := '';
v_Third VARCHAR2(20) := 'Something';
v_Blank VARCHAR2(2) := '';
v_Junk VARCHAR2(2) := '&~';
BEGIN
-- you need to run the following once, on it's own to make DBMS_OUTPUT.PUT_LINE work
-- what a shame there is no PRINT statement like a real database
-- SET SERVEROUTPUT ON;

-- proving NVL does not work with '' as the value
IF NVL(v_First,'')  = '' THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = "" Works'); END IF;
IF NVL(v_Second,'') = '' THEN DBMS_OUTPUT.PUT_LINE ( '"" = "" Works'); END IF;
IF NVL(v_Third,'') <> '' THEN DBMS_OUTPUT.PUT_LINE ( 'Something != "" Works'); END IF;
-- proving NVL does not work with a variable containing ''
IF NVL(v_First,v_Blank)  = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Blank Works'); END IF;
IF NVL(v_Second,v_Blank) = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( '"" = Blank Works'); END IF;
IF NVL(v_Third,v_Blank) <> v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Blank Works'); END IF;
-- proving that Oracle is junk, as other <> '' values do the trick
IF NVL(v_First,v_Junk)  = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Junk Works'); END IF;
IF NVL(v_Second,v_Junk) = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( '"" = Junk Works'); END IF;
IF NVL(v_Third,v_Junk) <> v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Junk Works'); END IF;
END;
/
 
RESULT:
NULL = Junk Works
"" = Junk Works
Something != Junk Works

 

Wot - me biased?

SQL Server - Oracle FAQ: "SQL Server"

Possibly the most xenophobic piece of community written drivel I have ever seen.

ViewState 101

TRULY Understanding ViewState - Infinities Loop

ViewState is a very misunderstood animal. I would like to help put an end to the madness by attempting to explain exactly how the ViewState mechanism works, from beginning to end, and from many different use cases, such as declared controls vs. dynamic controls.

I wish I had written this

Five Simple Database Design Errors You Should Avoid

Clear, concise and on the money.

This article covers:

(1) Common Lookup Tables
(2) Check Constraint conundrum
(3) Entity-Attribute-Value Table
(4) Application Encroachments into DB design
(5) Misusing Data values as Data Elements

Fun with triggers

What Happened:
1) Insert occurs - fires insert trigger (value 10)
2) Insert trigger updates record (value = 20) - fires update trigger
3) Update trigger updates record (value = 30) this does NOT fire a recursive call to the trigger
4) Update trigger writes hist record
5) Original insert trigger writes hist record
Hmmm interesting....



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MainTable]
( [MainTableID] [int] IDENTITY(1,1) NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[MainTable_Hist]
( [MainTable_HistID] [int] IDENTITY(1,1) NOT NULL
, [Created] [datetime] NOT NULL
CONSTRAINT [DF_MainTable_Hist_Created] DEFAULT (GETDATE())
, [MainTableID] [int] NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[trgMainTable_UPD]
ON [dbo].[MainTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ThisValue AS INT
      , @ThisID AS INT

SELECT @ThisID = MainTableID
     , @ThisValue = TheValue
FROM inserted

IF ( @ThisValue < 100 )
  UPDATE dbo.MainTable
  SET TheValue = @ThisValue + 10
  WHERE MainTableID = @ThisID

  INSERT INTO [dbo].[MainTable_Hist] ( [MainTableID], [TheValue] )
  SELECT MainTableID, TheValue
  FROM Inserted
END
GO

CREATE TRIGGER [dbo].[trgMainTable_INS] ON [dbo].[MainTable]
AFTER INSERT AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @ThisValue AS INT
    DECLARE @ThisID AS INT

    SELECT @ThisID = MainTableID , @ThisValue = TheValue
    FROM inserted IF ( @ThisValue < 100 )

    UPDATE dbo.MainTable
    SET TheValue = @ThisValue + 10
    WHERE MainTableID = @ThisID

    INSERT INTO [MainTable_Hist] ( [MainTableID], [TheValue] )
    SELECT MainTableID, TheValue
    FROM Inserted
END
GO

INSERT INTO [MainTable] ( [TheValue] ) VALUES ( 10 )
GO

--==--==--==--==--==--
SELECT * FROM [MainTable] ORDER BY MainTableID

MainTableID TheValue
1           30 

SELECT * FROM [MainTable_Hist] ORDER BY MainTable_HistID

MainTable_HistID Created                  MainTableID  TheValue
1                2009-10-05 14:33:02.553  1            20
2                2009-10-05 14:33:02.553  1            10

SQL Stupidity

So you are logged on to SQL Management studio.
The user you are logged in as has database X as the default database
Now you detach that database... and you are stuck - the connection fails, you can't do anything.

DUH!

A warning would have been nice.

SOLUTION: Connect as another user and set the default database to something else then re-connect.