December, 2004

Posts
  • Michaeljon Miller

    What's with the CRM security descriptor?

    • 0 Comments

    In response to a number of questions I'm seeing on the Microsoft CRM newsgroup I've gone back through the archives and dug this up. I hope it helps.

    There's been lots of speculation around the security descriptors in Microsoft CRM, and none of it is quite right. The “real” story behind the SD columns is talked about in the CRM architecture paper a bit. They really are NT security descriptors and in theory you can manipulate them using standard NT functionality. But... we use some of the access bits differently than they’re doc’d in MSDN and we’ve added some of our own.

    As for the “hash” rumor, and the “encrypted” one too – there’s no such thing. We store binary data in the database in base64 encoded text fields. That means that attachments, sales lit, and security descriptors are really binary data wrapped up in standard encoding. Somewhere around here I had a routine that would scan through a table, pull the descriptors, convert them back to the binary format that NT likes, and dump them to the console. It’s an interesting application for learning, but it’s not enough information to figure out which ACEs go into the descriptor itself.

    When we tweak an object (create, update, share, assign, etc.) we recalculate the SD values based on the instance’s location in the organization and on roles of security principals somehow related to it. For instance, a sales manager with DEEP READ access to accounts will probably have an ACE in the SD by way of the role membership that granted her the DEEP READ access.  Explicit share means that the principal gets an explicit ACE. Implicit means the ACE came from a group (role, team, business, …).

    But, don’t go thinking you’ve got it all figured out yet. You would still need to know how object hierarchies affect the SD value. That’s where this thread started. We have the concept of a ‘child entity’ which is essentially unsecure on its own. This means that we need to get the access rights from somewhere, so we pull it from the ‘parent’ instance. You can find these types by looking through the metadata for entities that don’t have security descriptors but still have security behavior and APIs.

    Oh, and the idea of creating an instance through the application or API and copying the SD for later SQL inserts will only sometimes work. Actually, it’ll probably not work more often than it will. The reason is that there’s some additional information in the database to track access to entities.  It’s not always kept up to date, and it’s not always applied to all entities, but it’s there nonetheless. So, if you try the trick of copying a known SD value when doing a “data migration” at the SQL level, remember caveat emptor.

    Hope that clears up some of the fog.

     

  • Michaeljon Miller

    CRM callouts are just plain hard to write

    • 9 Comments

    Building callout handlers for MS-CRM is hard. It’s just plain hard. Not because writing a COM object that implements an interface is hard – we’ve got a boatload of tools to help with that. They’re hard to write because the model is inconsistent and incomplete. The v1.x callout interface is very simple and has very simple semantics. It’s this simplicity that makes it useful, powerful, and hard to use.

    The callout interface looks like so. You’ve seen it in the documentation, and probably tried to implement it somewhere along the way. You might have even succeeded.

    [

        uuid("F4233E5B-17DC-4661-9ABC-6707A9F99215"),

        dual

    ]

    interface ICRMCallout : public IDispatch

    {

        HRESULT PostCreate([in] int ObjectType, [in] BSTR ObjectId, [in] BSTR OrigObjectXml);

        HRESULT PostUpdate([in] int ObjectType, [in] BSTR ObjectId, [in] BSTR OrigObjectXml);

        HRESULT PostDelete([in] int ObjectType, [in] BSTR ObjectId);

    };

    In case you’re a C# person, here’s the definition from the SDK. There’s not much difference.

    [GuidAttribute("F4233E5B-17DC-4661-9ABC-6707A9F99215")]

    public interface ICRMCallout

    {

        Int32 PostCreate(Int32 ObjectType, String ObjectId, String OrigObjectXml);

        Int32 PostUpdate(Int32 ObjectType, String ObjectId, String OrigObjectXml);

        Int32 PostDelete(Int32 ObjectType, String ObjectId);

    }

    The basic semantics are as follows:

    ·         PostCreate provides the handler with the instance XML as supplied by the platform consumer (this could be the application, integration, or another callout). The first parameter is self-explanatory, as is the second. OrigObjectXml on the other hand deserves some explanation and some discussion. I’ll get to that in a bit because it applies to PostUpdate as well.

    ·         PostUpdate follows the same pattern but happens after the changes have been submitted to the database. Note that I said “submit” and not “commit”. There’s an important distinction and this is one of those incomplete things about the callout interface. This problem applies to PostCreate as well so I’ll talk about it in a bit.

    ·         PostDelete is the simplest to understand and one of the hardest to use. It’s fired after a soft delete request is made to an instance. The only information supplied is an instance identifier (the type code and id).

     

    Side note – if you’re implementing a callout handler in C# you need to declare your class as follows. The thing that seems to be one of the biggest PSS issues is the GUID. It is a requirement that you add your own GUID. Don’t use the one from the documentation because someone else might have made the same mistake and now there are two registered COM classes implementing the same interface. Well, that’s not entirely true, there’s one – the last one to get registered.

     

    [ClassInterface(ClassInterfaceType.AutoDispatch)]

    [GuidAttribute("put a CLSID here")]

    public class MyCallout : ServicedComponent, ICRMCallout

     

    Submit vs. Commit

    Transaction semantics are not well-defined for callouts. Sometimes the callout is made inside of a transaction and sometimes it’s made outside. The sometimes isn’t well understood on the CRM team right now. That’s one of the problems with building large software systems from the ground up with a growing team. But that’s a discussion for another time. The important bit is that the transaction rules around callouts are ill-defined and the only assumption a callout author can make is that the instance in question is likely inaccessible for the duration of the callout function execution.

    What does this mean? Well, first it’s important to know how the platform calls the handler class. Let’s start by looking at the basic flow.

     

        COSERVERINFO si;

        si.dwReserved1 = 0;

        si.dwReserved2 = 0;

        si.pAuthInfo = NULL;

        si.pwszName = wszComputerName;

     

        MULTI_QI qi = { pIID, NULL, 0 };

        hr = CoCreateInstanceEx( callout, NULL, CLSCTX_REMOTE_SERVER, & si, 1, &qi );

    The thing to notice is that the platform is using an explicit out-of-proc call. Why? Because we really don’t want the platform to crash if the callout crashes. This means that the callout handler must be registered as an out-of-proc server, which is easily done using a COM+ application. But that also means that callouts can’t just be dropped on the server machine and registered as COM objects.

    Let’s look at how the platform actually calls a handler.

    for each pCallout

    {

        // call the event handler

        pCallout->PostCreate(otc, bstrId, bstrXml);

     

        // then release the interface pointer

        pCallout->Release();

    }

    The platform will walk the list of registered callouts and for each one it finds, and can create, it’ll call. But isn’t the definition of PostCreate supposed to return an HRESULT? Yup, and it’s being ignored by the platform because there’s no clear answer about what should occur if a given callout fails. This is good and bad. It’s good because the platform doesn’t get mired down in the transactional details necessary to “fix” things that might break in an unknown chunk of code. It’s also terribly bad for the same reason.

    Oh yeah, and all this happens deep, deep, deep in the platform infrastructure. So deep that we might as well consider them the equivalent of a database trigger. Not that triggers are bad or anything, many GP ISVs have built all kinds of solutions by adding triggers to the GP database. They’re bad because they happen to look like a trigger, and happen to behave like a trigger, but just aren’t triggers. There’s just no trigger context available. One thing that makes them very much like triggers is that they happen for every WRITE (for example) and WRITEs happen all the time in the platform for reasons that callout authors usually don’t care about (like on a security descriptor update because someone changed a role – does the callout care, probably not).

    The next thing to notice about the callout is that the platform calls it inline with the rest of the business logic happening on the current thread. This means that your customer (the application user typically) is sitting there patiently waiting for your callout code to complete. If you’re making a long-lived call to another application the user is effectively blocked from getting any other work done. It also means that the platform is blocked. At least one critical resource is waiting: the thread servicing the user request. But there may be other resources blocked depending on which platform call was made: database resources.

    Why does this matter?

    Well, if the database is blocked because the platform is in the middle of a transaction it means that other callers can’t get at the blocked resource. That caller might very well be your callout handler if it needs to call back into the platform to retrieve data. And, if the callout author is well-behaved then the callback to the platform is happening over SOAP, and that’s clearly an out-of-proc call.

    This is what I meant by incomplete earlier. The callout only gets the information that was supplied by the original caller and this data is clearly incomplete. The auditing, owner, and default data is missing from the XML for PostCreate, and it’s probably old data for a PostUpdate. The way around this is simple: call back into the platform to query the rest of the data. Oops, now we’re in trouble because we’ve probably gotten ourselves into a deadlock situation, and that’s not a good thing.

    There’s also the issue of PostDelete. Normally the delete handler works fairly well. It gets called when something gets deleted and it gets enough information to do something about it. Let’s say that a Sales Order Detail was deleted though. The callout will get the line item instance ID and nothing else. How should it deal with this? There isn’t a story. You could cheat and make an ADO call into the database to try to read the Sales Order ID from the just-deleted line item, but this is bad because we don’t want you reading the database directly (because we can and will change the structures from release to release) and that pesky deletion service might beat you to it and really delete the instance. Like I said, callouts are incomplete and themselves don’t have enough context to help the callout author.

    Uh, then what?

    Given all that, what’s a callout coder to do? First, make the callout hander as short, fast, and simple as possible. I recommend converting the callout parameters, along with some general contextual data, into a message and dropping it in a queue somewhere. This doesn’t have to be anything fancy. It can be MSMQ, a database table (in another database please), or even in the file system. This way control is returned to the platform as soon as possible which means control is returned to the user sooner. The next thing is to use a service (or other application) to watch that queue and do the expensive work out-of-band. This service is just another platform user and can call into the API to read the rest of the necessary data. Well sort of.

    Remember when I mentioned incomplete (yeah, I think I’m starting to repeat myself here). Sure, not getting the complete instance data is a problem, and if that problem were solved we could mitigate a lot of the call-back-into-the-platform problems. But what’s really missing here is the contextual information necessary to know what to do about the callout. For example, who made the original platform request? Was it an application user or was it your own callout code? There is no way to know. Part of protecting the platform from callout crashes means that getting context (current transaction, current method, and current user) to the callout is really difficult.

    For now we’re stuck. We can’t change the interface without breaking all those people who’ve already started writing handlers. We can add a new interface, but it’ll still suffer from a lot of the same problems. We need to go back to the drawing board and rethink how all this stuff can and should work, where it might best fit, how the ISV community might use it, and what tools should be supported (i.e. COM? VB6? .Net?)

    If you’ve read this far looking for enlightenment, I’m sorry, I don’t have the answer. I’ve told you what’s wrong with callouts, but you probably already knew that. Though I have given a little insight into how they work and I’m hoping that helps you understand how to write handlers that behave well. But, for now, building callout handlers for MS-CRM is hard.

     

  • Michaeljon Miller

    Add / Entity scenario in CRM 2005

    • 4 Comments

    I’m trying to get a feel for how ISVs and VARs might use custom tables and custom entities in MS-CRM. The canonical example around building 110 is that someone would add a BankAccount child entity to a customer (likely an Account). But that sure doesn’t seem like a valid solution.

    I mocked up a student management system using some of the custom entities features that are in the V2 alpha when we where doing some of the original design work around relationships. For the most part things fit into place pretty well, but the solution seems a little forced (I have a background in survey and assessment systems so the concepts all work correctly, it’s just that I went out of my way to get all the relationship “rules” in the model).

    Those "rules control how various relationships behave. The way we went about this was to devise a relationship type taxonomy and the rules that surround those types. The rules are pretty simple:

    ·         Cascade – broadcast the action ‘down’ the link to any related entity. The receiving entity will perform the operation for any related instances. This is a "code" thing that the platform manages on the caller's behalf.

    ·         Do not cascade – do not broadcast the operation at all. This action is useful for MERGE and security operations in many relationship models. It really means STOP the current traversal action for this relationship graph.

    ·         Remove link – we’ve traditionally called this ‘cut link’. The action disconnects the relationship based on the relationship type (a 1:M relationship will set the relating attribute to NULL by sending a message to the ‘far’ entity, an M:M relationship will delete the specified rows from the association table but no broadcast will occur).

    ·         Restrict operation – if an instance exists on the far end of a ‘restrict’ relationship then the whole operation is canceled. This is most useful for DELETE, but can apply to other operations.

    Keep in mind that in many cases there’s an overlap between ‘remove link’ and ‘do not cascade’ and we’ve been overloading their meaning based on the model.

    Note that operations only traverse in one direction. That is an operation will always start at the 1-side of a relationship and terminate at the m-side. Reverse traversals are undefined.

    The following scenario shows one possible extension to MS-CRM. This extension models a student management subsystem. The system tracks Accounts and Contacts as customers and students. Those students can attend seminars to receive necessary training. Those training requirements are determined based on an assessment exam. Courses can have pre- and post-requisites. Exams can be created for pre- and post-assessment (to determine training needs and to assess learning after the course has been completed).

    Many of the preceding models are represented in the scenario. There are several entity graph loops present. The loops are terminated by defining traversal rules on the following relationships: primary_contact, preferred_location, held_at, and held_for. The numbers throughout the model refer to the model numbers from an internal design document (they’re not too important).

    Note that activities are represented by a fictitious entity called ‘Generic Activity’. Activity and Annotation relationships are not shown in the model but can be inferred from the associated model metadata. Assume for purposes of this example that Course, Exam, Seminar, Location, and Instructor have relationships to Annotation. Also assume that Attendance Record and Examination have associated scheduling activities.

    Ideally “Add Entity” in V2 will have sufficient infrastructure to build a subsystem like the one below. A few modifications would make the model even more powerful such as defining Instructor and Location as Resources, and defining Examination, Seminar, and Attendance Record as Activity types.

    Now, does this seem like the kind of solution that you’d want to build on top of MS-CRM, or is this way too deep? Is the core scenario like the one above, or more like “I need to add a simple 1:M relationship to hold some ‘extra’ stuff”. Just wondering.

     

  • Michaeljon Miller

    More fun with XSD

    • 0 Comments

    One more old post making its way to MSDN.

    Torsten Schuster asked about the XML serializer, but unfortunately it was pretty buried. I saw it, but I'm not sure anyone else did. It sounds to me like another case of old CRM XSD and the VS XSD tool, but I'm not really sure.

    From what I gather the platform and APIs are behaving correctly. For example, on a sales order retrieve, the following column set can be passed in.


    <columnset>

        <column>salesorderid</column>

        <column>ordernumber</column>

        <column>name</column>

        <column>customerid</column>

        <column>customfield</column>

    </columnset>



    Which should (and in this case, does) result in the following XML.


    <salesorder>

        <salesorderid>{CA033A28-25FD-47E4-A98A-BDDF134B11F3}</salesorderid>

        <ordernumber>ORD-01001-54ZQ8T</ordernumber>

        <name>Test</name>

        <customerid name="STM Ltd." dsc="0" type="1">{3E8CDBD0-FD2E-409D-BB8D-39870AB689C1}</customerid>

        <customfield>stm_4711</customfield>

    </salesorder>



    The question is about the "customerid" element and why the serializer isn't pulling it into the hydrated object. I can only guess, but it sounds like the XSD doesn't have a correct definition for "salesorder" nor does it have a definition for "customerid".

    Ideally, the sales order XSD should have a "customerid" element of type "tns:customerType" which references this XSD


    <xsd:complexType name="customerType">

        <xsd:simpleContent>

            <xsd:extension base="xsd:string">

                <xsd:attribute name="name" type="xsd:string" />

                <xsd:attribute name="type" type="xsd:int" use="required" />

                <xsd:attribute name="dsc" type="xsd:int" />

            </xsd:extension>

        </xsd:simpleContent>

    </xsd:complexType>


    I can't guarantee that the VS XSD tool will cope well with the XSD that I talked about earlier. Although making the XSD tool deal with it is fairly trivial, I still prefer the other code generator.

    Like I said a while back, I can't support any of this stuff, but I can lend guidance on occasion. Hopefully this information is enough to get things moving again. If not, well, maybe someone else can chime in.

     

  • Michaeljon Miller

    Dealing with broken XSD

    • 3 Comments

    I'm bringing this over to MSDN from its original home.

    Ok, I've been dealing with this broken XSD issue for so long now that I just can't stand it. The platform has ways to give you back XSD, but it's not really XSD, and it's not really friendly about doing so. Given the current v1.x API, which aren't exactly friendly to deal with, and their insistence on using strings of XML for everything, I put this script together to start hiding the complexities. Now, one thing you'll notice is that this script doesn't generate XSD in a flavor that the VS .NET XSD.EXE tool likes (it's not the greatest either, but it does work usually).

    The cool thing is that there is a great tool available from the SD West Conference. This tool will happily eat the resulting XSD that this script generates and will create some very powerful client-side classes that should make everyone's life much easier. You'll still need to serialize the platform XML into a class, but that's pretty simple. The following C# snippit should do just fine (you'll have to grab some of the XML serialization references, IO, and Text, but that's left as an exercise for the reader).

    The third code snip is the SQL script you've been waiting for. I recommend always generating the whole pile, you'll end up with about 3,000 lines of XSD and about 30,000 lines of C# when you're done, but it's worth it. Remember though, as shown, this script will not generate XSD that the XSD.EXE tool likes. Don't ask me why, it just doesn't (and that goes for generating typed DataSets too). There are ways to make it work, but would you want to when XSDObjectGen does all the right things in terms of creating real platform XML and dealing well with minimal update sets? Oh yeah, the classes work well with the ColumnSetXml parameter on Retrieve methods. I've even created XSD that represents collections of platform entities and serialized those properly.

    As usual, nothing you've read here is remotely supported, and if you call support they'll likely have no idea what you're talking about. They might even ask why you're running SQL scripts in the metadata. I won't support this either. So don't ask. I'm making this available because it's something that needed to happen and never did. The bug with the bad XSD was found the day we RTM'd v1.0 and we never looked back (who'd ever want the schemas anyway, aren't XML strings self-describing...)


    public static string ToString(object o)

    {

        StringBuilder sb = new StringBuilder();

        XmlSerializer serializer = new XmlSerializer(o.GetType());

        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();

     

        ns.Add("", "");

        TextWriter writer = new StringWriter(sb);

        serializer.Serialize(writer, o, ns);

     

        return sb.ToString();

    }

     

    public static object ToObject(Type t, string s)

    {

        XmlSerializer serializer = new XmlSerializer(t);

        TextReader reader = new StringReader(s);

     

        object o = null;

        try

        {

            o = serializer.Deserialize(reader);

        }

        catch (Exception e)

        {

            throw new Exception("Failed to convert XML to object", e);

        }

        return o;

    }

     

    void fooBar()

    {

        // create an account object in "client-space" and set some properties

        Microsoft.Crm.WebServices.account a1 = new Microsoft.Crm.WebServices.account();

        a1.accountcategorycode.Value = 6;

        a1.accountcategorycode.name = "Corporate";

     

        a1.creditlimit.Value = 123456.0F;

        a1.creditlimit.value = "$123,456.00";

     

        a1.creditonhold.Value = true;

        a1.creditonhold.name = "Yes";

     

        a1.createdon.type = 8;

        a1.createdon.Value = userAuth.UserId;

     

        a1.name = "This is account 1";

        a1.description = "This is my sample account....";

     

        // turn it into XML

        string xml1 = ToString(a1);

     

        Microsoft.Crm.Platform.Proxy.CRMAccount accountService =

        new Microsoft.Crm.Platform.Proxy.CRMAccount();

     

        // stuff in into the platform and get the new one back

        string xml2 = accountService.CreateAndRetrieve(userAuth, xml1);

     

        // turn the new one into an object

        Microsoft.Crm.WebServices.account a2 =

        (Microsoft.Crm.WebServices.account)ToObject(typeof(Microsoft.Crm.WebServices.account), xml2);

    }




    set nocount on

     

    declare @view table (

      idvalue int identity,

      value nvarchar(4000)

    )

     

    declare @attributeName nvarchar(50)

    declare @typeName nvarchar(50)

    declare @entityName nvarchar(50)

     

    declare @buildDate datetime

    declare @buildNumber nvarchar(20)

     

    select @buildDate = coalesce(BuildDate, getutcdate()),

           @buildNumber = cast(coalesce(MajorVersion, 1) as nvarchar) + '.' + cast(coalesce(MinorVersion, 0) as nvarchar) + '.' + cast(coalesce(BuildNumber, 0) as nvarchar)

    from BuildVersion

     

    declare entityCursor cursor for

    select LogicalName

    from Entity

    where IsIntersect = 0

      and IsSecurityIntersect = 0

      and IsLookupTable = 0

      and IsAssignment = 0

      and LogicalName not like '%activity%'

      and LogicalName != 'activitypointer'

    order by 1

     

    -- write the top-level schema tags and namespace information

    insert @view (value) values ('<?xml version="1.0" encoding="utf-8" ?>')

     

    insert @view (value) values ('<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"')

    insert @view (value) values (' targetNamespace="http://www.microsoft.com/mbs/crm/schemas/2004"')

    insert @view (value) values (' xmlns:tns="http://www.microsoft.com/mbs/crm/schemas/2004"')

     

    insert @view (value) values (' elementFormDefault="unqualified" ')

    insert @view (value) values (' attributeFormDefault="unqualified" >')

    insert @view (value) values ('')

    insert @view (value) values (' <xsd:import namespace="http://www.w3.org/XML/1998/namespace"')

    insert @view (value) values (' schemaLocation="http://www.w3.org/2001/xml.xsd" />')

    insert @view (value) values ('')

     

    insert @view (value) values ('')

    insert @view (value) values (' <xsd:annotation>')

    insert @view (value) values (' <xsd:documentation xml:lang="en">')

    insert @view (value) values (' Copyright (c) ' + cast(year(getutcdate()) as nvarchar) + ' Microsoft Corp. All rights reserved.')

    insert @view (value) values (' DO NOT EDIT - Schema automatically generated ')

    insert @view (value) values (' Built on : ' + cast(@buildDate as nvarchar))

    insert @view (value) values (' Version : ' + cast(@buildNumber as nvarchar))

    insert @view (value) values (' </xsd:documentation>')

    insert @view (value) values (' </xsd:annotation>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:simpleType name="uniqueidentifier">')

    insert @view (value) values (' <xsd:restriction base="xsd:string">')

    insert @view (value) values (' <xsd:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}" /> ')

    insert @view (value) values (' </xsd:restriction>')

    insert @view (value) values (' </xsd:simpleType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="keyType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="principalType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="ownerType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="customerType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="lookupType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:string">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string" />')

    insert @view (value) values (' <xsd:attribute name="type" type="xsd:int" use="required" />')

    insert @view (value) values (' <xsd:attribute name="dsc" type="xsd:int" />')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="picklistType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="booleanType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:boolean">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="moneyType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="numberType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="decimalType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="floatType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:float">')

    insert @view (value) values (' <xsd:attribute name="value" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="dateTimeType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:dateTime">')

    insert @view (value) values (' <xsd:attribute name="date" type="xsd:string"/>')

    insert @view (value) values (' <xsd:attribute name="time" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="statusType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    insert @view (value) values (' <xsd:complexType name="stateType">')

    insert @view (value) values (' <xsd:simpleContent>')

    insert @view (value) values (' <xsd:extension base="xsd:int">')

    insert @view (value) values (' <xsd:attribute name="name" type="xsd:string"/>')

    insert @view (value) values (' </xsd:extension>')

    insert @view (value) values (' </xsd:simpleContent>')

    insert @view (value) values (' </xsd:complexType>')

    insert @view (value) values ('')

     

    -- open the cursor

    open entityCursor

    fetch entityCursor into @entityName

     

    while @@fetch_status = 0

    begin

      insert @view (value) values (' <xsd:complexType name="' + @entityName + '">')

      insert @view (value) values (' <xsd:sequence>')

     

      declare attributeCursor cursor for

      select Attribute.LogicalName,

      case

        when AttributeTypes.XmlType in ('dateTime.tz', 'datetime') then 'tns:dateTimeType'

        when AttributeTypes.XmlType = 'Boolean' then 'tns:booleanType'

     

        when AttributeTypes.XmlType = 'picklist' then 'tns:picklistType'

        when AttributeTypes.XmlType = 'state' then 'tns:stateType'

        when AttributeTypes.XmlType = 'status' then 'tns:statusType'

     

        when AttributeTypes.XmlType = 'primarykey' then 'tns:keyType'

        when AttributeTypes.XmlType = 'customer' then 'tns:customerType'

        when AttributeTypes.XmlType = 'lookup' then 'tns:lookupType'

        when AttributeTypes.XmlType = 'owner' then 'tns:ownerType'

     

        when AttributeTypes.XmlType = 'uuid' then 'tns:keyType'

     

        when AttributeTypes.XmlType = 'timezone' then 'xsd:int'

        when AttributeTypes.XmlType in ('integer', 'int', 'bigint', 'smallint', 'tinyint') then 'tns:numberType'

        when AttributeTypes.Description = 'money' then 'tns:moneyType'

        when AttributeTypes.Description = 'decimal' then 'tns:decimalType'

        when AttributeTypes.Description = 'float' then 'tns:floatType'

     

        else 'xsd:' + AttributeTypes.XmlType

      end

      from Entity join Attribute on (Entity.EntityId = Attribute.EntityId)

      join AttributeTypes on (Attribute.AttributeTypeId = AttributeTypes.AttributeTypeId)

      where Entity.LogicalName = @entityName

        and (Attribute.ValidForReadAPI = 1 or Attribute.ValidForUpdateAPI = 1 or Attribute.ValidForCreateAPI = 1)

        and Attribute.AttributeOf is NULL

        and Attribute.AggregateOf is NULL

      order by Attribute.LogicalName

     

      open attributeCursor

      fetch attributeCursor into @attributeName, @typeName

     

      while @@fetch_status = 0

      begin

        insert @view (value) values (' <xsd:element name="' + @attributeName + '" type="' + @typeName + '" />')

        fetch attributeCursor into @attributeName, @typeName

      end

     

      close attributeCursor

      deallocate attributeCursor

     

      insert @view (value) values (' </xsd:sequence>')

      insert @view (value) values (' </xsd:complexType>')

     

      fetch entityCursor into @entityName

     

      if @@fetch_status = 0

      begin

        insert @view (value) values ('')

      end

    end

     

    close entityCursor

    deallocate entityCursor

     

    insert @view (value) values ('</xsd:schema>')

     

    select value

    from @view order by idvalue



    Bonus if you've read this far. To get collection classes too, add some more SQL like this before the second fetch, and make sure you add a column for the collection name.


    if @collectionname is not null

    begin

      insert @view (value) values ('')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

      insert @view (value) values (' ')

    end

     

     

  • Michaeljon Miller

    Web services aren’t distributed objects

    • 7 Comments

    Why do we continue to try forcing a distributed object model into the world of web services? Nowhere in the name is there any hint that web service architecture has anything to do with distributed objects. Nowhere is there an implication that there are even objects involved in web service architecture. Sure, underneath the covers, in the private service implementation there are certain to be some kind of objects, if the service was written any time in the last decade or so.

    But objects on the outside? Where did that notion ever come into play? Werner Vogel did a great piece [1] back in December 2003 that tried to dispel some of the myths that keep creeping in. I keep reading that article, and talking to developers around MBS, to see why web services keep getting lumped in with distributed object architectures. The only reason I can come up with is that the development community has had OO on the collective brain for quite a while and anything that looks like it might fit into the CORBA, DCOM, or RMI mold is dismissed as another distributed OO attempt. Web services will never take off until we start thinking in terms of services.

    It’s really hard to do that though. Objects have all those nice characteristics that are pounded into our heads in school: data abstraction, information hiding, loose-coupling, separation of code from data… Wait a minute here. Aren’t those all things that web services provide too? Maybe that’s the reason we keep getting wrapped around the axle when we try to build new systems in a web services world. We want all that goodness, and the thing we’re taught to use is OO, so it must be the case that OO and web services are equivalent architectures. What can we do about it though?

    Well, one of the key tenets of OO is that we should only ever deal with an object via its public interface. That means we shouldn’t worry about what’s inside the object, and we probably never should be shown what’s inside the object. That’s all good. What if we just change all occurrences of “object” in the preceding sentence with “service”? Close, but no cigar. Why? Because there’s a programming model difference between objects and services. When I create a solution using OO techniques I get to assume that I can create an object instance and hang on to it for as long as I want. I might even decide to pass it to another object instance for consumption. As the developer I probably get to hold on to precious resources, even though I don’t know that I’m doing it, because I get to control the object lifetime.

    Hmm. That doesn’t sound right. What if one of those precious resources is a row in a table somewhere? (I know, it’s pretty hard to hold on to a row, but it’s not too hard to create a database transaction that holds a row.) Maybe the issue is that there are two levels of processing involved. Maybe the Smalltalk guys had the terminology right. Maybe what we send to an object is a message. That sounds right. Now, if we replace “object” with “service” we get to send messages to services. We don’t get to hold on to the “object” because it’s not there any more, it’s been replaced by a service.

    Ok, so this is a lot of rambling. Is there a point? Sort of. The point is that we need to change the way we think about the programming model when we talk about building distributed systems using web services. We don’t get to control object lifetimes, we don’t get to hold on to precious resources, we don’t get to control transactions across multiple services. Instead, we get to ask a service to go do something for us and we let the infrastructure handle the how.

    What’s the tie-in to CRM? Only that there are no distributed objects in the product. It’s all web services, and the “objects” are simply data structures that barely pass for documents. Why all that XML in the first two releases? Well, that was the technology available to us when we started and that technology solved the problem of getting programmers away from thinking that they needed to create objects and call methods.

    [1] http://weblogs.cs.cornell.edu/AllThingsDistributed/archives/000343.html

  • Michaeljon Miller

    Removing unwanted attributes

    • 1 Comments

    Any old post moving over to MSDN where I can find it.

    First, let me start off by saying that this is a completely unsupported process. There are a number of things that can go wrong and probably will go wrong, and if you break it there's nothing the support team can do to help you.

    Second, if you're using the Outlook client and have replication set up, this process gets a lot more complicated, and I'm not going to be able to help out either.

    So, let's say you've gone into the schema manager and you've added an attribute, and let's assume you've done something you didn't want to do (like create it with the wrong type, or the wrong size, or the wrong name...). You probably want to remove that attribute. I can imagine there are other scenarios where removing an attribute might be a really helpful thing to do.

    Well, removing attributes is a fairly straightforward thing to do. First, you need to make sure you're not using the attribute anywhere. If you are, and you remove it from the metadata, then the product will appear to break when whatever tool is using the attribute tries to access it. Attributes can be consumed in a number of places - the web application uses then obviously; reporting will use them sometimes; integration might use them; and the Outlook client might too.

    Make sure you've removed the attribute from the forms first, that way you can test out what you've done. Use the tools supplied by the product, they're really good and can do the right thing.

    Here comes the part where things might break. You need to edit the Attribute table in the metadata database. Find the attribute you want to remove (make sure it's the right one and bound to the entity you're expecting it to be bound to). Keep the attribute id handy because you're going to need it. You need to remove all the references to the attribute first - look in AttributeMap for references to the attribute on both sides and remove those references (preferably do this from the mapping tool in schema manager - it knows the right incantations, and if this step breaks you're still in supported territory, I think).

    I'm also assuming that you're not doing anything with an attribute used in any relationship because that will flat out break stuff that you can't fix. This means that AttributeOf, SortAttribute, TypeAttribute, and AggregrateOf will all be NULL for the attribute you're removing. It also means that you won't find the attribute used in KeyAttributes anywhere.

    Once you've got all that cleaned up, you can delete the row from Attribute. There are some limitations to what you can delete, but since you're only deleting attributes you added, right, you shouldn't have a problem. However, it might be educational to talk about some attribute characteristics that describe attributes that just can't be removed. Any attribute with RequiredForGrid, IsPKAttribute, or RequiresPlatformAuthorization == 1 have to stay. Any attribute with IsNullable == 0 have to stay as well - they are required by the product. By the way, if you find your attribute in KeyAttributes or referenced by name in GeneratedJoins (or JoinAttributes), then don't do this - that attribute is being used in a relationship and removing it will change the shape of the entity graph - and you're going to break the software.

    Once you've deleted the attribute reference from the metadata, you're almost done. You just need to ALTER TABLE on the *Base table to drop the column (if you don't know how to do that, you shouldn't be trying any of this stuff...) and then regenerate the views and triggers. If you look at the stored procedures in the metabase you'll find one called p_genSpecificViewAndTrigger. It takes the *Base name of the table that holds the entity as a parameter and generates a script that you can use to recreate the views and triggers. It won't recreate them for you, you need to run it, copy the resulting script, and run that script in the MSCRM database.

    Now, if you're using replication you've got all kinds of other issues do deal with. You need to drop the column from the publication (look in SQL BOL for information on sp_repldropcolumn).

    One thing you need to do now is reset the cache. In fact, you should probably have the application shut down while you're doing all this stuff. Simply do an IISRESET on all the web servers that host MS-CRM - that'll definitely flush the metadata cache from the platform.

    This might look something like:


    declare @attributeid uniqueidentifier

    select @attributeid = a.attributeid

    from attribute a join entity e on a.entityid = e.entityid

    where e.name = 'contact'

      and a.name = 'yomifullname'

     

    -- check that the attribute isn't used anywhere

    if exists (select * from keyattributes where attributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from keyattributes where referencedattributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from attributemap where sourceattributeid = ...)

    raiserror

     

    -- check that the attribute isn't used anywhere

    if exists (select * from attributemap where targetattributeid = ...)

    raiserror

     

    if exists (select *

               from attribute

               where (ispkattribute = 1

                      or isrequiredforgrid = 1

                      or requiresplatformauthorization = 1

                      or isnullable = 0)

                 and attributeid = @attributeid)

    raiserror

     

    delete from attribute

    where attributeid = @attributeid

     

    p_genSpecificViewAndTriggers 'ContactBase'

    xp_cmdshell 'IISRESET'



    Like I said though, this is all unsupported stuff and isn't typically something you want to do, but in the off chance that you've done something, like put in an attribute that blows out the 8k row size limit, and you want to fix the problem, then this procedure will probably help out a bit. Most likely I've missed a number of steps in the process, probably the important ones around removing the attribute from all the replication infrastructure (and there's a lot of it).

    As a reminder, if you remove any of the attributes that we shipped with the product you're probably going to break during the upgrade process. If V2 suddenly decides that one of those ill-named empty columns from V1 look useful for a feature, it might get used, and if it's not there, upgrade might not work. I haven't seen the final designs for V2 around the 8k limit but I've heard that there's a lot of work in this area - your columns might move around on you during the upgrade too. So, just careful, and be willing to put those columns back if you’re upgrade testing fails (you do plan on doing upgrade testing when the product ships, right?).

     

Page 1 of 1 (7 items)