Removing duplicate rows from a SQL server DB

I've been working on data imports lately and have run into this issue several times. If you data isn't clean when you import from a spreadsheet or csv or wherever, you can end up with duplicate rows in your tables. This isn't a really big deal if the rows have differing keys, but if ALL the data is identical, including the keys, then how do you delete the extra rows?

It turns out that the answer is pretty simple. Write your query to delete the rows but before you run it, set the rowcount to 1, this will prevent SQL server from performing your query on more than a single row. Don't forget to set rowcount back to 0 afterwards, or all your subsequent operations will only return 1 row which, at least for me, would be really frustrating :)

Here's a nice explanation of the process from Microsoft: http://support.microsoft.com/kb/139444.

Adding Labels/Arbitrary Text to MSCRM 2011 Forms

So, you say you want to add your own text to a MSCRM form huh? Well, Microsoft doesn't make it immediately obvious how to perform such a seemingly innocuous task. I was able to find a blog post here which did the trick: http://blogs.infinite-x.net/2011/05/20/adding-form-labels-using-html-web-resources/

Thanks to "Chris C" clarifying how to re-style the WebResource so that it takes up only the amount of space that you want it to take up instead of what MSCRM does by default.

I've taken the liberty of reproducing his comment below in case the blog post eventually goes "poof".

Chris C says:

April 26, 2012 at 6:34 pm

Hmm, looks like the HTML messed up in my last post! I'll remove any formatting which is going to mess it up this time :)

The reason you need to specify 2 rows is that the CSS isn't removing all the margins around the text and as such it's taking up more space than it appears.

I can successfully use 1 row with the following CSS changes:


body style="border: 0px 0px 0px 0px;
margin:0px 0px 0px 0px;
padding: 0px 0px 0px 0px;
font-family: Segoe UI, Tahoma, Arial;
font-size: 11px;
font-weight: bold;
background-color: #f6f8fa;"

Hope that helps!

MSCRM 2011 Problem Pricing Order Product Items

So, you've created your products, your price lists, your price list items and they're all nicely in the system. Now you go to add some products to your order and BAM! You get a pricing error. What gives?

We have a situation where we need unpriced or free items in our system. The gotcha isn't that you can't do it, it is HOW you have to do it. It seems that the only way CRM will be happy calculating a $0 price is if your pricelistitem uses the "Currency Amount" pricing method and an "Amount" of $0.

My experience has been that if you use any of the other methods with various values for Percentage and your product's referenced cost (List, Current Cost, Standard Cost) = $0 that an error will be displayed when the system attempts to calculate the Order Total.

Lesson Learned:
If you want to have an order-product to show up with a $0.00 price, set the order-product's pricelistitem "PricingMethod" to "Currency Amount" and the "Amount" to $0.00

Importing Data to MS CRM 2011 Online

After much struggling and gnashing of teeth trying to use MSCRM 2011's new feature that allows importing from .zip files, I discovered that zip files don't seem to support re-importing (updating). I was attempting to update the "Decimals Suported" field on all of our products (over 1000) and kept hitting a brick wall with the mapping. CRM wasn't recognizing the file as an import, it was trying to create the records and couldn't map the checksum fields. On a whim, I attempted to import the uncompressed file and viola, it worked. Silly me for trying to use the zip file to make the upload faster.

Lessons learned:
Use .zip files to create entities in CRM. This allows faster uploads for large files and the ability to associate records during the import process.

DON'T use .zip files to update existing products in the product catalog. (I have yet to determine if .zips will work to update other entities.) Use the uncompressed, modified file that you exported from CRM.