HashSet to Comma Delimited String

HashSet Coding Example: A HashSet is a collection that contains no duplicate elements.

Note: Tested in .NET 4.0.

       HashSet<string> myHashSet = new HashSet<string>();

       myHashSet.Add("A");

       myHashSet.Add("B");

       myHashSet.Add("B");

       myHashSet.Add("B");

       myHashSet.Add("B");

       myHashSet.Add("X");

       myHashSet.Add("Z");

       myHashSet.Add("B");

       myHashSet.Add("A");

       string commaDelimitedString = String.Join<string>(",", myHashSet);

       // commaDelimitedString is equal to "A,B,X,Z"

References
"HashSet(T) Class (System.Collections.Generic)." MSDN – the Microsoft Developer Network. N.p., n.d. Web. 16 Oct. 2013. <http://msdn.microsoft.com/en-us/library/bb359438(v=vs.100).aspx>.

SQL Server Management Studio Settings

Miscellaneous, useful settings in SSMS.

Note: This was tested in SSMS 2012.

Assign custom colors to database servers. These colors are used on the status bar.

1. Connect to Server
2. Options >>
3. Connection Properties tab
4. Use custom color:

Change what is displayed in Tab Text (Example: Remove text that is redundant with status bar.)

1. Tools/Options/Text Editor/Editor Tab and Status Bar/Tab Text
2. Include database name (False)
3. Include file name (True)
4. Include login name (False)
5. Include server name (False)

Copy column headers with query results

1. Tools/Options/Query Results/SQL Server/Results to Grid
2. Check "Include column headers when copying or saving the results"

Customize Context Menu of SSMS

Problem
Need to customize context menus (also known as shortcut menus) in SQL Server Management Studio.

Solution

Note: This was tested in SSMS 2012.

Use the same technique that is used for Visual Studio. See Customize Context Menu of Visual Studio | CSC - Technical Notes for detailed instructions.

Copy Page Title and URL to Clipboard

Bookmarklet to copy page title and URL to clipboard in Internet Explorer

JavaScript:window.clipboardData.setData("Text",document.title + "\r\n" + location.href);void(0);

Drag this bookmarklet to the bookmarks bar: JavaScript:window.clipboardData.setData("Text",document.title + "\r\n" + location.href);void(0);

Bookmarklet to copy page title and URL to prompt window for copying to clipboard in Chrome

JavaScript:window.prompt("Copy page title and URL",document.title + "\r\n" + location.href);void(0);

Drag this bookmarklet to the bookmarks bar: JavaScript:window.prompt("Copy page title and URL",document.title + "\r\n" + location.href);void(0);

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.

}