SQL 2008, Unclosed quotation mark after the character string ' COMMIT TRAN'.

  • ALTER PROC [dbo].[usp_UploadSantam] (@ImportOriginator varchar (50))

    AS

    DECLARE @Headers VARCHAR(1000)

    DECLARE @x VARCHAR(8000)

    DECLARE @Maxrows int

    DECLARE @RowCount int

    DECLARE @branch varchar(50)

    DECLARE @UploadID int

    DECLARE @InsurerID tinyint

    DECLARE @RecCount tinyint

    DECLARE @PolicyNumber varchar(25)

    DECLARE @Type char(3)

    DECLARE @BrokerIDSecondary INT

    DECLARE @Partnerid INT

    DECLARE @Brokerid INT

    DECLARE @SegmentID INT

    DECLARE @PolicyID INT

    DECLARE @CompanyName varchar (255)

    DECLARE @ClientID int

    DECLARE @BranchID int

    DECLARE @AgentcodeData varchar(255)

    DECLARE @AgentCodeID int

    DECLARE @TransactionDateChar char(8)

    DECLARE @TransactionDate datetime

    DECLARE @EarningMonth int

    DECLARE @EarningYear int

    DECLARE @PremiumVested DECIMAL (12,4)

    DECLARE @PremiumPolicyFee DECIMAL (12,4)

    DECLARE @PremiumBase DECIMAL (12,4)

    DECLARE @Commission DECIMAL (12,4)

    DECLARE @CommissionPerc DECIMAL (12,4)

    DECLARE @FullName varchar(255)

    DECLARE @CompanyID INT

    DECLARE @ProductID smallint

    DECLARE @PortFolioID smallint

    DECLARE @Sender varchar (100)

    DECLARE @SystemID int

    DECLARE @FiscalPeriodID int

    --ROLL-UP

    DECLARE @Table TABLE

    ([UploadID] [int] identity NOT NULL ,

    [Branch] [int] ,

    [AgentCode] [varchar] (255) ,

    [PolicyNo] [varchar] (35) ,

    [FullName] [varchar] (255),

    [Premium] [decimal](15, 2) NULL ,

    [PolicyFee] [decimal](15, 2) NULL ,

    [Commission] [decimal](15, 2) NULL ,

    [Earning Month] [int] NULL ,

    [Earning Year] [int] NULL)

    BEGIN

    SET @Sender = 'usp_UploadSantam'

    --usp_UploadSantam

    --usp_UploadSBInvest

    DELETE FROM tblMIS_UploadSantam WHERE PolicyNo IS NULL

    -------------------------------------------------------------------------------------------------------

    -- First validate Branch and agentcode

    UPDATE tblMIS_UploadSantam

    SET Branch = (case WHEN len(branch) = 1 THEN '00'+Branch

    WHEN len(Branch) = 2 THEN '0'+ Branch

    ELSE Branch END)

    UPDATE tblMIS_UploadSantam

    SET AgentCode = LTRIM(RTRIM(Branch)) + '/' + LTRIM(RTRIM(AgentCode))

    UPDATE tblMIS_UploadSantam

    SET Branch = T.BranchID

    FROM tblMIS_AgentCodeTranslation T, tblMIS_UploadSantam U

    WHERE T.AgentCode = U.AgentCode

    -- ----now process individual records----------------------------------------------------------------------

    SELECT @PartnerID = 1, @BrokerIDSecondary = 1, @SegmentID = 1, @ProductID = 1, @PortFolioID = 1, @SystemID = 3

    --get brokerID from New Business Table

    IF EXISTS (SELECT BrokerID

    FROM tblMIS_BrokerNewBusiness

    WHERE PolicyNumber = @PolicyNumber)

    SELECT @BrokerID = BrokerID

    FROM tblMIS_BrokerNewBusiness

    WHERE PolicyNumber = @PolicyNumber

    ELSE

    SET @BrokerID = 1

    INSERT INTO @Table (Branch, AgentCode,PolicyNo, FullName,

    Premium, PolicyFee, Commission,

    [Earning Month], [Earning Year] )

    SELECT Branch, AgentCode,PolicyNo, FullName,

    Sum(isnull(PremiumVested,0)), sum(isnull([Policy Fee],0)), sum(isnull(Commission,0)),

    [Earning Month], [Earning Year]

    FROM tblMIS_UploadSantam

    GROUP BY Branch, AgentCode, PolicyNo, FullName, [Earning Month], [Earning Year]

    Update @Table

    set Premium = Premium+PolicyFee

    SET @RowCount = 1

    SELECT @MaxRows = count(1) FROM @Table

    WHILE @RowCount <= @MaxRows

    BEGIN

    SELECT @Policynumber = PolicyNo,

    @BranchID = Branch,

    @FullName = FullName,

    @AgentCodeData = AgentCode,

    @PremiumVested = Premium,

    @PremiumPolicyFee = PolicyFee,

    @Commission = Commission,

    @CommissionPerc = (case when premium <> 0 then (commission/premium)*100 else 0 end),

    @EarningYear = [Earning Year],

    @EarningMonth= [Earning Month]

    FROM @Table

    WHERE UploadID = @RowCount

    SELECT @InsurerID =

    CASE

    WHEN SUBSTRING(@AgentcodeData,1,3) IN ('432','433','434','439','527') THEN 6

    WHEN SUBSTRING(@AgentcodeData,1,3) IN ('225') THEN 58

    ELSE 37

    END

    --get details for header record

    SELECT @ClientID = 1

    IF EXISTS (select ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName)

    SELECT @ClientID = ClientID FROM tblMIS_Client (nolock) WHERE FullName = @FullName

    --create new client if necessary

    IF @ClientID = 1

    EXEC usp_InsertClient null, null, @FullName, @FullName, null, @FullName, NULL, NULL, @ClientID OUTPUT

    --if header exists modify else insert

    IF EXISTS (SELECT PolicyID FROM tblMIS_PolicyHead (NOLOCK) WHERE PolicyNumber = @PolicyNumber)

    SELECT @Type = 'UPD'

    ELSE

    SELECT @Type = 'INS'

    --process header record

    EXEC usp_InsertPolicyHead

    @Sender, @Type,@PolicyNumber, 3, null, @BrokerID,@PartnerID, @ClientID, 4, @SegmentID, null,

    @InsurerID, @SystemID, @AgentCodeData, @ProductID, @PortFolioID, @PolicyID OUTPUT

    --------------------------------------------------------------------------------------------------------

    --get details for detail record

    SELECT @TransactiondateChar = FiscalDateChar , @FiscalPeriodID = FiscalPeriodID,

    @TransactionDate = FiscalDate FROM tblMIS_FiscalPeriods WHERE EarningYear = @EarningYear AND EarningMonth = @EarningMonth

    IF EXISTS ( SELECT AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID)

    SELECT @AgentCodeID = AgentCodeID FROM tblMIS_AgentCodeTranslation WHERE AgentCode = @AgentCodeData AND InsurerID = @InsurerID

    ELSE

    SELECT @AgentCodeID = 1

    IF isnull(@AgentCodeID,0) = 0

    RAISERROR ('AgentCode does NOT EXIST' ,11,1,@AgentCodeData)

    IF EXISTS (SELECT DetailID

    FROM tblMIS_PolicyDetail

    WHERE PolicyID = @PolicyID

    AND FiscalPeriodID = @FiscalPeriodID

    AND AgentCodeID = @AgentCodeID)

    SELECT @Type = 'UPD'

    ELSE

    SELECT @Type = 'INS'

    IF EXISTS ( SELECT PolicyNumber FROM tblMIS_BrokerNewBusiness WHERE PolicyNumber = @PolicyNumber)

    SELECT @PremiumBase = PremiumGross FROM tblMIS_BrokerNewBusiness (NOLOCK) WHERE PolicyNumber = @PolicyNumber

    ELSE

    SELECT @PremiumBase = @PremiumVested

    EXEC usp_InsertPolicyDetail

    @Type, @PolicyID, @TransactionDate, @TransactionDateChar, @PremiumBase,

    @PremiumVested, @PremiumPolicyFee, @Commission, @CommissionPerc, @AgentCodeID,

    @BranchID, @InsurerID, @SystemID,@ImportOriginator

    SET @RowCount = @RowCount + 1

    END

    DELETE FROM tblMIS_PolicyDetail

    WHERE PremiumVestedIncl = 0

    AND Commission = 0

    AND PremiumPolicyFee=0

    ---santam direct have been unable to provide policy fee details

    ---since mar 2006. therefore we are copying the policyfee from march to

    ---subsequent months as a provision

    -- update tblMIS_PolicyDetail

    -- set PremiumPolicyFee = M.PremiumPolicyFee

    -- from tblMIS_SantamDirectPFMar2006 M inner join

    -- tblMIS_PolicyDetail on tblMIS_PolicyDetail.PolicyId = M.PolicyId

    -- where TransactionDateChar = @TransactionDateChar

    END

  • What is this all about?

    If your question is about SQL 2008, why not post it in that forum?

    If your question is about T-SQL, why post it in an SSIS forum?

    What is your question?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I am trying to import a flat file but everytime it fails but I cant fine where is failing ... please help...

    oh yah is T-Sql

  • How about posting the code for the stored procedures which are called from this one?

    There's nothing in this stored procedure related to importing a flat file - how have you determined that it is this stored procedure which is causing the problem?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thank you guys, it took me a while but i found the problem. It was inside the stored procedure usp_InsertPolicyHead. There's stored procedure that runs inside usp_InsertPolicyHead ... that's where it failing ...

    Thank you very much ....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply