locked
CRM 4.0: Looking for sample plugin to pass values from CRM to an external database RRS feed

  • Question

  • Can anyone provide me a sample of either how to pass a crm value to a stored proceeure

    or select into an external data source?

    This would be after save of a custom entity.

    Thanks,, I've been trying all day to get a trigger to do this and it bombs as soon as it writes data externally.


    David Withers
    Wednesday, August 3, 2011 10:16 PM

All replies

  • I would create a totally separate .net assembly project.

    Add your connection information etc in the web.config.

    Write a function to call your stored procedure 

    compile the solution into a  dll.

    Add this to your plugin solution and call the function.

    Compile the plugin solution.

     

    Register your plugin on post create or post update event

    before you register this plugin add your custom dll to GAC.

     

    I hope this helps


    Amreek singh Senior CRM Consultant CDC Praxa Sydney,Australia http://mscrmshop.blogspot.com/
    • Proposed as answer by Jehanzeb.Javeed Thursday, August 4, 2011 10:15 AM
    • Unproposed as answer by David Withers Thursday, August 4, 2011 3:11 PM
    Thursday, August 4, 2011 3:09 AM
  • Hi David,

    If you want to use Java Script to call external DB stored proc then its possible but its not recommended. It will open a security hole for that external DB. Yes, you can use triggers to access external DB from CRM database but you need to link these DB's together. Here is the guide to link server up:

    http://msdn.microsoft.com/en-us/library/ms188279.aspx

    http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx 

    After servers are linked up; you can easily pass on values and make a call to a stored proceduer of second DB.

    I hope its helpful.

    Regards,


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" Thanks, Imran Abbasi
    • Proposed as answer by Imran I Abbasi Thursday, August 4, 2011 3:23 AM
    Thursday, August 4, 2011 3:23 AM
  • Servers are linked and if I change my trigger to use the GUID rather than From Inserted,,, the trigger works fine.  The problem is that the CRM record never saves if the trigger is written After Insert.......
    David Withers
    Thursday, August 4, 2011 3:12 PM
  • Hi David,

    Can you please provide us trigger code? And I didn't get 'From Inserted'.

    Regards, 


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" Thanks, Imran Abbasi
    Friday, August 5, 2011 12:14 AM
  • I changed the remote server name and database for online posting
    GO
    /****** Object: Trigger [dbo].[PushNewGroups]  Script Date: 08/04/2011 09:22:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		David Withers
    -- Create date: 08/02/11
    -- Description:	Push groups from CRM to eligibility
    -- =============================================
    ALTER TRIGGER [dbo].[PushNewGroups] 
      ON [dbo].[New_divisionExtensionBase]
      After Insert
      
    AS 
    BEGIN
    	DECLARE
    		@CRMID AS uniqueidentifier,
    		@Imported as int,
    		@ClientID varchar (15),
    		@GroupID varchar(15),
    		@GroupName varchar(50),
    		@GroupEffectiveDate varchar(8),
    		@GroupTerminationDate varchar(8),
    		@CoverageEffectiveDate varchar(8),
    		@CoverageStrategyID varchar(10),
    		@AddressLine1 varchar(20),
    		@AddressLine2 varchar(20),
    		@City varchar(18),
    		@State varchar(2),
    		@ZipCode varchar(9),
    		@PhoneNumber varchar(10),
    		@Fax varchar(10),
    		@DateFilledtoDateSubmittedDMR varchar(3),
    		@SendToClaimsSystem varchar(1),
    		@AnniversaryMonth varchar(2),
    		@AnniversaryDay varchar(2)
    
    	SET NOCOUNT ON -- You might want to comment this out, just to see what you get
    
    	SELECT
    		@CRMID = New_DivisionID,
    		@Imported = new_imported
    	FROM Inserted
    	--From New_divisionExtensionBase Where New_divisionId = '06b6fabe-13be-e011-96f6-003048ca3615'
    
    	IF @Imported = 1
    		BEGIN
    			SELECT
    				@ClientID = GD.New_ClientPricingIDName
    				,@GroupID = GD.new_divisionnumber
    				,@GroupName = GD.new_name
    				,@GroupEffectiveDate = CONVERT(varchar(8),ISNULL(GD.new_effectivedate,''), 112)
    				,@GroupTerminationDate = CONVERT(varchar(8),ISNULL(GD.new_terminationdate,'99991231'), 112)
    				,@CoverageEffectiveDate = CONVERT(varchar(8),ISNULL(BP.New_EffectiveDate,''), 112)
    				,@CoverageStrategyID = GD.new_coveragestrategyidname
    				,@AddressLine1 = GD.new_address1
    				,@AddressLine2 = GD.new_address2
    				,@City = GD.new_city
    				,@State = GD.new_state
    				,@ZipCode = GD.new_zip
    				,@PhoneNumber = GD.new_businessphone
    				,@Fax = GD.new_fax 
    				,@DateFilledtoDateSubmittedDMR = ISNULL(GD.new_datefilledtodatesubmitteddmr,365)
    				,@SendToClaimsSystem = 'Y'
    				,@AnniversaryMonth = CONVERT(varchar(2),ISNULL(PA.New_AnniversaryMonth,''))
    				,@AnniversaryDay = ''
    			FROM
    				New_Division GD
    				----
    				LEFT OUTER JOIN
    				New_GroupAccount PA
    				ON GD.New_ParentGroupId = PA.New_groupaccountId
    				----
    				LEFT OUTER JOIN
    				New_BenefitPlan BP
    				ON GD.New_CoverageStrategyId = BP.New_BenefitPlanId
    			WHERE new_divisionid = @CRMID 
    
    			SET @AddressLine1 = ISNULL(@AddressLine1, '')
    			SET @AddressLine2 = ISNULL(@AddressLine2, '')
    			SET @City = ISNULL(@City, '')
    			SET @State = ISNULL(@State, '')
    			SET @ZipCode = ISNULL(@ZipCode, '')
    			SET @PhoneNumber = ISNULL(@PhoneNumber, '')
    			SET @State = ISNULL(@State, '')
    			SET @Fax = ISNULL(@Fax, '')
    			SET @AnniversaryMonth = ISNULL(@AnniversaryMonth, '')
    			SET @AnniversaryDay = ISNULL(@AnniversaryDay, '')
    			--IF @SendToClaimsSystem = 'N' SET @SendToClaimsSystem = 'Y' ELSE SET @SendToClaimsSystem = 'N'
    
    			EXEC Server.Database.dbo.sp_GroupSearchAddTPAGroupTx 
    				@ClientID ,
    				@GroupID ,
    				@GroupName ,
    				@GroupEffectiveDate ,
    				@GroupTerminationDate ,
    				@CoverageEffectiveDate ,
    				@CoverageStrategyID ,
    				@AddressLine1 ,
    				@AddressLine2 ,
    				@City ,
    				@State ,
    				@ZipCode ,
    				@PhoneNumber ,
    				@Fax ,
    				@DateFilledtoDateSubmittedDMR ,
    				@SendToClaimsSystem ,
    				@AnniversaryMonth ,
    				@AnniversaryDay 
    		END
    END
    
    


    David Withers
    Friday, August 5, 2011 4:35 PM