I blogged about TFS and the maximum file path length issue a while back, and thought I had covered it pretty well.  However, the issue came back to sting me again, so I thought it deserved another post.

Our issue was, sometimes, but not all the time, we would get this error on our team build on the build server (but the local developer build would always work fine).

[Any CPU/Release] CSC(0,0): error CS0006: Metadata file '..\..\..\..\SharedAssemblies\MSApplicationBlocks\Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.dll' could not be found

It took some time to figure this out... the path was correct on the server and the DLL was there.  Now if you are the intuitive type, you may have already guessed from this blogs title that the problem is related to Windows path length limitation, but why was the problem intermittent?

It turns out that this assembly reference was on a VS.NET Team Test Unit Test project.  When you used the right click "Create Unit Tests" menu to create a new test, the wizard automatically adds assembly references, including a reference to this ExceptionHandling.dll.  This would break the build on our build server.  Our quick solution was to remove the reference from the unit test project, everything still compiled and it didn't seem to be needed.  The builds now worked again on the build server, UNTIL someone added a new unit test and the cycle would start over again.  This explained our intermittent problem.

It still didn't explain why the build failed when this assembly was referenced, until I happened to find this blog entry... the same blog entry I noted in Part 1 of this blog series, but I didn't connect the dots until now. Aaron's blog entry talks about the 260 character path limit, but doesn't mention the error message we were seeing.

It turns out, that for our unit test project,  this ExceptionHandling.dll reference was the longest path of all of them, and was just long enough to be too long, but ONLY on the build server.  The way the build server paths are structured is different than our dev boxes, which was shorter by about 25 characters and this explains why the build would break on our build server but not on our local boxes.

The other severely annoying thing is that the actual error message mentions NOTHING about the path length... just that the file cannot be found.

Using Aaron's tip about these variables ($(BuildDefinitionPath) and $(BuildDefinitionId)) in the properties of the Build Agent I switched to using the $(BuildDefinitionId) which shortened the path on our build server by about 23 characters.  Now the builds always work.

The moral of the story is... if you see the error message above, double-check your path lengths.

So I had a good fight today with a SQL Server 2008 connection that would not work.  The error was:

Cannot open database requested in login myDbName. Login fails. Login failed for user 'myDbUserName'

 This was dumbfounding.  Troubleshooting the usual suspects, the following were attempted:

• check the database server to ensure that the database exists
• check the user login to ensure it exists, check the permissions for the user for this database
• try to connect using SQL Server authentication using this username and password from the Enterprise Manager - this works perfectly fine - but the ASP.NET application still gives this error
• double, triple and quadruple check the connection string for the ASP.NET application - no problems found
• drop the database and the login, recreate and try again - no difference
• reboot the database server - no difference
• check the event log for error messages - only the one displayed above is shown - nothing else out of the ordinary

WTF?

I finally figured out, after a couple hours of head-scratching, from going through some event logs within SQL Server Enterprise Manager that the database name had 6 space characters appended to the name. Therefore what appeared in Enterprise manager and everywhere else as "myDbName" was actually "myDbName      ".  Something is obviously wrong with our MSBuild script which is autogenerating the database for us during the build process... I still have to figure out how it happened, but at least now I know why it would work in Enterprise Manager and not from the application.  The app defines the connection string explicitly (with incorrect name missing spaces), while EntMgr connects and gives you a list of databases to choose from, so you never need to define the database name.

Once I renamed the database to remove the blank spaces, everything worked again.

I confirmed my theory by running this query, which highlights the extra spaces:

SELECT '"' + name + '"' FROM sys.databases

Ahhhh, the world makes sense again.