Concatenating strings from multiple records into a single value

Say that you want to concatenate values from several records into a single string.  The classic example is a list of categories where you want to present it to the user as Category Name followed by a comma separated list of all products for that category.

There are multiple solutions for this problem, but this one seems to be the most straight forward:

   1: Select C.Name, 

   2:     stuff((select ', ' + P.Name 

   3:             from Product as P

   4:                 where P.CategoryId = C.Id

   5:             for xml path('')),1,2,'') as ProductList

   6:     from Category as C

Trouble Connecting to a Server on a Different Domain

Occasionally, when attempting to connect to a server on a different domain, you may get an error that says the credentials are incorrect even though you didn’t enter any credentials, or if you do enter the credentials, it says that these credentials conflict with an existing set of credentials.   To fix this, open a command prompt and enter the following command:

   1: net use \\server /delete

Then you should be able to connect to the server, either through the command prompt or through the command prompt:

   1: net use x: \\server\share /user:username password

UUID Already Exists Error When Registering a Virtual Hard Disk

When registering a hard disk with Oracle Virtual Box, if you get an error like the following:

It means you need to change the UUID of the disk.  This generally happens if you have cloned a disk.  To correct this, you need to run the VBoxManage program which comes with Virtual Box.  This is generally located in the C:\Program Files\Oracle\VirtualBox directory.   Open up a command prompt in this directory and do the following (filling in the name of your hard disk file where applicable:

VBoxManage internalcommands sethduuid c:\directory\MyHDFile.vhd

Invalid Target Namespace When Deploying a Report to SQLServer 2008

If you get an error like the following when deploying a report to SQLServer ReportingServices 2008, it is usually because the report was created in a design environment setup for SQL Server 2008 R2, but your server is SQL Server 2008 R1 (I know, that’s not what it’s called, but you know what I mean).

The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded. (rsInvalidReportDefinition)

To fix this error:

• Edit the report RDL (Code View as it is called in BIDS).
• Replace the Report header with the following:

  <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" 
  xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
  
  

  
  
• Remove the <ReportSections> and <ReportSection> tags from the report. Note, remove the tags only.  Leave all content inside the tag.  Don’t forget to remove the closing tags for each of these.
  
• Save the report.  You should now be able to upload it.

Windows 7 and Program Files

With Windows 7, if you think you are putting something in the Program Files directory, you may actually be storing the file somewhere else.   Windows virtualizes this directory so that the files are stored in another location.

For example, if you drop an Access database into this location, and the Access database is updated, the updated copy will be stored in c:\Users\UserName\AppData\Local\VirtualStore\ProgramFiles.  All future references to this file will be virtualized to this location.  If multiple users are on the machine, they will each have their own copy of this directory, and a ‘Shared’ Access database will no longer be shared.

401.2 Error on IIS7

I was getting a 401.2 error on a website that I had moved to IIS7.  The website required Windows Authentication.  To resolve the issue:

• Make sure the website users have access to the underlying files.
• In the Application setup in the IIS console
• Go to Basic Settings for the Application (in the right pane), and click on ‘Connect As …’.  Selection ‘Application User’.
  
• Click the Authentication icon in the IIS section.
• Verify that ASP.NET Impersonation and Windows Authentication are both enabled. 
  

Alternating Background Colors in a Tablix Group

When alternating background colors in a group in a tablix, you cannot use the traditional formula for alternating rows (which would be something like: =iif(RowNumber(Nothing) Mod 2, "White", "#E5E5E5")  )

Instead you must use the following:

1. To go the Code for the report (found in the Report Properties dialog)
2. Add an even number function, and an internal variable to track the current row:

    Public _evenRow As Boolean  
    Public Function EvenRow() As Boolean  
      _evenRow = Not _evenRow  
      return _evenRow  
    End Function  
   

   
   
3. Go to the “Group Properties” for the Row Group that you want to alternate, go to variables, and add a variable called EvenRow. The expression for this variable should be =Code.EvenRow().  This will be executed each time the group is created.
   
4. Finally, in the background property for the group row, you can enter a modified version of the original expression: =IIF(Variables!EvenRow.Value=true,”Red”,”Green”)