FULL OUTER JOIN Not Working

Problem

FULL OUTER JOIN not returning all records from right table when left table has a Search Condition in the WHERE clause.

-- Full outer join with where clause at bottom

-- Returns all records from MyTable1 where Category is 23.

-- Does not return all records from MyTable2. Only matches with MyTable1.

SELECT t1.[Code],t2.[Code]

FROM [MyTable1] t1

FULL OUTER JOIN [MyTable2] t2

      ON t2.[Code] = t1.[Code]

WHERE t1.[Category] = 23

Solution

Use a Derived Table for the left table. The Search Condition is moved from the WHERE clause to the Derived Table query.

-- Full outer join with derived table, no where clause at bottom

-- Returns all records from MyTable1 where Category is 23.

-- Returns all records from MyTable2.

SELECT t1.[Code],t2.[Code]

FROM ( SELECT * FROM [MyTable1] WHERE [Category] = 23 ) t1

FULL OUTER JOIN [MyTable2] t2

      ON t2.[Code] = t1.[Code]

References

See also SQL Join Types

Team Foundation Server Delete Workspace

Problem

When a new developer uses a computer that another developer used, you may see the error: The working folder is already in use by the workspace on computer

Solution

1. Make sure there are no pending changes for that user:
   tf status /user:username
2. Delete the old workspace:
   tf workspace /delete [/server:servername] workspacename[;workspaceowner]

References

"Workspace Command." MSDN – the Microsoft Developer Network. N.p., n.d. Web. 16 Aug. 2013.
<http://msdn.microsoft.com/en-us/library/y901w7se(v=vs.90).aspx>.

Windows Search Filtering

Problem
Need to be able to narrow down search results in Windows 7. Unlike Windows XP, there are no boxes to check.

Solution
Use search filters by clicking on filters in search box:

1. Open the folder to search.
2. Click in the search box, and then click a search filter. (Ex: Kind:, Date modified:, Type:, Size:)
3. Click one of the available options.

Use keywords to refine a search:

• You may filter on a property that does not appear when you click in the search box by using special keywords.

Keyword Examples

filename:~=backup	Files and folders whose names contain "backup"
filename:=backup	Files and folders named exactly "backup"
filename:~<backup	Files and folders whose names begin with "backup"
filename:~>backup	Files and folders whose names end with "backup"
filename:~=backup kind:=document	Only files that are considered to be "documents" whose names contain "backup"
filename:~=backup kind:folder	Only folders whose names contain "backup"
filename:~=ClassLibrary filename:~=dll.refresh	Files whose names contain "classlibrary" and end with "dll.refresh"

References

"Advanced Tips for Searching in Windows." Advanced Tips for Searching in Windows. N.p., n.d. Web. 14 Aug. 2012.
<http://windows.microsoft.com/en-gb/windows7/advanced-tips-for-searching-in-windows>.

SQL Join Types

[INNER] JOIN
All matching pairs of rows are returned. Discards unmatched rows from both tables. Default type of join.

LEFT [ OUTER ] JOIN
Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER] JOIN
Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.

FULL [ OUTER ] JOIN
Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

See also FULL OUTER JOIN Not Working

CROSS JOIN
Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table.

References

joins, using. "Join Fundamentals." MSDN – Explore Windows, Web, Cloud, and Windows Phone Software Development. N.p., n.d. Web. 13 Aug. 2012.
http://msdn.microsoft.com/en-us/library/ms191517%28v=sql.100%29

"Using Joins." MSDN – Explore Windows, Web, Cloud, and Windows Phone Software Development. N.p., n.d. Web. 13 Aug. 2012.
http://msdn.microsoft.com/en-us/library/ms191472%28v=sql.100%29

"Null Values and Joins." MSDN – Explore Windows, Web, Cloud, and Windows Phone Software Development. N.p., n.d. Web. 13 Aug. 2012.
http://msdn.microsoft.com/en-us/library/ms190409%28v=sql.100%29

How to Force SQL Server Job Step to Fail

Problem

Need to force an operating system job type to fail.

Solution

In a .NET application, set the ExitCode of the Environment object to a value other than zero. C#.NET example:

private static void TestSqlJobOutput()

{

       System.Console.WriteLine("Sample Error Message 1"); // Shows up in SQL Server job history log.

       Environment.ExitCode = 1; // Causes SQL Server job step to fail when application ends.

}

How to Write Output to SQL Server Job History Log

Problem

Need to generate custom output in job history of an operating system job type.

Solution

1) Check this box in SQL Server Management Studio: Job Step Properties / Advanced / Include step output in history

2) Generate output in job's executable. C#.NET example:

private static void TestSqlJobOutput()

{

       System.Console.WriteLine("Sample Error Message 1"); // Shows up in SQL Server job history log.

       Environment.ExitCode = 1; // Causes SQL Server job step to fail when application ends.

}

Windows Event Viewer Filter XML

Windows 7 XML Sample

<QueryList>

       <Query Id="0" Path="Security">

              <Select Path="Security">

                     *[System[(

                           EventID=4624

                           or EventID=4625

                           or EventID=4634

                     )]]

                     and

                     *[EventData[(

                           (

                                  Data[@Name='TargetDomainName'] = 'Abcd'

                                  or Data[@Name='AccountDomain'] = 'XYZ'

                           )

                           and

                           (

                                  Data[@Name='TargetUserName'] != 'U123'

                           )

                     )]]

              </Select>

       </Query>

</QueryList>

References

"Event Viewer - Wikipedia, the free encyclopedia." Wikipedia, the free encyclopedia. N.p., n.d. Web. 19 Oct. 2011.
<http://en.wikipedia.org/wiki/Event_Viewer#Filtering_using_XPath_1.0>.

"Windows Event Viewer CUSTOM XML FILTER | Jamin Quimby Installation & Development Notes." Jamin Quimby .com | Jamin Quimby Installation & Development Notes. N.p., n.d. Web. 19 Oct. 2011.
<http://jaminquimby.com/index.php/microsoft-windows-2008/129-windows-event-viewer-custom-xml-filter>.

"Event Log Hell (finding user logon & logoff) - Ars Technica OpenForum." Ars Technica. N.p., n.d. Web. 19 Oct. 2011.
<http://arstechnica.com/civis/viewtopic.php?f=17&t=1139356>.

Blackberry Tips

Blackberry Browser View Source: alt + RBVS
Note: Tested on Blackberry 8800

Blackberry Reboot: alt + right shift + del
Note: Tested on Blackberry Bold 9700. Takes about 5 seconds for the red light to come on. Another 5 seconds for the screen to change.

Microsoft Outlook Tips

Hack to view full folder path of a message found in Search results

Outlook 2010
1) Double click on the message to open it in its own window.
2) Open the Advanced Find feature in message with CTRL+SHIFT+F
3) Click Browse... next to "Look in" folder name.

Source:

Determine the folder path of a message found in Search results - MSOutlook.info. (2013, December 19).
Retrieved February 6, 2015, from http://www.msoutlook.info/question/846

View Source of HTML Email

Outlook 2010
1) Open message
2) Ribbon Message tab/Move section/Actions/Other Actions/View Source

View Internet Headers

Outlook 2010
1) Open message
2) Ribbon File tab/Info section/Properties

Conditional Formatting of Email in Inbox
This allows you to, for example, have email from your manager show up as bold and red; or, show all email addressed specifically to you as green; etc.

Outlook 2010
1) Ribbon View Tab/Current View section/View Settings/Conditional Formatting
2) Add or modify rules

Updates

2012-09-04 Added "Conditional Formatting of Email in Inbox"

2012-09-06 Added "View Internet Headers"

2015-02-06 Added "Hack to view full folder path of a message found in Search results"

IIS 7 Command Line Utility Tips (APPCMD)

To use APPCMD, type this in command prompt
cd %windir%\System32\inetsrv
appcmd /?

Display all applications
appcmd list APP

Display all sites
appcmd list SITE

Display all applications belonging to a site
appcmd list APP /site.name:MySiteName

Find all virtual directories with the specified path
appcmd list VDIR /path:/GlobalControls

Output to XML, then open in Excel for formatted columns
appcmd list vdir /xml > "C:\AppcmdOutput.xml"

Create an IIS Command Prompt Shortcut:

1. Create new shortcut
2. Target: %comspec% /k "appcmd /?"
3. Start in: %windir%\System32\inetsrv

To combine commands, see:
Do complex IIS management tasks easily with AppCmd command piping « Mike Volodarsky's Blog Mike Volodarsky's Blog
http://mvolo.com/do-complex-iis-management-tasks-easily-with-appcmd-command-piping/

References

"Getting Started with AppCmd.exe : Management Tool Options : Planning Your IIS 7 Architecture : The Official Microsoft IIS Site." Learn IIS7 : The Official Microsoft IIS Site. Web. 15 Aug. 2011.
<http://learn.iis.net/page.aspx/114/getting-started-with-appcmdexe/>.

"Appcmd.exe." MSDN – Explore Windows, Web, Cloud, and Windows Phone Software Development. N.p., n.d. Web. 10 May 2012.
<http://msdn.microsoft.com/en-us/library/cc772200(v=ws.10).aspx>.