Invalid object name 'Entity'.

Submitted by Robert MacLean on Fri, 12/21/2007 - 09:21

My first MSCRM 4.0 upgrade was last night and so was my first MSCRM 4.0 issue. The issue after upgrading MSCRM and going to the site was "System.Data.SqlClient.SqlException: Invalid object name 'Entity'.". Screen shot is attached to the post if you want to see that.

Straight away you can see it is a SQL error, so I whipped out SQL profiler to see what is going on. I first limited to the _MSCRM and _METABASE databases (remember this is an upgrade) and started to watch what is happening. What I noticed is that it is running a "select * from entity" so entity must be a table or view. Checking the DB though I can't find it and running the select statement myself also failed. I also noticed that the _METABASE is not being called at all.

So using deployment manager I created a new tenant to see what that looked like. First thing I noticed is no _METABASE for the new tenant, and that if I ran the select statement in the new tenant DB myself it worked. So I look through that and what do I find a table named entity. But wait whats this before it? Normally tables are prefixed by dbo this isn't it's prefixed by metabase something or other, and then I remembered my SQL 2005 training on schemas.

See MSCRM 4.0 doesn't need 2 databases anymore since it uses schema's to put both the metabase and data in one database and can control the security seperately on that! This is also why SQL 2000 is not support, since it doesn't have schemas (atleast one of the reasons).

To resolve this I made sure the network service account (since we upgraded with that) had the same permissions on the original DB as on the new tenant DB and viola it worked!

Using Excel 2007 and SSIS

Submitted by Robert MacLean on Thu, 12/20/2007 - 15:21
Since the Excel source component in SSIS (2005) doesn't support the XLSX formats from Excel 2007 you kinda have to kiss that away (hopefully SSIS 2008 is better). So to load it you can use ODBC to create a system or user DSN to the XLSX file (office 2007 installs the ODBC drivers needed) and then using the data source reader component connect to the data source and viola! XLSX goodness and a fraction of annoyance.

I must admit that it seems slow though, but I am not sure if that is because I never needed to load 200000 records out of Excel before our my laptop has enter the Christmas spirit mode already.

If you have any better ideas or ways of doing this, I would love to hear about it.

Update: This may also be of help to some people, drivers for OleDB etc...
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

It is definately the size of the spreadsheet which is killing it. Made a new one with one record and it works fine.

Essential Developer Tools - Part 3: UDL Files

Submitted by Robert MacLean on Wed, 12/19/2007 - 14:03
One thing about being a developer is that at some point you will have to work with databases/datastores. It's a fact of life (like death and taxes). The thing about these databases is that they all have connections strings to get to them, but the problem is how do you get the connection string? A popular website is http://www.connectionstrings.com/ which lists them and you can then copy, paste and modify those for your own needs.

However there is a problem with this, in that it's open to errors during the modification (assuming you can copy and paste without error) and that there is no nice test system for your environment. Well fear not, Windows (I've known about this since Windows 2000 and can confirm it works on XP, Vista and 2003 fine) has a great feature for finding out connection strings, and testing them.

To do this you create a new empty file somewhere (desktop is good, cause you can go right click New -> Text Document). The trick is to make sure the file extension is udl (i.e. connection.udl). Now you can double click this file and using the (possibly) familiar connection wizard/odbc thing to set and test the values the values:
Once you click OK the window disappears.

So what good is that? Well if you now open that file in notepad you'll find the connection string in plain text right there! With all the right values for your environment! Anyway happy connecting!

Update 10 Jan 2008: If you are doing this on x64 machines and getting issues this may help: http://blogs.msdn.com/snehadeep/archive/2008/01/10/running-a-32-bit-data-link-properties-udl-in-64-bit-box.aspx

.NET Framework 3.5 - Part 2: What's new in it?

Submitted by Robert MacLean on Mon, 12/17/2007 - 11:50
For the sake of the series I am going to just highlight a few points. For real concrete stuff see:

