I recently has a user who found that email messages from specific people would cause her Outlook 2010 to crash 100% of the time. Most peoples emails were perfectly fine and others were not.
There were lots of entries in the Windows Event Log like the following
I read up online and found this article http://social.technet.microsoft.com/Forums/en-US/outlook/thread/178bce4f-707e-42ac-8743-ac41b7a4e046/ where a lot of people where having a similar problem.
There were lot of opinions given there and they did nothing to help me fix the problem directly.
I ended up opening a case with Microsoft and together over a period of a week we diagnosed the problem.
The problem was – A Corrupt Font
I found a few posts online which referred to Helvetica Font (above link and also other places) being corrupt and causing problems with Outlook.
But in my case it was an Arial Narrow font.
* I have suggested that Microsoft Write a tool to test all fonts for corruption – who knows what will happen.
One of the responses from Microsoft was
Based on my research I found that the affected emails which you sent to me is in Helvetica font. Looks like the issue is specific to Helvetica font.
This problem may occur if your document contains Type 1 fonts, such as Helvetica and TimesNewRomanPS.
To resolve this problem, please follow the action below.
Determine which offending Type 1 font is being applied to your document, and then remove the offending Type 1 Font Substitutions entry from the registry. To do this, follow these steps:
Collapse this imageExpand this image
If you are prompted for an administrator password or for confirmation, type the password, or provide confirmation.
314960 (http://support.microsoft.com/kb/314960/ ) How to install or remove a font in Windows
For more detailed information, please refer to http://support.microsoft.com/default.aspx?scid=kb;en-US;2119612
** IMPORTANT ** The Microsoft response above did not fix the problem but more investigation showed that Arial fonts were the issue
This problem was on a Windows 7 machine (and mine was as well) so I decided to copy the Arial fonts from the bad machine to my machine. After this “I” got the problem as well!!! (I made a backup of the fonts first!)
The Fix (for me anyway) - Copy my good Arial Fonts to the bad computer
In order to resolve this I needed to copy the good fonts from my machine to a temp directory on my machine and then copy them to a temp folder on the bad machine and then copy the font from the temp folder to the Fonts applet.
Normally using the Fonts applet you can not copy fonts to anywhere else, but by opening a CMD prompt you can make copies of them.
So on the good computer open a CMD prompt and change directory to c:\windows\fonts
You can now copy the good fonts to another folder on the local machine such as c:\temp
You now need to copy the fonts from the c:\temp folder to a folder on the bad computer – again c:\temp
Then on the bad computer open the Fonts applet (you can just type fonts and hit enter at the search programs and files area in the start menu)
Now with that window open you can drag the fonts from c:\temp to the fonts window and you will be prompted to overwrite any existing fonts (if copying more than one font you can let it overwrite all fonts)
Now by the time I got around to checking these fonts we had sent 100’s of megabytes of debug logs to Microsoft, removed all add-ons, started in safe mode, rebooted heaps of times and generally spent a lot of time on the problem.
I had been in contact with Microsoft on the phone and via email and a week or more time had passed with the user not being able to open emails from specific users.
I would assume you could copy all fonts from a known good machine to the bad computer. If you copy all fonts you may never know what font was corrupt, but there is a good chance that in less than 5 minutes you will have a working Outlook again.
I had a user ask me today how they can delete a duplicate row from their database table. They were receiving the following error message when they tried to delete the duplicate row.
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
The reason that the user gets this message is because the table did not have a Primary Key or other Unique Index defined. Why, that’s another story and not one I know!
An example table
Some example data
Notice the duplicate row (row 1 and row 3)
So the user in this case would select the first row and hit the delete button
They would then see this message
So how do we get rid of the duplicate row?
Add a new column which contains unique values
We could run the following code to add the new column and populate the new column with unique values
After issuing this command we will have something like this.
The user can now select their row and hit delete because SQL Server can uniquely identify this row.
You could then remove the new column
But this adds a new column to the table which is not really the best option so on to option #2
We can simply issue a simple delete command taking advantage of the Set RowCount command.
What this does is limit the delete command to one record. Since the data is duplicated you do not care which row is deleted as long as only one of the rows is deleted. No adding and removing columns.
WebMatrix includes a complete Web development stack that integrates a Web server (IIS Developer Express), database (Microsoft SQL Server Compact 4.0), programming model (ASP.NET Web pages with Razor syntax), and a tool (WebMatrix Beta) into a seamless experience. You can use WebMatrix to streamline the way you create an ASP.NET Web site from templates, or by starting a new Web site by using the world’s most popular free and open source (ASP.NET or PHP) apps like DotNetNuke, Umbraco, WordPress, or Joomla!. With WebMatrix you can code your Web sites, customize them, optimize them for good search engine ranking, test them, and deploy them to an Internet hosting company, all through the tool.
Learn more about WebMatrix through:
For additional resources, visit:
To start I should mention that you can not search the actual data in your tables for text, but you can search your SQL Objects – Tables, Views, Stored Procedures, Constraints, Triggers and Functions for text.
Often you want to find all references to a particular piece of text which could be the name of a table or column or just something that you have in a stored procedure.
The free tool from Red-Gate software fits the bill well, supporting SQL 2005 and above (including the express editions of Management Studio – See below for notes on SQL Express 2005)
There is some advertising links to their other products included in the tool, but they are well worth a look at if you have never used them before – Red-Gate products are great.
Downloading the tool
To download the tool simply go to http://www.red-gate.com/products/sql_search/
Note: You can only search a single database at a time.
The search is activated by simply typing into the search field and your results are immediately displayed in a grid (no need to hit enter).
You can limit the search to different types of objects such as tables or views if needed.
Clicking on any of the search results will display the full text of the object where it was found with any matching text highlighted.
SQL 2005 Management Studio Express
This statement is included on the requirements page for SQL Search at http://www.red-gate.com/products/sql_search/requirements.htm
* To use SQL Search with SSMS 2005 Express Edition, extensibility.dll must be installed.
But then they give you no indication of what that means or where you get it from.
You can download the extensibility.dll by installing the following tool:
As part of trying to monitor my power usage and to know when my power prices change I download the power prices online from my power provider every 15 minutes and store this in a database.
I have been doing this for more than 4 months now and wanted to produce some data showing how the prices have changed over time.
I wanted to produce a report of all of the price movements per product over time.
I have two SQL Server tables described below that contain the data:
CREATE TABLE [dbo].[PowerUpdate]( [id] [int] IDENTITY(1,1) NOT NULL, [DateTime] [datetime] NOT NULL, [Enabled] [bit] NOT NULL )
CREATE TABLE [dbo].[PowerItem]( [id] [int] IDENTITY(1,1) NOT NULL, [PowerUpdateID] [int] NOT NULL, [Name] [nvarchar](128) NOT NULL, [Price] [decimal](9, 4) NOT NULL, [Type] [nvarchar](128) NOT NULL, [Description] [ntext] NULL, )
I also have one view that just joins the tables back together for reporting purposes.
CREATE VIEW [dbo].[vPowerData] as SELECT PowerItem_1.id, PowerItem_1.Name, PowerItem_1.Price, PowerUpdate_1.DateTime FROM PowerItem AS PowerItem_1
Some sample data
So here is some examples of the data that I have in the database.
PowerUpdate contains records with an ID and a Date and Time which identifies when the update occurred.
PowerItem contains the actual power products that were available for the particular update date & time and their current price.
What I want in my report
What I would like to see is for every product, the date and time when the price was first seen, and the last date and time that the price was seen and the actual prices.
To make it more complex a product’s price could return to the same value as a previous date and time and I would like the last price to display null for the next price and next price datetime fields.
The final solution
Now I had problems trying to get my head around my report requirement. I spent some time trying to come up with a solution but failed – I knew that it could be done using a SET based solution, which is what I wanted, but I could not do it myself.
I posted my question to the guru’s at www.sqlservercentral.com – the original post is here http://www.sqlservercentral.com/Forums/Topic930464-338-1.aspx
I received some good replies from people on the forum and really appreciate the work all the people put into this, to help me!
I liked the solution provided by a member called “Mark-101232”
Below is his solution to the problem which did exactly what I needed and was very fast compared to some solutions given.
WITH CTE1 AS ( SELECT Name ,Price ,DateTime, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATETime) AS rn1, ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATETime) AS rn2 FROM dbo.vPowerData), CTE2 AS ( SELECT Name,Price AS [Min Price],MIN(DateTime) AS [Min DateTime],MAX(rn1) AS maxRN FROM CTE1 GROUP BY Name,Price,rn2-rn1) SELECT a.Name,a.[Min Price],a.[Min DateTime], b.Price AS [NextPrice], b.DateTime AS [Next Price DateTime] FROM CTE2 a LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.DateTime>a.[Min DateTime] ORDER BY a.Name,a.[Min DateTime];
Now although I have a solution I really need to understand the logic here so I decided to write this blog post to try to strip down this TSQL code so I can actually understand what is going on and in the process this may help someone else as well.
The first step is to split the query down into its parts.
SELECT Name ,Price ,DateTime, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATETime) AS rn1, ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATETime) AS rn2 FROM dbo.vPowerData
What this is TSQL is doing is return the name, price and date time and also two row numbers.
For more details on row_number() see http://msdn.microsoft.com/en-us/library/ms186734.aspx
The first row_number() function returns the a row number (resetting it to 1 when the name changes) and ordering it by the date time
The second row_number() function returns the a row number (resetting it to 1 when the name and price changes) and ordering it by the date time
The easiest way to look at this is to look at a subset of data and show you what gets returned.
Assume our PowerItem table only contains the following records
Note: There are 3 different products listed, but only the “$49.95 Value Pack” has had the price changing as follows:
The TSQL code in Part 1 will return the following result set
SELECT Name,Price AS [Min Price],MIN(DateTime) AS [Min DateTime],MAX(rn1) AS maxRN FROM CTE1 GROUP BY Name,Price,rn2-rn1
Note: In this case treat CTE1 as the final result set that is visible in Part 1 above
The TSQL code in Part 2 returns the following result set
SELECT a.Name,a.[Min Price],a.[Min DateTime], b.Price AS [NextPrice], b.DateTime AS [Next Price DateTime] FROM CTE2 a LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.DateTime>a.[Min DateTime] ORDER BY a.Name,a.[Min DateTime];
Part 3 is really just a simple Left Outer Join sorted by name and the Minimum Date Time, the reason for the left outer join is that we want the final price and date.
Hint: I often try to think as CTE (Common Table Expressions) as a physical table and you can therefore logically join it easier (well it works for me - sometimes)
So in this case we have the following going on (for the product name ‘$49.95 Value Pack’)
Note: there is also other filtered here such as b.name = a.name and b.datetime > a.[min datetime]
So for the first 4 rows this products the following output (when the order by clause is added)
Now I hope I have explained what is going on because I am still trying to decipher and understand this myself.
But in any case it has made it easier for me to comprehend so I am happy with that and if it helps anyone else then that is good.
Additional resources to create database and populate with data
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE
Server Error in '/' Application.
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65551. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: