A few weeks ago, I started noticing that every time I opened a LINQ to SQL .dbml file in Visual Studio 2008 I would get an alert message that said "The connection property in the web.config file is missing or incorrect.  The connection string from the .dbml file has been used in its place."

The dbml file was configured to reference a connection string in the web.config file, but after I got this alert and clicked OK, Visual Studio would check out the project and add a new Settings.settings file under the Properties folder and generate a new connection string there, and then change the dbml file to reference that instead of the web.config.

This would probably be the behavior you would expect if the web.config was really missing the connection string, but in my case the referenced connection string was there and well-formed.  I know this, because the related code was under source control and other developers could open the same dbml file (from the shared code base) and they wouldn't get the alert and everything worked flawlessly.

To me, it seemed like the LINQ to SQL designer on my machine was simply having trouble reading from the configuration file ... so I thought it might be an issue with a particular dll or registry entry.  Another thing that convinced me of this was the fact that when you were in the dbml editor ... under the Connections property on my computer, none of the connection strings from the web.config were included in the drop down to select from.  But, when another developer would look at this on their machine (using the same exact code base and files), their Visual Studio environment would find those connection strings in the web.config and include them in the list to select from.

I figured out that I could just undo the changes to add the new file, and then view the differences of the dbml file's XML and undo the reference change to make it once again reference the web.config instead of the settings file it created ... and that worked fine for a couple weeks, but eventually jumping through those extra hoops that "should just work" got pretty old.

So I first tried to repair my Visual Studio install, but that didn't work.  Next, I completely uninstalled Visual Studio and then re-installed it and the related service packs ... but that didn't work either.  I was about to just format my machine and re-install Windows ... but I thought I would give Microsoft Support a chance, hoping they could help me.  I knew this was an obscure enough problem that they probably wouldn't be much help, but I really hate paving my machine ... so it was worth a little time to give them a chance.  After explaining what was going on to a technician, we tried to use ProcMon to figure out what was going on behind the scenes.  We noticed several registry errors, and that further convinced me that the problem was corrupted  or missing registry keys.  The support technician said he would have to look into it more, but I wasn't going to count on them as my only hope for a solution ... so I kept investigating myself.  Apparently when you uninstall Visual Studio, it doesn't remove all of the registry keys.  It also doesn't overwrite them all when you run a repair.  It doesn't even remove them all when you remove all the other software that might have hooks or add-ins related to Visual Studio.  So these corrupted or missing registry keys could have persisted through the repair and re-install.

Here are some of the issues we noticed in ProcMon (it is a snapshot of what was happening when I would open the dbml file and the pop-up alert would appear, and then I would click OK).  The one highlighted is the what I was guessing was the culprit, because that missed registry key occurred right between the read of the dbml file and the opening the related project (which I am guessing was to create the new settings file).  You can ignore the "Fast IO Disallowed" stuff, because that is unrelated.

So, although the support technician had actually explicitly advised me not to do this ... I decided to uninstall Visual Studio (and any other software add-ins that might be related to it), manually delete the related registry keys contained in any folder that appeared related to Visual Studio, and then do a fresh install.  I never think hacking in the registry is a good solution to a problem, but this was my last ditch effort ... and I was resolved to do a full re-install of Windows if it didn't work.  To my surprise ... it actually worked.

I can't guarantee this will work for you, or even recommend it ... but here are the registry folder locations I manually deleted:

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio
• HKEY_CURRENT_USER\SOFTWARE\Microsoft\VisualStudio
• HKEY_CURRENT_USER\SOFTWARE\Microsoft\VSCommon
• HKEY_CURRENT_USER\SOFTWARE\Microsoft\VSTAHost

Hopefully if you are still reading, this is the exact problem you are having and this will work for you as well.  The only thing I can say is ... it worked on my machine, and it saved me from having to completely re-install Windows to fix the problem.

We recently ran into a problem up at work where people were opening a particular spreadsheet from a network fileshare, which had a lot of formulas that were tied to a few inputs.  You would typically change one input cell, and a lot of the other related cells would be recalculated and updated automatically.  However, a wierd issue started occuring where some users would change the input and nothing would happen.  But ... when they saved the worksheet, all of the related calculated cells would be updated at that time.