There is a lot of new language features in 3.5, most important to highlight for this series is LINQ. Moving along to the more shiny information there is significant work put into integration of AJAX, WPF (XBabs support in Firefox, can work with cookies now), WCF (more WS* support, general syndication support, special model for web development,  and Silverlight. WCF + WF and WCF + AJAX now play very well together (lots of support for each other now). There is also support for new cryptography stuff (nice), peer to peer development. Interesting WinForms now supports the same model as ASP.NET for authentication.

WinAmp - Must have Plugin

Submitted by Robert MacLean on Sun, 12/16/2007 - 13:29
So after letting the auto tagger run all last night I now have my collection of music in a decent state. Next is the cleaning up of the collection. To do that I went and downloaded Media Library Organiser plugin. This great plugin renames files, builds a neat folder structure and removes duplicates. You can specify what the structure is (by default [Artist]\[Album]\[Track No] [Title].[File Extension]) and you can turn on/off the duplicate removal etc... Since this is all tagged base you desperately need to make sure your tags are done right first. This takes just a few minutes for thousands of audio files to be cleaned up.

Winamp Auto Tagger - Best Feature Ever

Submitted by Robert MacLean on Sat, 12/15/2007 - 18:13
WinAmp 5.5 was release recently with a great new plug-in called AutoTag. This plugin samples your music (the music itself, not the tags, not the file or filename, the actual music) then sends that to Gracenote who identify it and send all the tag information back so any music WinAmp can play (including music videos) gets correctly tagged and sorted.

Now add the album art download plug-in and your MP3 collection is looking great :)

These two features (and the revised layout, and outlook like popups) have made me a winamp fan again (after a few years of using Windows Media Player).

Get it from Winamp.com!

Essential Developer Tools - Part 2: Microsoft patterns and practise

Submitted by Robert MacLean on Fri, 12/14/2007 - 08:53
There is a scene in Jurassic Park where Ian Malcolm (If I remember right, he is the mathematician/chaos theorist) criticizes Jurassic Park for not earning the right to clone the dinosaurs as they just "Stood on the shoulders of those before them". Well that may be true when working with millions of years old dinosaurs, but in development standing on the shoulders of the greats makes life better for you as a developer and for your clients.

Being a Dot Net developer means that those giants include the Microsoft patterns and practices team. Well what do they do?

We talk with a large number of Microsoft customers, partners, and consultants to understand the commonly occurring scenarios and the technical challenges associated with them. Then we discover and harvest the solution patterns and engineering practices (including anti-patterns) that have proved successful in addressing these scenarios.
Once we understand the scenarios and technical challenges, we work with product and technology teams across Microsoft, industry experts, and with customers and partners, to build guidance that reflects both the current, practical state of the technology and that is also aligned with future Microsoft technology plans. Often, the underlying scenarios and solutions guidance ends up influencing future Microsoft product direction.
Most of our projects are conducted in CodePlex communities. This allows continuous input from the development community at large and keeps us honest. We call this approach, “customer connected engineering” and it ensures that what we deliver actually meets the needs of our customers. We also ship the source code to nearly everything we deliver, usually along with a license agreement that encourages source level adoption and customization.

From http://msdn2.microsoft.com/en-za/practices/bb969103(en-us).aspx

That's great for marketing but for developers it means that they produce tools, frameworks and documentation (guides, proven practices etc...). My personal favorite from them is a set of components called the Enterprise Library which provides amazing code for logging (log4net eat your heart out), database access, exception handling, cache handling, cryptography, policy management (ala AD policies in your app) and validation. It really is amazing stuff. Two nice examples are the database application block, you can connect to a database, run a query and get the result in result set and close the database all following best practices with a tool to change the connection string (cause it's not hard coded so your users may need it) all in 2 lines of code! The validation block provides attributes to decorate your classes with to enforce validation with no additional code. You can get all of that from http://msdn2.microsoft.com/en-us/library/aa480453.aspx and much more from http://msdn2.microsoft.com/en-za/practices/bb969097(en-us).aspx.

.NET Framework 3.5 - Part 1: Where you can find it?

Submitted by Robert MacLean on Thu, 12/13/2007 - 17:28
There is the logical place on the Microsoft site but if you installed Visual Studio 2008 you have it already (including 2.0, 3.0) all in 32 and 64 bit. Now in VS 2005 this was under the Visual Studio folder in Program Files. With VS 2008 it moved to being part of the Windows SDK, so you can now find it in (assuming you installed to default locations, else modify as needed):
C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages

BTW one nice feature of using this version, is that you have all 60Mb already downloaded. So when you launch it, and it says you need to download a bunch of data - well you give it a second cause it doesn't.