Question

I am trying to insert some data. It is not getting inserted in the table using transactions. Instead, it is going to the catch block. Below is sql stored procedure: GO

/****** Object:  StoredProcedure [dbo].[rml_re_GetAndStoreConsumerSubscriptionData_NEWUPS] Script Date: 05/21/2012 12:39:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--EXEC rml_re_GetAndStoreConsumerSubscriptionData_NEWUPS
ALTER PROCEDURE [dbo].[rml_re_GetAndStoreConsumerSubscriptionData_NEWUPS]  

AS
BEGIN  
 DECLARE @currenttime datetime  
 SET @currenttime= getdate()  
 INSERT INTO rml_re_consumerdatasync_log VALUES( rml_re_GetAndStoreConsumerSubscriptionData ,@currenttime,null, started ,@currenttime,  ,getdate(),null);  
  BEGIN TRY  
    BEGIN TRANSACTION PULLCONSUMERDATA  
        DECLARE @dbServerName VARCHAR(50),@databaseName VARCHAR(50),@dbUserName VARCHAR(50),@query VARCHAR(MAX)    
        SELECT  @dbServerName=dbservername,@databaseName=dbname,@dbUserName=dbusername FROM rml_re_applications   
        WHERE id=(SELECT configvalue FROM rml_re_configuration WHERE configname= CRMApplicationID )

        -- Removes all old data before insertion.  
        ALTER TABLE [dbo].[rml_re_consumermarkets] DROP CONSTRAINT [FK_rml_re_consumermarkets_rml_re_consumercrops]
        ALTER TABLE [dbo].[rml_re_consumermarkets] DROP CONSTRAINT [FK_rml_re_ConsumerSubscriptionMarket_rml_re_ConsumerSubscriptionData]
        ALTER TABLE [dbo].[rml_re_consumercrops] DROP CONSTRAINT [FK_rml_re_ConsumerSubscriptionCrops_rml_re_ConsumerSubscriptionData]

        TRUNCATE TABLE rml_re_consumermarkets
        TRUNCATE TABLE rml_re_consumercrops
        TRUNCATE TABLE rml_re_ConsumerSubscriptionData

    ALTER TABLE [dbo].[rml_re_consumercrops]  WITH NOCHECK ADD  CONSTRAINT [FK_rml_re_ConsumerSubscriptionCrops_rml_re_ConsumerSubscriptionData] FOREIGN KEY([subscriptionid])
    REFERENCES [dbo].[rml_re_ConsumerSubscriptionData] ([id])
    ALTER TABLE [dbo].[rml_re_consumermarkets]  WITH NOCHECK ADD  CONSTRAINT [FK_rml_re_ConsumerSubscriptionMarket_rml_re_ConsumerSubscriptionData] FOREIGN KEY([subscriptionid])
    REFERENCES [dbo].[rml_re_ConsumerSubscriptionData] ([id])
    ALTER TABLE [dbo].[rml_re_consumermarkets]  WITH CHECK ADD  CONSTRAINT [FK_rml_re_consumermarkets_rml_re_consumercrops] FOREIGN KEY([cropid])
    REFERENCES [dbo].[rml_re_consumercrops] ([cropid])


        SET @query=  ;
        SET @query=  INSERT INTO rml_re_ConsumerSubscriptionData(id,msisdn,consumername,
                    languagetype,statecode,talukcode,districtcode,regioncode,status,
                    subscriptionstartdate,subscriptionenddate,DistributorCode,
                    package,soiltype,comp_category,AdditionalInfo) 
                            SELECT  id,msisdn,
                            case when Len(consumername) <=150 then consumername else null end  as consumername,
                            case when Len(languagetype) <=10 then languagetype else null end  as languagetype ,   
        SET @query=@query+  case when Len(statecode) <=5 then statecode else null end  as statecode ,
                            case when Len(talukcode) <=10 then talukcode else null end  as talukcode  ,
                            case when Len(districtcode) <=10 then districtcode else null end  as districtcode,
                            case when Len(regioncode) <=10 then regioncode else null end  as regioncode ,
                            case when Len(status) <=50 then status else null end  as status ,
                            subscriptionstartdate ,   
        SET @query=@query+  subscriptionenddate,
                            case when Len(DistributorCode) <=100 then DistributorCode else null end as DistributorCode ,
                            case when Len(package) <=50 then package else null end AS package ,
                            case when Len(soiltype) <=50 then soiltype else null end   as soiltype,   
        SET @query=@query+  case when Len(comp_category) <=50 then comp_category  else null end  as comp_category  ,
                            case when Len(AdditionalInfo) <=100 then AdditionalInfo  else null end  as AdditionalInfo    
        SET @query=@query+  FROM    
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  
        SET @query=@query+  WHERE crop1code is not null AND crop2code is not null  



        EXEC (@query)  
        SET @query=  ;
        -- INSERT FOR rml_re_consumercrops
        --INSER FOR PRIMARY CROP
        SET @query= INSERT INTO rml_re_consumercrops(subscriptionid,commoditycode,sowingdate,sowingweek) 
        SET @query=@query+ SELECT id,case when Len(crop1code) <=15 then crop1code  else null end  as crop1code ,sowingdate1,DATEDIFF (WEEK,sowingdate1,getdate())  
        SET @query=@query+  FROM  
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  
        SET @query=@query+  WHERE crop1code is not null AND crop2code is not null 


        EXEC (@query)  
        SET @query=  
        --INSER FOR SECONDARY CROP
        SET @query= INSERT INTO rml_re_consumercrops(subscriptionid,commoditycode,sowingdate,sowingweek) 
        SET @query=@query+ SELECT id,case when Len(crop2code) <=15 then crop2code  else null end  as crop2code  ,sowingdate2,DATEDIFF (WEEK,sowingdate2,getdate())  
        SET @query=@query+ FROM  
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  
        SET @query=@query+  WHERE crop1code is not null AND crop2code is not null 

        EXEC (@query)  
        SET @query=  

        --INSERT FOR rml_re_consumermarkets
        --INSER FOR PRIMARY CROP
        SET @query= INSERT INTO rml_re_consumermarkets(cropid,subscriptionid,marketcode,ric) 
        SET @query=@query+ SELECT CS.cropid,E.id,case when Len(M.item) <=15 then M.item  else null end AS marketcode ,case when Len( R.item) <=15 then  R.item  else null end  AS ric  
        SET @query=@query+ FROM rml_re_consumercrops CS, 
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  E   
        SET @query=@query+  OUTER APPLY fnSplit(E.Ric1, +   ,   + ) R, 
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  F  
        SET @query=@query+  OUTER APPLY fnSplit(F.MandiCode1, +   ,   + ) M  
        SET @query=@query+  WHERE F.ID = E.ID 
                AND R.ID = M.ID
                AND CS.commoditycode = E.crop1code
                AND CS.subscriptionid=E.id 
                AND CS.subscriptionid=F.id  
        SET @query=@query+  AND E.crop1code is not null AND E.crop2code is not null 
        SET @query=@query+  AND F.crop1code is not null AND F.crop2code is not null 

        EXEC (@query)  
        SET @query=  
        --INSER FOR SECONDARY CROP
        SET @query= INSERT INTO rml_re_consumermarkets(cropid,subscriptionid,marketcode,ric)  
        SET @query=@query+ SELECT CS.cropid,E.id,case when Len(M.item) <=15 then M.item  else null end AS marketcode,case when Len( R.item) <=15 then R.item  else null end AS ric   
        SET @query=@query+ FROM rml_re_consumercrops CS, 
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  E  
        SET @query=@query+  OUTER APPLY fnSplit(E.Ric2, +   ,   + ) R, 
        SET @query=@query+  [ +@dbServerName+ ]. +@databaseName+ . +@dbUserName+ .rml_re_getConsumerSubscriptionData_New  F   
        SET @query=@query+  OUTER APPLY fnSplit(F.MandiCode2, +   ,   + ) M  
        SET @query=@query+  WHERE F.ID = E.ID 
              AND R.ID = M.ID
              AND CS.commoditycode = E.crop2code
              AND CS.subscriptionid=E.id 
              AND CS.subscriptionid=F.id  
        SET @query=@query+  AND E.crop1code is not null AND E.crop2code is not null 
        SET @query=@query+  AND F.crop1code is not null AND F.crop2code is not null 

    EXEC (@query)       
    COMMIT TRANSACTION PULLCONSUMERDATA  
    UPDATE  rml_re_consumerdatasync_log SET status= completed ,endtime=getdate() WHERE logdate=@currenttime  
  END TRY  
  BEGIN CATCH  
  --  ROLLBACK TRANSACTION PULLCONSUMERDATA  

    UPDATE  rml_re_consumerdatasync_log SET status= failure ,error_info=ERROR_MESSAGE() WHERE logdate=@currenttime  
  END CATCH  

END  

--exec rml_re_GetAndStoreConsumerSubscriptionData



GO

Answer 1

OK 首先这里有一些可以帮助自己的东西。在 proc 中添加一个默认值为 0 的调试参数。如果您将调试参数值设置为 1, 那么添加代码来打印动态 SQl, 而不是运行它。这应该是标准做法, 每当您在 proc 中使用动态 SQl 时。 Likely 其中一个动态参数不会形成 correcly, 所以您需要看到 Teh 生成的 SQL 来找出什么是错的。

你把故障存储在捕捉区你收到什么错误信息?

友情链接