A personal repository of technical notes. - CSC

SQL Concatenate Row Data into Variable

Problem

Need to generate a comma-delimited string list from data contained in rows of table

Solution

DECLARE @commaDelimitedList varchar(8000)
SET @commaDelimitedList = '' -- Must set to empty value

SELECT @commaDelimitedList = @commaDelimitedList + [MyColumn] + ','
FROM [MyTable]

-- Remove trailing comma from list
IF (@commaDelimitedList <> '')
BEGIN
      SET @commaDelimitedList = LEFT(@commaDelimitedList, LEN(@commaDelimitedList) - 1)
END

-- Return record set
SELECT @commaDelimitedList AS 'CommaDelimitedList'

Debug Console Application When Started Outside of Visual Studio

Problem

Need to be able to debug a console application when the executable is started outside of Visual Studio. When doing this with a web application, you attach to process aspnet_wp.exe. This will not work with a console application.

Solution

Manually start debugger inside console application. Temporarily add the following line to the console application code:

System.Diagnostics.Debugger.Launch();

Running the console application executable will cause a prompt from Visual Studio Just-In-Time Debugger. At that time you may select Visual Studio from list of Possible Debuggers.

Option Strict On disallows late binding

Problem

When setting Option Strict On in Visual Studio macro, the following code examples:

Dim toolbar As Microsoft.VisualStudio.CommandBars.CommandBar
toolbar = DTE.CommandBars.Item("MyToolbarName")
DTE.ActiveDocument.Selection.NewLine()


Causes the error:

Option Strict On disallows late binding.

Solution

Use CType Function to explicitly convert an expression to a specified object like the following examples:

toolbar = CType(DTE.Commands.Item("MyToolbarName"), Microsoft.VisualStudio.CommandBars.CommandBar)
Dim activeDocument As Document = CType(DTE.ActiveDocument, Document)
Dim activeSelection As TextSelection = CType(DTE.ActiveDocument.Selection, TextSelection)

Windows XP Hangs Viewing Shortcuts

Problem
Windows hangs when opening or trying to view properties of shortcuts to network files that do not exist.

Note: This solution has been tested and works with Windows XP and Windows 7.

Solution
Prevent Windows from conducting a comprehensive search of the target drive in an attempt to find the file.
  1. Run gpedit.msc
  2. Select User Configuration / Administrative Templates / Start Menu and Taskbar
  3. Enable "Do not use the search-based method when resolving shell shortcuts"
References
"Windows XP Hangs When You Open a Folder That Contains a Network Shortc." PC Pitstop Forums. Web. 04 Mar. 2011.
<http://forums.pcpitstop.com/index.php?/topic/121312-windows-xp-hangs-when-you-open-a-folder-that-contains-a-network-shortc/>.
"Policy Settings for the Start Menu in Windows XP." Microsoft Support. Web. 04 Mar. 2011.
<http://support.microsoft.com/kb/292504>.
"Do Not Use the Search-based Method When Resolving Shell Shortcuts." Microsoft TechNet: Resources for IT Professionals. Web. 04 Mar. 2011.
<http://technet.microsoft.com/en-us/library/cc939469.aspx>.

Updates
2011-10-20 Added note that this works with XP and Win7.