none
分派其他user给新纪录出错(0x80044150。Generic SQL error.)CRM4 RRS feed

  • 问题

  • 用系统管理员创建一条新的纪录然后分派给当前用户,出现错误

    代码如下:

                CrmService service = GetCrmService.BuildService();
                Common common = new Common();
                Microsoft.Crm.Sdk.PropertyCollection pCollection = new Microsoft.Crm.Sdk.PropertyCollection();

                pCollection = common.AddProperty(pCollection, "zmmc_leadid", "LookupProperty", leadId);

                DynamicEntity entity = new DynamicEntity();
                entity.Name = "zmmc_guanzkhlb";
                entity.Properties = pCollection;

                //创建
                TargetCreateDynamic target = new TargetCreateDynamic();
                target.Entity = entity;
                CreateRequest create = new CreateRequest();
                create.Target = target;
                CreateResponse response = (CreateResponse)service.Execute(create);

                //分派
                common.ReAssignOwner(new Guid(userId), "zmmc_guanzkhlb", response.id);

     

    BuildService():

                CrmService service = new CrmService();

                service.Url = GetConfig("url");

                System.Net.NetworkCredential credential = new System.Net.NetworkCredential();

                credential.UserName = GetConfig("user");
                credential.Password = GetConfig("password");
                credential.Domain = GetConfig("domain");
                service.Credentials = credential;

                CrmAuthenticationToken token = new CrmAuthenticationToken();
                token.OrganizationName = GetConfig("organization");
                service.CrmAuthenticationTokenValue = token;
                return service;

     

    Trace Error:

    at CrmException..ctor(String message, Exception innerException, Int32 errorCode)
    at SoapExtensionExceptionHandlerBase.GetCrmException(Exception exception)
    at CrmExceptionHandler.Handle(Stream to, Stream from, Exception exception)
    at CompositeSoapExtensionExceptionHandler.Handle(Stream to, Stream from, Exception exception)
    at CrmAuthenticationSoapExtensionBase.ProcessMessage(SoapMessage message)
    at SoapMessage.RunExtensions(SoapExtension[] extensions, Boolean throwOnException)
    at SoapServerProtocol.WriteException(Exception e, Stream outputStream)
    at WebServiceHandler.WriteException(Exception e)
    at WebServiceHandler.Invoke()
    at WebServiceHandler.CoreProcessRequest()
    at SyncSessionlessHandler.ProcessRequest(HttpContext context)
    at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
    at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
    at ApplicationStepManager.ResumeSteps(Exception error)
    at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
    at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
    at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
    at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
    >Crm Exception: Message: , ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException: Invalid column name 'q'.
    Invalid column name 's'.
    Invalid column name 'y'.
    在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    在 Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
    在 Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
    在 Microsoft.Crm.BusinessEntities.CrmCascadeDBCollection.Collect(CascadeActionType actionType, Guid entityId, Int32 entityOtc, Guid ownerId)
    在 Microsoft.Crm.BusinessEntities.CascadeEngine.AssignDB(BusinessEntityMoniker moniker, SecurityPrincipal assignee, ExecutionContext context)

     

    platform:

    at MessageProcessor.Execute(PipelineExecutionContext context)
    at InternalMessageDispatcher.Execute(PipelineExecutionContext context)
    at ExternalMessageDispatcher.Execute(String messageName, Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, PropertyBag fields, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
    at RequestBase.Process(Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
    at RequestBase.Process(CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
    at CrmServiceInternal.Execute(RequestBase request, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId)
    at CrmService.Execute(Request request)
    at RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
    at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at LogicalMethodInfo.Invoke(Object target, Object[] values)
    at WebServiceHandler.Invoke()
    at WebServiceHandler.CoreProcessRequest()
    at SyncSessionlessHandler.ProcessRequest(HttpContext context)
    at CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
    at HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
    at ApplicationStepManager.ResumeSteps(Exception error)
    at HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext context, AsyncCallback cb, Object extraData)
    at HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
    at HttpRuntime.ProcessRequestNoDemand(HttpWorkerRequest wr)
    at ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32 iWRType)
    >MessageProcessor fail to process message 'Assign' for 'zmmc_guanzkhlb'.

    在原来的服务器上运行正常,发布到新服务器才出现此错误,两个服务器的版本都是12;最奇怪的是create出的新纪录都没有,报的错误却是assign 的错误。我试过其他的create和assign,create成功,assign错误,但是这个两个都没成功

     

    求高手指点

     

    2011年9月5日 11:37

答案

  • 找到原因,两个系统打补丁后fn_CollectForCascadeAssign函数不一样

    重新更新这个函数即可解决问题

    USE [zmmc_MSCRM]
    GO
    /****** Object:  UserDefinedFunction [dbo].[fn_CollectForCascadeAssign]    Script Date: 09/05/2011 19:41:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER function [dbo].[fn_CollectForCascadeAssign]
    (
      @root_id uniqueidentifier,
      @root_otc int,
      @isoffline int,
      @old_owner uniqueidentifier
    )
    returns @t table
    (  
       o uniqueidentifier,
       t int,
       p int default 0,
       u uniqueidentifier,
       q uniqueidentifier,
       s int,
       y bit default 0
    )
    as
    begin
    insert into @t values(@root_id,@root_otc,0,@old_owner,N'00000000-0000-0000-0000-000000000000',0,0)
    if(exists(select * from @t where t=9100))begin insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=9100))insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0 and o.ReportId not in(select o from @t where o=o.ReportId and t=9100) end
    if(exists(select * from @t where t=4400))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4402,0,o.OwningUser,c.o,c.t,1 from CampaignActivity o,@t c where o.RegardingObjectId=c.o and c.t=4400 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(4400,4406)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4401,0,o.OwningUser,c.o,c.t,1 from CampaignResponse o,@t c where o.RegardingObjectId=c.o and c.t in(4400,4406) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=1))insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0 and o.AccountId not in(select o from @t where o=o.AccountId and t=1) end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=2))insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0 and o.ContactId not in(select o from @t where o=o.ContactId and t=2) end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1088))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4209,0,o.OwningUser,c.o,c.t,1 from OrderClose o,@t c where o.SalesOrderId=c.o and c.t=1088 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1084))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4211,0,o.OwningUser,c.o,c.t,1 from QuoteClose o,@t c where o.QuoteId=c.o and c.t=1084 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=112))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4206,0,o.OwningUser,c.o,c.t,1 from IncidentResolution o,@t c where o.IncidentId=c.o and c.t=112 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=4))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_yujzdId,10077,0,o.OwningUser,c.o,c.t,1 from Zmmc_yujzd o,@t c where o.Zmmc_bumkh=c.o and c.t=4 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=10077))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_yujzdhId,10092,0,o.OwningUser,c.o,c.t,1 from Zmmc_yujzdh o,@t c where o.ZMMC_yujzdid=c.o and c.t=10077 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=4))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_DispatchingId,10046,0,o.OwningUser,c.o,c.t,1 from Zmmc_Dispatching o,@t c where o.Zmmc_bumkh=c.o and c.t=4 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.CustomerOpportunityRoleId,4503,0,o.OwningUser,c.o,c.t,1 from CustomerOpportunityRole o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10081,10107,10109,10059,10078,10097,10002,10012,10079,10040,10026,10093,10014,10057,10043,10092,10108,112,10053,10013,10009,10018,10104,10001,10037,10047,10017,10029,10110,10120,10045,10071,10025,10100,10075,1010,10015,1,10044,10019,10061,10030,10035,10095,10106,10111,10056,1090,10052,10049,10115,10004,10118,10096,10028,1084,10103,10077,10020,10082,10046,10022,10051,10031,10007,10117,10034,3,2,10024,10021,10114,10023,10000,10048,10062,10090,4,10076,10008,10010,10036,10050,10058,10091,10073,10105,10033,1088)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4214,0,o.OwningUser,c.o,c.t,1 from ServiceAppointment o,@t c where o.RegardingObjectId=c.o and c.t in(10081,10107,10109,10059,10078,10097,10002,10012,10079,10040,10026,10093,10014,10057,10043,10092,10108,112,10053,10013,10009,10018,10104,10001,10037,10047,10017,10029,10110,10120,10045,10071,10025,10100,10075,1010,10015,1,10044,10019,10061,10030,10035,10095,10106,10111,10056,1090,10052,10049,10115,10004,10118,10096,10028,1084,10103,10077,10020,10082,10046,10022,10051,10031,10007,10117,10034,3,2,10024,10021,10114,10023,10000,10048,10062,10090,4,10076,10008,10010,10036,10050,10058,10091,10073,10105,10033,1088) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10024,10079,10078,10004,10023,10037,10017,10095,10062,10035,10120,10008,10019,10115,10025,10110,10050,10029,10051,10107,10077,4400,10033,10105,10028,10056,2,10049,4,10012,10046,10075,10000,10007,10009,10047,10090,1084,10059,10026,10053,10021,10034,10076,10043,10010,10114,10045,10048,10097,10002,10096,10022,1,10082,10030,10117,10071,1088,10031,10001,1010,10081,10036,10109,10058,10052,10020,1090,10092,10118,3,112,10061,10013,10108,10093,10111,10057,10091,10015,10014,10104,10018,10100,10073,10040,10044,10103,10106)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4212,0,o.OwningUser,c.o,c.t,1 from Task o,@t c where o.RegardingObjectId=c.o and c.t in(10024,10079,10078,10004,10023,10037,10017,10095,10062,10035,10120,10008,10019,10115,10025,10110,10050,10029,10051,10107,10077,4400,10033,10105,10028,10056,2,10049,4,10012,10046,10075,10000,10007,10009,10047,10090,1084,10059,10026,10053,10021,10034,10076,10043,10010,10114,10045,10048,10097,10002,10096,10022,1,10082,10030,10117,10071,1088,10031,10001,1010,10081,10036,10109,10058,10052,10020,1090,10092,10118,3,112,10061,10013,10108,10093,10111,10057,10091,10015,10014,10104,10018,10100,10073,10040,10044,10103,10106) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10118,4402,10046,10020,10034,4406,10017,10120,1084,10082,10077,2,10012,10023,10097,3,10061,10058,10018,10001,10076,10110,1088,10026,10008,10050,10019,10103,10010,10043,10057,10030,10013,10036,10104,1090,10075,10004,10078,1,10052,10095,10021,10049,10108,10071,10106,10114,10056,10115,10092,10015,10000,10014,10022,10044,10024,10025,10059,10037,10090,10047,10107,10093,10100,10002,4,10105,10053,10031,10028,10091,10009,10062,10007,10117,10109,10033,10073,10096,1010,10051,10081,10029,10045,112,10035,10040,10111,10048,10079)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4210,0,o.OwningUser,c.o,c.t,1 from PhoneCall o,@t c where o.RegardingObjectId=c.o and c.t in(10118,4402,10046,10020,10034,4406,10017,10120,1084,10082,10077,2,10012,10023,10097,3,10061,10058,10018,10001,10076,10110,1088,10026,10008,10050,10019,10103,10010,10043,10057,10030,10013,10036,10104,1090,10075,10004,10078,1,10052,10095,10021,10049,10108,10071,10106,10114,10056,10115,10092,10015,10000,10014,10022,10044,10024,10025,10059,10037,10090,10047,10107,10093,10100,10002,4,10105,10053,10031,10028,10091,10009,10062,10007,10117,10109,10033,10073,10096,1010,10051,10081,10029,10045,112,10035,10040,10111,10048,10079) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4208,0,o.OwningUser,c.o,c.t,1 from OpportunityClose o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10075,10004,10018,1010,10021,4406,10036,10107,10023,10037,10052,10013,10079,1090,10111,10077,10044,10046,3,10062,2,10073,10110,1084,10115,10076,10059,10043,10029,10104,10096,10048,10000,10047,1,10057,10061,10049,10007,10118,10031,10105,10071,10090,10081,10051,10002,10001,10050,10056,112,10012,10034,10024,10091,10019,10092,10053,10015,10097,10022,10033,10020,10009,10045,10114,10109,10058,10082,10117,10100,10014,10106,10010,4402,4,10078,10008,10120,10035,10093,10095,10108,10030,10040,1088,10026,10025,10103,10028,10017)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4207,0,o.OwningUser,c.o,c.t,1 from Letter o,@t c where o.RegardingObjectId=c.o and c.t in(10075,10004,10018,1010,10021,4406,10036,10107,10023,10037,10052,10013,10079,1090,10111,10077,10044,10046,3,10062,2,10073,10110,1084,10115,10076,10059,10043,10029,10104,10096,10048,10000,10047,1,10057,10061,10049,10007,10118,10031,10105,10071,10090,10081,10051,10002,10001,10050,10056,112,10012,10034,10024,10091,10019,10092,10053,10015,10097,10022,10033,10020,10009,10045,10114,10109,10058,10082,10117,10100,10014,10106,10010,4402,4,10078,10008,10120,10035,10093,10095,10108,10030,10040,1088,10026,10025,10103,10028,10017) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10105,10109,10021,10028,10056,10013,3,10059,10015,10095,4402,10073,10079,10045,10076,10048,10104,10004,10034,10090,10050,10100,4,1088,10024,10014,10010,10049,10026,10017,112,10111,10047,10023,1,10103,10044,10035,10053,10096,10036,10019,10043,10031,10057,10029,10046,10110,10108,10114,10117,10018,10097,10002,4406,10022,1010,10037,10071,10051,10061,10092,10008,10106,10091,10000,10062,10107,10025,10093,10118,10052,10009,10001,1090,10078,10077,10120,10012,10033,10115,10058,10081,10007,10030,10020,10082,1084,10075,10040,2)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4204,0,o.OwningUser,c.o,c.t,1 from Fax o,@t c where o.RegardingObjectId=c.o and c.t in(10105,10109,10021,10028,10056,10013,3,10059,10015,10095,4402,10073,10079,10045,10076,10048,10104,10004,10034,10090,10050,10100,4,1088,10024,10014,10010,10049,10026,10017,112,10111,10047,10023,1,10103,10044,10035,10053,10096,10036,10019,10043,10031,10057,10029,10046,10110,10108,10114,10117,10018,10097,10002,4406,10022,1010,10037,10071,10051,10061,10092,10008,10106,10091,10000,10062,10107,10025,10093,10118,10052,10009,10001,1090,10078,10077,10120,10012,10033,10115,10058,10081,10007,10030,10020,10082,1084,10075,10040,2) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10093,10023,10107,10048,10031,10105,10073,10020,10030,10118,10109,10078,10013,10026,1088,10108,10025,3,10046,1084,10015,10058,1010,4406,10077,10092,10082,10037,10010,10004,10044,10053,10028,10075,1090,10022,10014,10043,10062,10017,2,10114,10057,10095,10040,10034,10106,10045,10008,10000,10076,10021,10104,10050,10047,10111,10079,10103,10052,10009,10110,10001,10051,10100,112,1,10049,4700,4,10056,10035,10002,10115,4402,10024,10061,10033,10059,10117,10012,10018,10029,10071,10090,10097,10120,10019,10096,10036,10081,10007,10091)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4202,0,o.OwningUser,c.o,c.t,1 from Email o,@t c where o.RegardingObjectId=c.o and c.t in(10093,10023,10107,10048,10031,10105,10073,10020,10030,10118,10109,10078,10013,10026,1088,10108,10025,3,10046,1084,10015,10058,1010,4406,10077,10092,10082,10037,10010,10004,10044,10053,10028,10075,1090,10022,10014,10043,10062,10017,2,10114,10057,10095,10040,10034,10106,10045,10008,10000,10076,10021,10104,10050,10047,10111,10079,10103,10052,10009,10110,10001,10051,10100,112,1,10049,4700,4,10056,10035,10002,10115,4402,10024,10061,10033,10059,10117,10012,10018,10029,10071,10090,10097,10120,10019,10096,10036,10081,10007,10091) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10096,10062,1084,10078,10029,10014,4402,10046,10107,10059,10008,10106,10052,10076,10092,10021,10120,10105,10045,10053,10118,10108,10081,10109,10037,10057,10044,10114,10026,10093,10004,112,10002,10071,10058,10049,10075,10035,10022,10110,10009,1,10028,10043,3,10020,10073,10007,10048,10115,10047,10019,10034,10091,10025,10013,1088,10018,10036,10100,2,10030,10050,10117,10103,10111,1010,10015,10104,10012,10056,10040,10095,4406,1090,10061,10031,10033,10023,10000,10051,10090,10077,4,10097,10082,10024,10079,10001,10017,10010)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4201,0,o.OwningUser,c.o,c.t,1 from Appointment o,@t c where o.RegardingObjectId=c.o and c.t in(10096,10062,1084,10078,10029,10014,4402,10046,10107,10059,10008,10106,10052,10076,10092,10021,10120,10105,10045,10053,10118,10108,10081,10109,10037,10057,10044,10114,10026,10093,10004,112,10002,10071,10058,10049,10075,10035,10022,10110,10009,1,10028,10043,3,10020,10073,10007,10048,10115,10047,10019,10034,10091,10025,10013,1088,10018,10036,10100,2,10030,10050,10117,10103,10111,1010,10015,10104,10012,10056,10040,10095,4406,1090,10061,10031,10033,10023,10000,10051,10090,10077,4,10097,10082,10024,10079,10001,10017,10010) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(4204,112,4207,10009,10118,10107,4,10058,10033,10010,10051,10049,10025,10100,10007,10034,4703,10000,10076,10012,10092,10079,10021,10056,10045,10093,1010,4202,10001,10028,4209,10104,10108,10110,10029,10075,10030,10013,4201,1088,10043,10111,10095,10050,10040,10048,4210,10103,10020,10017,4401,10052,10002,10097,4300,10019,10018,10014,10061,10073,4211,1084,4414,10062,10078,10106,10026,10077,10114,10008,1,10036,4206,10086,10022,10117,10096,10090,10082,4400,2,10091,4402,4208,10024,10053,4214,4212,10120,10004,10057,10059,10115,10046,10105,3,10015,10047,10071,1090,10031,10023,10081,10044,10035,10037,10109)))begin insert into @t(o,t,p,u,q,s,y) select o.AnnotationId,5,0,o.OwningUser,c.o,c.t,1 from Annotation o,@t c where o.ObjectId=c.o and c.t in(4204,112,4207,10009,10118,10107,4,10058,10033,10010,10051,10049,10025,10100,10007,10034,4703,10000,10076,10012,10092,10079,10021,10056,10045,10093,1010,4202,10001,10028,4209,10104,10108,10110,10029,10075,10030,10013,4201,1088,10043,10111,10095,10050,10040,10048,4210,10103,10020,10017,4401,10052,10002,10097,4300,10019,10018,10014,10061,10073,4211,1084,4414,10062,10078,10106,10026,10077,10114,10008,1,10036,4206,10086,10022,10117,10096,10090,10082,4400,2,10091,4402,4208,10024,10053,4214,4212,10120,10004,10057,10059,10115,10046,10105,3,10015,10047,10071,1090,10031,10023,10081,10044,10035,10037,10109) and o.DeletionStateCode=0 end
    return
    end

    • 已标记为答案 Pring 2012年3月5日 8:43
    2011年9月5日 11:51

全部回复

  • 找到原因,两个系统打补丁后fn_CollectForCascadeAssign函数不一样

    重新更新这个函数即可解决问题

    USE [zmmc_MSCRM]
    GO
    /****** Object:  UserDefinedFunction [dbo].[fn_CollectForCascadeAssign]    Script Date: 09/05/2011 19:41:08 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER function [dbo].[fn_CollectForCascadeAssign]
    (
      @root_id uniqueidentifier,
      @root_otc int,
      @isoffline int,
      @old_owner uniqueidentifier
    )
    returns @t table
    (  
       o uniqueidentifier,
       t int,
       p int default 0,
       u uniqueidentifier,
       q uniqueidentifier,
       s int,
       y bit default 0
    )
    as
    begin
    insert into @t values(@root_id,@root_otc,0,@old_owner,N'00000000-0000-0000-0000-000000000000',0,0)
    if(exists(select * from @t where t=9100))begin insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=9100))insert into @t(o,t,p,u,q,s,y) select o.ReportId,9100,0,o.OwningUser,c.o,c.t,1 from Report o,@t c where o.ParentReportId=c.o and c.t=9100 and o.DeletionStateCode=0 and o.ReportId not in(select o from @t where o=o.ReportId and t=9100) end
    if(exists(select * from @t where t=4400))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4402,0,o.OwningUser,c.o,c.t,1 from CampaignActivity o,@t c where o.RegardingObjectId=c.o and c.t=4400 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(4400,4406)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4401,0,o.OwningUser,c.o,c.t,1 from CampaignResponse o,@t c where o.RegardingObjectId=c.o and c.t in(4400,4406) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=1))insert into @t(o,t,p,u,q,s,y) select o.AccountId,1,0,o.OwningUser,c.o,c.t,1 from Account o,@t c where o.ParentAccountId=c.o and c.t=1 and o.DeletionStateCode=0 and o.AccountId not in(select o from @t where o=o.AccountId and t=1) end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0
    while(@@rowcount <> 0)if(exists(select * from @t where t=2))insert into @t(o,t,p,u,q,s,y) select o.ContactId,2,0,o.OwningUser,c.o,c.t,1 from Contact o,@t c where o.ParentContactId=c.o and c.t=2 and o.DeletionStateCode=0 and o.ContactId not in(select o from @t where o=o.ContactId and t=2) end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.InvoiceId,1090,0,o.OwningUser,c.o,c.t,1 from Invoice o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.SalesOrderId,1088,0,o.OwningUser,c.o,c.t,1 from SalesOrder o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1088))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4209,0,o.OwningUser,c.o,c.t,1 from OrderClose o,@t c where o.SalesOrderId=c.o and c.t=1088 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.QuoteId,1084,0,o.OwningUser,c.o,c.t,1 from Quote o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1084))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4211,0,o.OwningUser,c.o,c.t,1 from QuoteClose o,@t c where o.QuoteId=c.o and c.t=1084 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.ContractId,1010,0,o.OwningUser,c.o,c.t,1 from Contract o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.IncidentId,112,0,o.OwningUser,c.o,c.t,1 from Incident o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=112))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4206,0,o.OwningUser,c.o,c.t,1 from IncidentResolution o,@t c where o.IncidentId=c.o and c.t=112 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.LeadId,4,0,o.OwningUser,c.o,c.t,1 from Lead o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=4))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_yujzdId,10077,0,o.OwningUser,c.o,c.t,1 from Zmmc_yujzd o,@t c where o.Zmmc_bumkh=c.o and c.t=4 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=10077))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_yujzdhId,10092,0,o.OwningUser,c.o,c.t,1 from Zmmc_yujzdh o,@t c where o.ZMMC_yujzdid=c.o and c.t=10077 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=4))begin insert into @t(o,t,p,u,q,s,y) select o.Zmmc_DispatchingId,10046,0,o.OwningUser,c.o,c.t,1 from Zmmc_Dispatching o,@t c where o.Zmmc_bumkh=c.o and c.t=4 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=1))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.AccountId=c.o and c.t=1 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=2))begin insert into @t(o,t,p,u,q,s,y) select o.OpportunityId,3,0,o.OwningUser,c.o,c.t,1 from Opportunity o,@t c where o.ContactId=c.o and c.t=2 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.CustomerOpportunityRoleId,4503,0,o.OwningUser,c.o,c.t,1 from CustomerOpportunityRole o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10081,10107,10109,10059,10078,10097,10002,10012,10079,10040,10026,10093,10014,10057,10043,10092,10108,112,10053,10013,10009,10018,10104,10001,10037,10047,10017,10029,10110,10120,10045,10071,10025,10100,10075,1010,10015,1,10044,10019,10061,10030,10035,10095,10106,10111,10056,1090,10052,10049,10115,10004,10118,10096,10028,1084,10103,10077,10020,10082,10046,10022,10051,10031,10007,10117,10034,3,2,10024,10021,10114,10023,10000,10048,10062,10090,4,10076,10008,10010,10036,10050,10058,10091,10073,10105,10033,1088)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4214,0,o.OwningUser,c.o,c.t,1 from ServiceAppointment o,@t c where o.RegardingObjectId=c.o and c.t in(10081,10107,10109,10059,10078,10097,10002,10012,10079,10040,10026,10093,10014,10057,10043,10092,10108,112,10053,10013,10009,10018,10104,10001,10037,10047,10017,10029,10110,10120,10045,10071,10025,10100,10075,1010,10015,1,10044,10019,10061,10030,10035,10095,10106,10111,10056,1090,10052,10049,10115,10004,10118,10096,10028,1084,10103,10077,10020,10082,10046,10022,10051,10031,10007,10117,10034,3,2,10024,10021,10114,10023,10000,10048,10062,10090,4,10076,10008,10010,10036,10050,10058,10091,10073,10105,10033,1088) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10024,10079,10078,10004,10023,10037,10017,10095,10062,10035,10120,10008,10019,10115,10025,10110,10050,10029,10051,10107,10077,4400,10033,10105,10028,10056,2,10049,4,10012,10046,10075,10000,10007,10009,10047,10090,1084,10059,10026,10053,10021,10034,10076,10043,10010,10114,10045,10048,10097,10002,10096,10022,1,10082,10030,10117,10071,1088,10031,10001,1010,10081,10036,10109,10058,10052,10020,1090,10092,10118,3,112,10061,10013,10108,10093,10111,10057,10091,10015,10014,10104,10018,10100,10073,10040,10044,10103,10106)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4212,0,o.OwningUser,c.o,c.t,1 from Task o,@t c where o.RegardingObjectId=c.o and c.t in(10024,10079,10078,10004,10023,10037,10017,10095,10062,10035,10120,10008,10019,10115,10025,10110,10050,10029,10051,10107,10077,4400,10033,10105,10028,10056,2,10049,4,10012,10046,10075,10000,10007,10009,10047,10090,1084,10059,10026,10053,10021,10034,10076,10043,10010,10114,10045,10048,10097,10002,10096,10022,1,10082,10030,10117,10071,1088,10031,10001,1010,10081,10036,10109,10058,10052,10020,1090,10092,10118,3,112,10061,10013,10108,10093,10111,10057,10091,10015,10014,10104,10018,10100,10073,10040,10044,10103,10106) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10118,4402,10046,10020,10034,4406,10017,10120,1084,10082,10077,2,10012,10023,10097,3,10061,10058,10018,10001,10076,10110,1088,10026,10008,10050,10019,10103,10010,10043,10057,10030,10013,10036,10104,1090,10075,10004,10078,1,10052,10095,10021,10049,10108,10071,10106,10114,10056,10115,10092,10015,10000,10014,10022,10044,10024,10025,10059,10037,10090,10047,10107,10093,10100,10002,4,10105,10053,10031,10028,10091,10009,10062,10007,10117,10109,10033,10073,10096,1010,10051,10081,10029,10045,112,10035,10040,10111,10048,10079)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4210,0,o.OwningUser,c.o,c.t,1 from PhoneCall o,@t c where o.RegardingObjectId=c.o and c.t in(10118,4402,10046,10020,10034,4406,10017,10120,1084,10082,10077,2,10012,10023,10097,3,10061,10058,10018,10001,10076,10110,1088,10026,10008,10050,10019,10103,10010,10043,10057,10030,10013,10036,10104,1090,10075,10004,10078,1,10052,10095,10021,10049,10108,10071,10106,10114,10056,10115,10092,10015,10000,10014,10022,10044,10024,10025,10059,10037,10090,10047,10107,10093,10100,10002,4,10105,10053,10031,10028,10091,10009,10062,10007,10117,10109,10033,10073,10096,1010,10051,10081,10029,10045,112,10035,10040,10111,10048,10079) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t=3))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4208,0,o.OwningUser,c.o,c.t,1 from OpportunityClose o,@t c where o.OpportunityId=c.o and c.t=3 and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10075,10004,10018,1010,10021,4406,10036,10107,10023,10037,10052,10013,10079,1090,10111,10077,10044,10046,3,10062,2,10073,10110,1084,10115,10076,10059,10043,10029,10104,10096,10048,10000,10047,1,10057,10061,10049,10007,10118,10031,10105,10071,10090,10081,10051,10002,10001,10050,10056,112,10012,10034,10024,10091,10019,10092,10053,10015,10097,10022,10033,10020,10009,10045,10114,10109,10058,10082,10117,10100,10014,10106,10010,4402,4,10078,10008,10120,10035,10093,10095,10108,10030,10040,1088,10026,10025,10103,10028,10017)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4207,0,o.OwningUser,c.o,c.t,1 from Letter o,@t c where o.RegardingObjectId=c.o and c.t in(10075,10004,10018,1010,10021,4406,10036,10107,10023,10037,10052,10013,10079,1090,10111,10077,10044,10046,3,10062,2,10073,10110,1084,10115,10076,10059,10043,10029,10104,10096,10048,10000,10047,1,10057,10061,10049,10007,10118,10031,10105,10071,10090,10081,10051,10002,10001,10050,10056,112,10012,10034,10024,10091,10019,10092,10053,10015,10097,10022,10033,10020,10009,10045,10114,10109,10058,10082,10117,10100,10014,10106,10010,4402,4,10078,10008,10120,10035,10093,10095,10108,10030,10040,1088,10026,10025,10103,10028,10017) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10105,10109,10021,10028,10056,10013,3,10059,10015,10095,4402,10073,10079,10045,10076,10048,10104,10004,10034,10090,10050,10100,4,1088,10024,10014,10010,10049,10026,10017,112,10111,10047,10023,1,10103,10044,10035,10053,10096,10036,10019,10043,10031,10057,10029,10046,10110,10108,10114,10117,10018,10097,10002,4406,10022,1010,10037,10071,10051,10061,10092,10008,10106,10091,10000,10062,10107,10025,10093,10118,10052,10009,10001,1090,10078,10077,10120,10012,10033,10115,10058,10081,10007,10030,10020,10082,1084,10075,10040,2)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4204,0,o.OwningUser,c.o,c.t,1 from Fax o,@t c where o.RegardingObjectId=c.o and c.t in(10105,10109,10021,10028,10056,10013,3,10059,10015,10095,4402,10073,10079,10045,10076,10048,10104,10004,10034,10090,10050,10100,4,1088,10024,10014,10010,10049,10026,10017,112,10111,10047,10023,1,10103,10044,10035,10053,10096,10036,10019,10043,10031,10057,10029,10046,10110,10108,10114,10117,10018,10097,10002,4406,10022,1010,10037,10071,10051,10061,10092,10008,10106,10091,10000,10062,10107,10025,10093,10118,10052,10009,10001,1090,10078,10077,10120,10012,10033,10115,10058,10081,10007,10030,10020,10082,1084,10075,10040,2) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10093,10023,10107,10048,10031,10105,10073,10020,10030,10118,10109,10078,10013,10026,1088,10108,10025,3,10046,1084,10015,10058,1010,4406,10077,10092,10082,10037,10010,10004,10044,10053,10028,10075,1090,10022,10014,10043,10062,10017,2,10114,10057,10095,10040,10034,10106,10045,10008,10000,10076,10021,10104,10050,10047,10111,10079,10103,10052,10009,10110,10001,10051,10100,112,1,10049,4700,4,10056,10035,10002,10115,4402,10024,10061,10033,10059,10117,10012,10018,10029,10071,10090,10097,10120,10019,10096,10036,10081,10007,10091)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4202,0,o.OwningUser,c.o,c.t,1 from Email o,@t c where o.RegardingObjectId=c.o and c.t in(10093,10023,10107,10048,10031,10105,10073,10020,10030,10118,10109,10078,10013,10026,1088,10108,10025,3,10046,1084,10015,10058,1010,4406,10077,10092,10082,10037,10010,10004,10044,10053,10028,10075,1090,10022,10014,10043,10062,10017,2,10114,10057,10095,10040,10034,10106,10045,10008,10000,10076,10021,10104,10050,10047,10111,10079,10103,10052,10009,10110,10001,10051,10100,112,1,10049,4700,4,10056,10035,10002,10115,4402,10024,10061,10033,10059,10117,10012,10018,10029,10071,10090,10097,10120,10019,10096,10036,10081,10007,10091) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(10096,10062,1084,10078,10029,10014,4402,10046,10107,10059,10008,10106,10052,10076,10092,10021,10120,10105,10045,10053,10118,10108,10081,10109,10037,10057,10044,10114,10026,10093,10004,112,10002,10071,10058,10049,10075,10035,10022,10110,10009,1,10028,10043,3,10020,10073,10007,10048,10115,10047,10019,10034,10091,10025,10013,1088,10018,10036,10100,2,10030,10050,10117,10103,10111,1010,10015,10104,10012,10056,10040,10095,4406,1090,10061,10031,10033,10023,10000,10051,10090,10077,4,10097,10082,10024,10079,10001,10017,10010)))begin insert into @t(o,t,p,u,q,s,y) select o.ActivityId,4201,0,o.OwningUser,c.o,c.t,1 from Appointment o,@t c where o.RegardingObjectId=c.o and c.t in(10096,10062,1084,10078,10029,10014,4402,10046,10107,10059,10008,10106,10052,10076,10092,10021,10120,10105,10045,10053,10118,10108,10081,10109,10037,10057,10044,10114,10026,10093,10004,112,10002,10071,10058,10049,10075,10035,10022,10110,10009,1,10028,10043,3,10020,10073,10007,10048,10115,10047,10019,10034,10091,10025,10013,1088,10018,10036,10100,2,10030,10050,10117,10103,10111,1010,10015,10104,10012,10056,10040,10095,4406,1090,10061,10031,10033,10023,10000,10051,10090,10077,4,10097,10082,10024,10079,10001,10017,10010) and o.DeletionStateCode=0 end
    if(exists(select * from @t where t in(4204,112,4207,10009,10118,10107,4,10058,10033,10010,10051,10049,10025,10100,10007,10034,4703,10000,10076,10012,10092,10079,10021,10056,10045,10093,1010,4202,10001,10028,4209,10104,10108,10110,10029,10075,10030,10013,4201,1088,10043,10111,10095,10050,10040,10048,4210,10103,10020,10017,4401,10052,10002,10097,4300,10019,10018,10014,10061,10073,4211,1084,4414,10062,10078,10106,10026,10077,10114,10008,1,10036,4206,10086,10022,10117,10096,10090,10082,4400,2,10091,4402,4208,10024,10053,4214,4212,10120,10004,10057,10059,10115,10046,10105,3,10015,10047,10071,1090,10031,10023,10081,10044,10035,10037,10109)))begin insert into @t(o,t,p,u,q,s,y) select o.AnnotationId,5,0,o.OwningUser,c.o,c.t,1 from Annotation o,@t c where o.ObjectId=c.o and c.t in(4204,112,4207,10009,10118,10107,4,10058,10033,10010,10051,10049,10025,10100,10007,10034,4703,10000,10076,10012,10092,10079,10021,10056,10045,10093,1010,4202,10001,10028,4209,10104,10108,10110,10029,10075,10030,10013,4201,1088,10043,10111,10095,10050,10040,10048,4210,10103,10020,10017,4401,10052,10002,10097,4300,10019,10018,10014,10061,10073,4211,1084,4414,10062,10078,10106,10026,10077,10114,10008,1,10036,4206,10086,10022,10117,10096,10090,10082,4400,2,10091,4402,4208,10024,10053,4214,4212,10120,10004,10057,10059,10115,10046,10105,3,10015,10047,10071,1090,10031,10023,10081,10044,10035,10037,10109) and o.DeletionStateCode=0 end
    return
    end

    • 已标记为答案 Pring 2012年3月5日 8:43
    2011年9月5日 11:51
  • 楼主V5
    过好今天!
    2011年9月19日 1:58