locked
How to do bulk update using sql script in CRM 3.0 (with CrmService)? RRS feed

  • Question

  • I want to create a way to do a bulk update to the crm database (adding new orders, based on previous year's orders that meet certain conditons).

     

    I set up the CRM 3.0 SDK with crmservice.asmx and metadataService.asmx and installed these in Visual Studio Express web developer 2005 as WebReferences.

     

    I have an sql script that I want to test (below) and expand however I do not find how I can use this in CRM? Need to convert it to FetchXml? If not how to use the sql script directly? If yes how would that conversion be done? Syntax?

     

     

     

    Code Snippet

    with step1 as (

    select newid() as new_order_id,ttt.*,

    tttt.New_Hulpabonnement, tttt.New_Automatischeincasso

    from dbo.SalesOrderExtensionBase t

    inner join dbo.SalesOrderBase tt

    on t.SalesOrderId=tt.SalesOrderId

    inner join dbo.SalesOrderBase ttt

    on tt.ContactId=ttt.ContactId

    inner join dbo.SalesOrderExtensionBase tttt

    on ttt.SalesOrderId=tttt.SalesOrderId

    where t.New_Hulpabonnement=2 and ttt.Name='hulp 2008'

    ),

    step2 as (

    select t.*,

    newid() as new_order_line_id,

    tt.SalesOrderDetailId,

    tt.SalesRepId,

    tt.IsProductOverridden,

    tt.IsCopied,

    tt.DeletionStateCode as tt_DeletionStateCode,

    tt.QuantityShipped,

    tt.LineItemNumber,

    tt.QuantityBackordered,

    tt.UoMId,

    tt.QuantityCancelled,

    tt.ProductId,

    tt.RequestDeliveryBy as tt_RequestDeliveryBy,

    tt.Quantity,

    tt.PricingErrorCode as tt_PricingErrorCode,

    tt.ManualDiscountAmount,

    tt.ProductDescription,

    tt.VolumeDiscountAmount,

    tt.PricePerUnit,

    tt.BaseAmount,

    tt.ExtendedAmount,

    tt.Description as tt_Description,

    tt.IsPriceOverridden,

    tt.ShipTo_Name as tt_ShipTo_Name,

    tt.Tax,

    tt.CreatedOn as tt_CreatedOn, tt.ShipTo_Line1 as tt_ShipTo_Line1,

    tt.CreatedBy as tt_CreatedBy, tt.ModifiedBy as tt_ModifiedBy,

    tt.ShipTo_Line2 as tt_ShipTo_Line2, tt.ShipTo_Line3 as tt_ShipTo_Line3,

    tt.ModifiedOn as tt_ModifiedOn,

    tt.ShipTo_City as tt_ShipTo_City, tt.ShipTo_StateOrProvince as tt_ShipTo_StateOrProvince,

    tt.ShipTo_Country as tt_ShipTo_Country, tt.ShipTo_PostalCode as tt_ShipTo_PostalCode,

    tt.WillCall as tt_WillCall, tt.ShipTo_Telephone as tt_ShipTo_Telephone,

    tt.ShipTo_Fax as tt_ShipTo_Fax,

    tt.ShipTo_FreightTermsCode as tt_ShipTo_FreightTermsCode,

    tt.ShipTo_AddressId as tt_ShipTo_AddressId,

    tt.ShipTo_ContactName as tt_ShipTo_ContactName

    from step1 t

    inner join dbo.SalesOrderDetailBase tt

    on t.SalesOrderId=tt.SalesOrderId)

    select *

    into #t

    from step2

    insert into dbo.SalesOrderBase

    (SalesOrderId,/*all related columns for order*/)

    select

    new_order_id--all related columns for order

    from #t

    insert into dbo.SalesOrderExtensionBase

    (SalesOrderId, New_Hulpabonnement, New_Automatischeincasso)

    select

    new_order_id,New_Hulpabonnement, New_Automatischeincasso

    from #t

    insert into dbo.SalesOrderDetailBase

    (SalesOrderDetailId,SalesOrderId,/*all related columns for order line */)

    select

    new_order_line_id,new_order_id--all related columns for order line (tt_ prefix)

    from #t

    drop table #t

     

     

    Friday, November 28, 2008 7:48 PM

Answers

  • Hi,

    One alternative way that i can think of is by looping through this sample code (inserting into database).

    Please refer to the "Create Message (CrmService)" section at the CRM SDK.

    hope this helps,

    hadi teo


    [C#]
    // Set up the CRM Service.
    CrmAuthenticationToken token = new CrmAuthenticationToken();
    // You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
    token.AuthenticationType = 0;
    token.OrganizationName = "AdventureWorksCycle";
     
    CrmService service = new CrmService();
    service.Url = "http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
    service.CrmAuthenticationTokenValue = token;
    service.Credentials = System.Net.CredentialCache.DefaultCredentials;

    // Create the account object.
    account account = new account();

    // Set the properties of the account object.
    account.name = "Fourth Coffee";
    account.address1_line1 = "23 Market St.";
    account.address1_city = "Sammamish";
    account.address1_stateorprovince = "MT";
    account.address1_postalcode = "99999";
    account.donotbulkemail = new CrmBoolean();
    account.donotbulkemail.Value = true;

    // Create the target object for the request.
    TargetCreateAccount target = new TargetCreateAccount();

    // Set the properties of the target object.
    target.Account = account;

    // Create the request object.
    CreateRequest create = new CreateRequest();

    // Set the properties of the request object.
    create.Target = target;

    // Execute the request.
    CreateResponse created = (CreateResponse)service.Execute(create);



    Saturday, November 29, 2008 12:30 AM
  • CrmAuthenticationToken onlt applies to CRM 4.0. For CRM 3.0 you can ignore those lines of code

    Sunday, November 30, 2008 9:06 PM
    Moderator

All replies

  • Hi,

    One alternative way that i can think of is by looping through this sample code (inserting into database).

    Please refer to the "Create Message (CrmService)" section at the CRM SDK.

    hope this helps,

    hadi teo


    [C#]
    // Set up the CRM Service.
    CrmAuthenticationToken token = new CrmAuthenticationToken();
    // You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory authentication.
    token.AuthenticationType = 0;
    token.OrganizationName = "AdventureWorksCycle";
     
    CrmService service = new CrmService();
    service.Url = "http://<servername>:<port>/mscrmservices/2007/crmservice.asmx";
    service.CrmAuthenticationTokenValue = token;
    service.Credentials = System.Net.CredentialCache.DefaultCredentials;

    // Create the account object.
    account account = new account();

    // Set the properties of the account object.
    account.name = "Fourth Coffee";
    account.address1_line1 = "23 Market St.";
    account.address1_city = "Sammamish";
    account.address1_stateorprovince = "MT";
    account.address1_postalcode = "99999";
    account.donotbulkemail = new CrmBoolean();
    account.donotbulkemail.Value = true;

    // Create the target object for the request.
    TargetCreateAccount target = new TargetCreateAccount();

    // Set the properties of the target object.
    target.Account = account;

    // Create the request object.
    CreateRequest create = new CreateRequest();

    // Set the properties of the request object.
    create.Target = target;

    // Execute the request.
    CreateResponse created = (CreateResponse)service.Execute(create);



    Saturday, November 29, 2008 12:30 AM
  • I'm taking a look at that now, see following code

     

    I changed 2007 to 2006 in http://mscrm/mscrmservices/2006/crmservice.asmx.

    However method CrmAuthenticationToken() is not defined (compilation error), also not in CrmSdk.

    Is there a newer SDK version available for CRM3.0 that I should install instead (I downloaded this from http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&displaylang=en)?

    Or is that the CRM4.0 SDK? I guess I should not install that? How to do this then in CRM3.0?

     

    By the way I started off with the create method instead of the create message since there's a remark that says that performance it better with the method-way than with the message-way.

     

    Code Snippet

    Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click

    ' Set up the CRM Service.

    Dim token As New CrmAuthenticationToken()

    ' You can use enums.cs from the SDK\Helpers folder to get the enumeration for Active Directory Authentication.

    token.AuthenticationType = 0

    token.OrganizationName = "'t Computerhuys";

    Dim service As New CrmSdk.CrmService()

    service.Url = "http://mscrm/mscrmservices/2006/crmservice.asmx";

    service.CrmAuthenticationTokenValue = token;

    service.Credentials = System.Net.CredentialCache.DefaultCredentials

    ' Create the contact object.

    Dim contact As New CrmSdk.contact()

    ' Create the properties for the contact object.

    contact.firstname = "Jesper"

    contact.lastname = "Aaberg"

    contact.address1_line1 = "23 Market St."

    contact.address1_city = "Sammamish"

    contact.address1_stateorprovince = "MT"

    contact.address1_postalcode = "99999"

    contact.donotbulkemail = New CrmSdk.CrmBoolean()

    contact.donotbulkemail.Value = True

    ' Create the contact in Microsoft Dynamics CRM.

    Dim contactGuid As Guid = service.Create(contact)

    End Sub

     

     

    Saturday, November 29, 2008 11:30 AM
  • CrmAuthenticationToken onlt applies to CRM 4.0. For CRM 3.0 you can ignore those lines of code

    Sunday, November 30, 2008 9:06 PM
    Moderator