I think the issue was related to one person opening the spreadsheet on a Mac that was running Office 2008, and when they saved it we noticed it changed a lot of subtle things like the colors or formatting ... but I think it may have also changed the formulas to be manually updated and only automatically updated when the file is saved, which is an Excel Option.  Regardless of how this issue occurred ... here is the fix:

1. Open Excel, and click on the  icon in the top left of the window, then go to Excel Options.
   
   
2. Then go to the Formulas tab, and change the Calculation options to Automatic, and hit OK to apply the changes.
   

I was recently using SQL Server Report Builder, and needed to format a date and time in a specific way.  I went to edit the related expression, and looked for built-in functions to help me work with dates under Common Functions > Date & Time ... where I found FormatDateTime:

This is the example shown for how to use it:

=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

This is helpful, because the 2nd argument is obviously a DateFormat enumeration ... but what are the other members besides ShortDate?  I searched (like you might have) and couldn't come up with anything.  The only thing I can assume, is that the DateFormat enumeration used in SSRS expressions is the same as the DateFormat enumeration in Visual Basic, which is:

• GeneralDate
• LongDate
• ShortDate
• LongTime
• ShortTime

None of these "canned" formats matched the way I wanted to format the date, so I kept looking and noticed some MSDN samples that used the Format method instead of the FormatDateTime method.  In the expression edittor, that function can be found under Common Functions > Text:

The Format method is very flexible, and allows you to use any of the standard string-based format patterns you are probably already familar with.  Here are a few common examples:

=Format(Fields!myDateTime.Value, "M/d/yy") ... 6/15/09
=Format(Fields!myDateTime.Value, "M/d/yyyy h:mmtt") ... 6/15/2009 2:45PM
=Format(Fields!myDateTime.Value, "MM/dd/yy HH:mm") ... 06/15/09 14:45
=Format(Fields!myDateTime.Value, "MMM d, yyyy") ... Jun 15, 2009
=Format(Fields!myDateTime.Value, "Short Date") ... 6/15/2009
=Format(Fields!myDateTime.Value, "Long Date") ... Monday, June 15, 2009

Of course, the standard format strings like "Short Date" and "Long Date"  may vary depending on the culture set on your system.  The examples above reflect the "en-US" configuration.

Then, here is a slight variation of the 2nd sample.  It is my personal favorite and how I usually format all dates that have time values related to them, because I think the lowercase AM/PM designator makes the value easier for someone to scan, and requires less conscience effort to process.  If it is all uppercase, the AM/PM designator seems to blend in with the rest of the text and you have to pay a little closer attention to interpret the related value (even if it is just a tenth of a second).

=LCase(Format(Fields!myDateTime.Value, "M/d/yy h:mmtt")) ... 3/15/2009 2:45pm

I was working on a report in Report Builder 2.0 for SQL Server 2008 Reporting Services, and added a textbox to an existing report.  I wanted to display some summary info related to the data shown in the report.  I added a textbox, then went to edit the “Expression” property of that report item, but wasn't able to select any fields because it said “Report item not linked to a dataset.”

So I tried to find a property on the textbox related to a "Dataset" (like the "DataSetName" property on Tablix elements), but the only thing I found was a “DataElementName” property … which I tried, but didn’t work.

I then learned you can link to a dataset by defining it’s name directly in the expression, like this:

=Count(Fields!Address.Value,"DatasetName")

After installing SQL Server Data Mining Add-ins for Office, I was attempting to use some of the table analysis functionality it provides and ran into an error that said "Error (Data mining): Session mining objects (including special data source views used to process data mining dimensions) cannot be created on this instance".  Wow, what a helpful error message!

It turns out, in order to use the analysis tools like forecast, highlight exceptions, detect categories, analyze key influencers, scenario analysis, fill from example, etc. ... you have to configure the SQL server to allow creation of temporary mining models (aka "session mining objects").  Ahh ... the cryptic error message makes a little more sense now.

Here is how you fix it:

1. Go to Start Menu > All Programs > Microsoft SQL 2008 Data Mining Add-ins > Server Configuration Utility
   
   
2. The utility will walk you through a few simple steps, but this step is the one that really addresses this issue.  It explains a little more about these temporary/session models.
   

That's it.  After you finish the wizard, you should be able to run the model.

NOTE: If you created a new database for add-in users in step 3 of the configuration tool, you will obviously have to change the Analysis Services connection in Excel to point to that new database before this will work.